I'm assuming the Start time and End time can only fall within the same date. I.e. no start date 1-Jan and finish 2-Jan next day.
Using some dummy numbers. Here's what I got.
Row\Col |
A |
B |
C |
D |
E |
1 |
Date |
Start time |
End Time |
1800:2400 |
0000:0700 |
2 |
Dates |
8:00 PM |
12:00 AM |
4 |
- |
3 |
Dates |
3:00 AM |
6:00 AM |
- |
3 |
4 |
Dates |
4:00 PM |
11:00 PM |
5 |
- |
5 |
Dates |
4:00 AM |
12:00 PM |
- |
3 |
Formula in D2 - copied down
Formula in E2 - copied down
Change the section in red to whatever time limit you want.
Note that, TIME(24,0,0) and TIME(0,0,0) is the same, i.e. =0. Hence, if you want 12AM as an end shift, you need to put in a 1 (see formula D2).
Bookmarks