SOLVED

Total hours between two time ranges

Copper Contributor

Hello,

 

I would like to find the total hours between 7:30 am to 12:30 pm on same day in excel.

TIMETOTAL HOURS
7:30 to 12:30?

 

Please guide. Thank you so much.

13 Replies

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

 

NameSatSunMonTueWedThuFriTotal Hrs
Tom9:00-7:0010:00-7:002:00-9:00  8:30-1:302:00-9:00?
Mark   8:00-1:308:00-1:302:00-9:00 ?
Harry   2:00-9:002:00-9:00 8:30-1:30?

@Onlinehelp 

As variant

image.png

with

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

@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 (Copper Contributor)
Solution

@Onlinehelp 

As variant

image.png

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 

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

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

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

 

Onlinehelp_0-1621695547426.png

 

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

@Onlinehelp 

Then should it be say AM to PM written?

@Onlinehelp , @tusharm10 

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.

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))

@Onlinehelp 

@Onlinehelp 

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.

1 best response

Accepted Solutions
best response confirmed by Onlinehelp (Copper Contributor)
Solution

@Onlinehelp 

As variant

image.png

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.

View solution in original post