Forum Discussion
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.
13 Replies
Forgot to say above is for Excel which supports dynamic arrays, otherwise array formula with Ctrl+Shift+Enter shall be used.
SergeiBaklan Getting zero for me. It is excel home and student 2019 edition. Thank you for the help.
2019 doesn't support dynamic arrays. I have no machine with it to test right now, please try to enter formula as array one. Instead of Enter use Ctrl+Shift+Enter. {} shall appear around the formula.
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.
