May 22 2021 06:45 AM
May 22 2021 07:03 AM
@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.
May 22 2021 07:16 AM
@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.
May 22 2021 07:27 AM
@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.
May 22 2021 07:34 AMSolution
=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.
May 22 2021 07:41 AM
May 22 2021 09:19 AM - edited May 22 2021 09:20 AM
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.
May 22 2021 01:56 PM
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))