Forum Discussion
Solved
Total hours between two time ranges
Hello, I would like to find the total hours between 7:30 am to 12:30 pm on same day in excel. TIME TOTAL HOURS 7:30 to 12:30 ? Please guide. Thank you so much.
As variant
with
=SUM( IFERROR( MOD(RIGHT(C7:I7, LEN(C7:I7)-SEARCH("-",C7:I7)) - LEFT(C7:I7, SEARCH("-",C7:I7)-1), 0.5),0))but it very depends on do you have overnight shifts or not and do you keep exactly the same format for all time periods.
Riny_van_Eekelen Thank you @Riny_van_Eekelen for your reply. Below is the real life scenario where I would like to fill in the total hours. Ideally no the TIME column in only in one column but can be little creative if there is a guidance from you/someone. Thanks.
| Name | Sat | Sun | Mon | Tue | Wed | Thu | Fri | Total Hrs |
| Tom | 9:00-7:00 | 10:00-7:00 | 2:00-9:00 | 8:30-1:30 | 2:00-9:00 | ? | ||
| Mark | 8:00-1:30 | 8:00-1:30 | 2:00-9:00 | ? | ||||
| Harry | 2:00-9:00 | 2:00-9:00 | 8:30-1:30 | ? |
As variant
with
=SUM(
IFERROR(
MOD(RIGHT(C7:I7, LEN(C7:I7)-SEARCH("-",C7:I7)) -
LEFT(C7:I7, SEARCH("-",C7:I7)-1),
0.5),0))but it very depends on do you have overnight shifts or not and do you keep exactly the same format for all time periods.
- No overnight shifts. Same format for all time periods. Let me try to test at me end and will update.
If there are no overnight values, how does one interpret 10:00 - 7:00? As compared to, say, 2:00-9:00.
- Then should it be say AM to PM written?
