Forum Discussion
Total hours between two time ranges
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.
Onlinehelp Can you split the TIME column into two? One for start time and one for end time. Then you can simple use a formula like end-time minus start-time to calculate duration. For example =B2-A2
Depending on the times involved you may have to perform some other steps. Better to upload a file with some examples that you encounter in real life.
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.
Onlinehelp That's a bit different from your original question. It seems you have a bunch of texts in all the time columns. You could do a number of formulae similar to what SergeiBaklan just suggested or do what I suggested earlier. Split the columns. Alternatively, perhaps Power Query could do what you need without the need of complex formulae.
