Forum Discussion
Time between leave entries
Hi All,
I'm struggling with this one. I have data of part day leave entered by drivers. I'm trying to calculate availability based on leave entered. For the most part it works fine, but one of the requirements is that if one leave entry starts less than 4 hours after another one finishes then that driver is considered unavailable from the beginning of the first entry to the end of the second entry. (see example data below of 2 entries that fall into that situation.
| Date | Driver_Code | Leave_Start | Leave_End | Number_of_Days |
| 27/01/2023 | 1002 | 27/01/2023 7:00 | 27/01/2023 12:00 | 1 |
| 27/01/2023 | 1002 | 27/01/2023 14:00 | 27/01/2023 19:00 | 1 |
| 29/01/2023 | 1003 | 29/01/2023 15:30 | 29/01/2023 19:30 | 1 |
| 29/01/2023 | 1002 | 29/01/2023 3:30 | 29/01/2023 13:30 | 1 |
| 30/01/2023 | 1003 | 30/01/2023 21:30 | 1/02/2023 6:00 | 2 |
Regards, Duane
I'm thinking something like a calulated column that checks for start times for that driver between 0 and 4 hours after this end time and if it finds one then the new column would be NEW_END_TIME is the found start time otherwise it would be the original end time.
1 Reply
I'm thinking something like a calulated column that checks for start times for that driver between 0 and 4 hours after this end time and if it finds one then the new column would be NEW_END_TIME is the found start time otherwise it would be the original end time.