Excel update day name on cell select

README.md

Excel Macro to dynamically update cell with day name

Demo

2023-01-19.16-06-00.mp4

You can download template file here.

Important

1. Save your excel file with .xlsm (macro-enabled)
2. Make sure the cell for date is formatted as Text (If formatted as Date, sometimes it would lead to incorrect day, perhaps due to locale).

code.vb

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Selection.Count = 1 Then
        Dim currentYear As Integer
        Dim selectedRow As Long
        Dim selectedCol As Long
        Dim validRange As Range
        Dim dayAddress As String
        Dim selectedDay As Integer
        Dim monthValue As Integer
        
        'Selecting outside of this range will be ignored
        Set validRange = Range("B6:M36")
        If Intersect(Target, validRange) Is Nothing Then
            Exit Sub
        End If
        
        currentYear = Range("A4").Value
    
        selectedRow = Target.Row 'Date row
        selectedCol = Target.Column 'Month column

        dayAddress = "A" & selectedRow
        selectedDay = Range(dayAddress).Value

        monthValue = selectedCol - 1

        Range("O6").Value = selectedDay & "/" & monthValue & "/" & currentYear
        Range("O5").Value = Format(Range("O6").Value, "dddd")
    End If
End Sub