Formula to find last date of attendance
Solved/Closed
Reminv93
-
May 10, 2021 at 02:52 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Nov 3, 2022 at 12:32 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Nov 3, 2022 at 12:32 PM
Related:
- Attendance formula in excel
- Date formula in excel dd/mm/yyyy - Guide
- Number to words in excel formula - Guide
- Excel grade formula - Guide
- Logitech formula vibration feedback wheel driver - Download - Drivers
- Screenshot in excel - Guide
2 responses
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Updated on May 18, 2021 at 12:04 PM
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:
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
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
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Nov 3, 2022 at 12:32 PM
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
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 FunctionI 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