SOLVED

# Total hours between two time ranges

Occasional Contributor

# 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.

13 Replies

# Re: Total hours between two time ranges

@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.

# Re: Total hours between two time ranges

@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 ?

# Re: Total hours between two time ranges

As variant

with

``=RIGHT(A2, LEN(A2)-(SEARCH(" to ",A2)+3))-LEFT(A2,SEARCH(" to ",A2))``

# Re: Total hours between two time ranges

@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 @Sergei Baklan 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.

best response confirmed by Onlinehelp (Occasional Contributor)
Solution

# Re: 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.

# Re: Total hours between two time ranges

Forgot to say above is for Excel which supports dynamic arrays, otherwise array formula with Ctrl+Shift+Enter shall be used.

# Re: Total hours between two time ranges

No overnight shifts. Same format for all time periods. Let me try to test at me end and will update.

# Re: Total hours between two time ranges

@Sergei Baklan Getting zero for me. It is excel home and student 2019 edition. Thank you for the help.

# Re: Total hours between two time ranges

If there are no overnight values, how does one interpret 10:00 - 7:00?  As compared to, say, 2:00-9:00.

# Re: Total hours between two time ranges

Then should it be say AM to PM written?

# Re: Total hours between two time ranges

If the difference between end and start is negative it assumes that start is before midday and the end after. It is calculated as =MOD(end-start, 0.5).  But that gives incorrect result for long shifts (more than 12 hours) or in situations where under 01:00-02:00 you assume 1am-2pm (also long shift).

Yes, more correct will be to use full time. From calculations point of view more suitable will be 14:00 format, not 2pm. Since the latest is hard in maintenance. We always shall use "2pm", not "2 pm" or "2p.m." or something else. Or we shall consider in formula all possible notations. That's possible, but formula will be much more complex. And formula itself depends on which Excel do you use, 365 or 2019 or something else.

# Re: Total hours between two time ranges

OK, so assuming you want to smallest amount of positive time difference, and with the from-to value in D2, use

``````=LET(_dash,SEARCH("-",D2),
_start,--TRIM(LEFT(D2,_dash-1)),
_end,--TRIM(MID(D2,_dash+1,255)),
_end-_start+12*(_end<_start))``````

# Re: Total hours between two time ranges

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.