Formula to find last date of attendance

[Solved]
Report
-
Posts
2809
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 23, 2021
-
Hello,


I have an excel sheet that enters the time taken by each student on each day for the whole month. The student names are in the rows and date are in the columns.

I need a formula in a cell that returns the date after checking each day and finding the last date that the student attended the class.
Thanks,

System Configuration: Android / Chrome 90.0.4430.91

1 reply

Posts
2809
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 23, 2021
484
Hi Reminv93,

So a very simplified version of your sheet looks like this:


The names are place in column B.
The dates are placed in row 2.

If you want to create the result as in column F, then put the code below in a standard module (Alt+F11 to open VBA editor, top menu Insert > Module, then paste code and close window.) to add a new function:
Function LastAttendanceDate(mDates, mTimes As Range)
Dim mCol As Integer
For Each cell In mTimes
    If cell.Value <> vbNullString Then
        mCol = cell.Column
    End If
Next cell
LastAttendanceDate = Cells(mDates.Row, mCol)
End Function


The syntax for the custom function is:
=LastAttendanceDate(Range where the dates are located,Range of the student times)
NOTE: I'm using a comma here, as that is the most commonly used separator, as opposed to the semicolon, that I'm using in the screenshot.

In case you don't know; the dollar symbols in the formula mean that the range for the dates are absolute/fixed. This enables you to drag the formula down without the range for dates to change.

Best regards,
Trowa