Formula to find last date of attendance

Solved/Closed
Reminv93 - May 10, 2021 at 02:52 AM
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 - Nov 3, 2022 at 12:32 PM
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

2 replies

TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
Updated on May 18, 2021 at 12:04 PM
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

2
slinger42 Posts 2 Registration date Sunday October 30, 2022 Status Member Last seen October 31, 2022
Oct 31, 2022 at 11:34 AM

Hi TrowaD

I have used your UDF in my register system as shown below, and it works well.

However, I need it to ignore the characters 'O' (Thats the letter O by the way), and the character "W" if entered in a Row, and if either are entered the original date is left unchanged. In other words, the true last date of attendance is not changed if the O or the W is entered.

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

I have tried so many ways to implement this I have become confused, and any help would be much appreciated.

Looking forward to your reply

Regards

0
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
Nov 3, 2022 at 12:32 PM

Hi Slinger,

For that you will need to add the extra conditions to code line 4:
 

Function LastAttendanceDate(mDates, mTimes As Range)
Dim mCol As Integer
For Each cell In mTimes
    If cell.Value <> vbNullString And cell.Value <> "O" And cell.Value <> "W" Then
        mCol = cell.Column
    End If
Next cell
LastAttendanceDate = Cells(mDates.Row, mCol)
End Function

Best regards,
Trowa


2