How to Generate Absent Dates in a Single Cell in Excel (2022)

First create an attendance sheet as shown below. We already have shown how to create this attendance sheet, if you want to learn, click here.

Generate Row number & Column number

First we need to generate the row number and column number of the cell in which we are doing attendance. And these row number and column number will help us to generate the dates related to that cell. Suppose we mark A in cell C7 then in cell A4 we will get the row number as 7 and in B4 we will get the column number as 3. Based on these row number and column number we will generate the date of that column (i.e. column C) , the date will be 01.

So, first open the Visual Basic Editor. Right-Click on the sheet tab and select view code. You will get the screen as below:

Select the object as Worksheet and Procedure as Change, and type the code as show below. Before that we need to know the condition. The condition is if we make any changes on any cell within C7:AF16 then we we will get row number of the cell in A4 and column number in B4.

Now type the code given below:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("C7:AF16")) Is Nothing Then

ActiveSheet.Range("A4").Value = Target.Row ' to get row number in A4

ActiveSheet.Range("B4").Value = Target.Column ' to get column number in B4

End If

End Sub

Now change any cell value within C7:AF16, you will notice row number and column number will appear in A4 and B4 respectively.

Now we will create another module/macro to generate the date in the report based on that row number and column number.

Generate Date in Report

Again go to Visual Basic Editor, and click Insert ► Module. Insert the code given below:

Sub AbsentDates()
Dim Row As Integer, Col As Integer ' Variable declaration
Row = ActiveSheet.Range("A4").Value 'Set Value of Row
Col = ActiveSheet.Range("B4").Value 'Set Value of Col
ActiveSheet.Range("AK" & Row).ClearContents 'Clear any value in Absent Dates column in report
For i = 3 To 33 ' loop through all the date columns
If ActiveSheet.Cells(Row, i).Value = "A" Then 'Check if any cell within selected row has value A
If ActiveSheet.Range("AK" & Row).Value = "" Then 'check Absent column is blank or not
ActiveSheet.Range("AK" & Row).Value = Format(ActiveSheet.Cells(6, i).Value, "d") ' get the first date of the row
Else
ActiveSheet.Range("AK" & Row).Value = ActiveSheet.Range("AK" & Row).Value & "," & Format(ActiveSheet.Cells(6, i).Value, "d") ' get the rest of the date of the row
End If
End If
Next i
End Sub

Now we will add another code in Worksheet Change procedure to trigger the above code. Double-Click on Sheet1 and Just type Call AbsentDate before End If.