Please see below table.
I am trying to get a formula for column D which works out the time taken from generated to started, taking into account working time and working days. I also have a list of bank holidays to be ignored on a separate sheet.
Once I get that, I can do further formulas for time taken for Start to Completed and Generated to Completed.
Currently in D3 is the following formula which works fine for most but, struggles once there is an entry where the start time falls outside of the work hours, for instance rows 5 and row 8.
For times that are before 7:00, the generated time should be 7:00 for the day, or next working day if the day is not a working day. For times after 17:00, the generated time should be 7:00 for the next working day.
Please can someone help?
Thanks
=IF(B3="","Not Started",IF(AND(NETWORKDAYS(A3,B3,'Bank Holidays'!$A$1:$A$8)>0, A3<>""),(NETWORKDAYS(A3,B3,'Bank Holidays'!$A$1:$A$8)-1)*($D$1-$B$1)+MOD(B3,1) - IF(OR(MOD(A3,1)<$B$1, MOD(A3,1)>$D$1), $B$1, MOD(A3,1)) + IF(MOD(A3,1)<$B$1, $B$1-MOD(A3,1), 0) + IF(MOD(B3,1)>$D$1, MOD(B3,1)-$D$1, 0),0))
I am trying to get a formula for column D which works out the time taken from generated to started, taking into account working time and working days. I also have a list of bank holidays to be ignored on a separate sheet.
Once I get that, I can do further formulas for time taken for Start to Completed and Generated to Completed.
Currently in D3 is the following formula which works fine for most but, struggles once there is an entry where the start time falls outside of the work hours, for instance rows 5 and row 8.
For times that are before 7:00, the generated time should be 7:00 for the day, or next working day if the day is not a working day. For times after 17:00, the generated time should be 7:00 for the next working day.
Please can someone help?
Thanks
=IF(B3="","Not Started",IF(AND(NETWORKDAYS(A3,B3,'Bank Holidays'!$A$1:$A$8)>0, A3<>""),(NETWORKDAYS(A3,B3,'Bank Holidays'!$A$1:$A$8)-1)*($D$1-$B$1)+MOD(B3,1) - IF(OR(MOD(A3,1)<$B$1, MOD(A3,1)>$D$1), $B$1, MOD(A3,1)) + IF(MOD(A3,1)<$B$1, $B$1-MOD(A3,1), 0) + IF(MOD(B3,1)>$D$1, MOD(B3,1)-$D$1, 0),0))
| Row/Column | A | B | C | D |
| 1 | Work Start Time: | 07:00:00 | Work End Time: | 17:00:00 |
| 2 | Generated | Started | Completed | Logged to Start |
| 3 | 03/06/2024 10:29 | 03/06/2024 11:56 | 03/06/2024 12:10 | 01:27:31 |
| 4 | 03/06/2024 13:21 | 03/06/2024 14:38 | 04/06/2024 08:09 | 01:17:26 |
| 5 | 04/06/2024 06:53 | 04/06/2024 08:23 | 04/06/2024 08:23 | 01:30:04 |
| 6 | 04/06/2024 07:54 | 04/06/2024 08:39 | 04/06/2024 08:39 | 00:45:39 |
| 7 | 04/06/2024 08:14 | 04/06/2024 08:22 | 04/06/2024 08:22 | 00:08:16 |
| 8 | 04/06/2024 20:09 | 06/06/2024 12:10 | 10/06/2024 07:23 | 01:10:34 |
| 9 | 05/06/2024 07:38 | 06/06/2024 12:10 | 06/06/2024 12:19 | 14:31:47 |
| 10 | 05/06/2024 08:47 | 06/06/2024 12:10 | 07/06/2024 06:55 | 13:23:35 |
