ExcelNewbie2020
Active Member
- Joined
- Dec 3, 2020
- Messages
- 293
- Office Version
- 365
- Platform
- Windows
I would like to calculate the time difference between completed and start date but with condition.. Please see below.. thank you..
Start | Completed | Expected Result (hh:mm) | |
01-08-22 10:00 | 01-08-22 16:50 | 3:50 | |
03-08-22 10:00 | 04-08-22 10:20 | 7:20 | |
06-08-22 11:00 | 09-08-22 11:40 | 25:40 | |
09-08-22 13:00 | 10-08-22 11:15 | 2:15 | |
10-08-22 15:00 | 12-08-22 15:00 | 15:00 | |
10-08-22 15:00 | 13-08-22 16:00 | 25:00 | |
22-08-22 16:00 | 23-08-22 16:00 | 7:00 | |
25-08-22 16:00 | 27-08-22 10:50 | 10:50 | |
Office Hours 9am-6pm | |||
Condition | |||
if the start date is <1PM Counting start from 1PM to 6PM (5hrs) of the same date | |||
if the start date is >1PM Counting start the next day from 9AM to 6PM (9hrs) | |||
Assuming it is not completed on the succeeding days 1 day = to 9hrs (within working hours) | |||