Excel Time Calculation 2

[Sethanye]

Hey,

I was wondering if someone would be able to help me with this, it's a little complicated, so I do apologise for that right away.

Effectively I have two time fields for shifts, which if above 6 hours and 15 minutes will subtract a 30 minute lunch. As excel is not a big fan of calculating time, I ended up having to use an IF(TIME comparisons, which lead to me to this:

=IF(TIME(HOUR(D2),MINUTE(D2),SECOND(D2))-TIME(HOUR(C2),MINUTE(C2),SECOND(C2))>TIME(6,15,0),(TIME(HOUR(D2),MINUTE(D2),SECOND(D2))-TIME(HOUR(C2),MINUTE(C2),SECOND(C2)))-TIME(0,30,0),(TIME(HOUR(D2),MINUTE(D2),SECOND(D2))-TIME(HOUR(C2),MINUTE(C2),SECOND(C2))))

Which works just fine, until you have an occurrence where let's say the start of the shift is at 10PM and ends at 2AM, which results in a negative number and a display of #########, after a bit of google search I found out you can do a > comparison over the time calculation and effectively add a single day if the number is negative correcting the issue. Which resulted in this formula:

=IF(TIME(HOUR(D3),MINUTE(D3),SECOND(D3))-TIME(HOUR(C3),MINUTE(C3),SECOND(C3))>TIME(6,15,0),(TIME(HOUR(D3),MINUTE(D3),SECOND(D3))-TIME(HOUR(C3),MINUTE(C3),SECOND(C3)))+(TIME(HOUR(C3),MINUTE(C3),SECOND(C3))>(TIME(HOUR(D3),MINUTE(D3),SECOND(D3))))-TIME(0,30,0),(TIME(HOUR(D3),MINUTE(D3),SECOND(D3))-TIME(HOUR(C3),MINUTE(C3),SECOND(C3)))+(TIME(HOUR(C3),MINUTE(C3),SECOND(C3))>(TIME(HOUR(D3),MINUTE(D3),SECOND(D3))))


The fields being D2 or D3, does not matter in this particular case.

This formula works half way, it still subtracts the 30 minutes if the shift is lets say from 8am to 4pm as it treats it as a over the required number gap, it also calculates anything with the midnight gap by adding the one day to it resulting in accurate representation of the hours, what it does not do is deduct the 30 minutes for these crossing over shifts. Here's an example to hopefully better explain.

10:00:00 | 14:00:00 | Result 06:00:00 which is correct.
10:00:00 | 18:00:00 | Result 07:30:00 correct as 30 minutes were deducted.
22:00:00 | 06:00:00 | Result 08:00:00 Which is not correct as it should have 30 minutes subtracted, however due to the calculation being converted from a negative time it does not subtract the 30 minutes.

Is there any reasonable way to get around this?

Thank you

 

[SkipVought]

Hi,

Unless you are referring to DURATION (the absolute difference betweeb two Date/Time values, like how long does it take to get from here to there) every time value is really a Date/Time value as Date/Time, as expressed in Excel, or any computing system is a pure number, formatted as per our choice.

As of this writing, her in North Texas, that vslue is 43252.3580975694 which csn be formatted any number of ways like 6/1/18 8:35 or 08:35:40 or 2018-06-01.

The point is that when you do DURATION cakculations, the Date part ought to be part of the equation particularly if your calculations will be across days.

When performing Duration calculations, use Date/Time values.

And BTW, why do all that conversion? You already have Time values!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

So here are two rows, one spans days
[pre]
C D E
From To Dur
6/1/2018 8:48 6/1/2018 15:00 6:11:31
6/1/2018 9:00 6/2/2018 1:15 15:45:00
[/pre]

The formuula in E2
[tt]
E2: =D2-C2-IF(D2-C2>TIME(6,15,0),TIME(0,30,0),0)
[/tt]



Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Andrzejek]

As excel is not a big fan of calculating time [DURATION]

It is like saying: "Screwdrivers are not good when used to drive nails into the wood."
Excel is great in these calculation, if you are doing it right.

[pre]
From To Duration in Days Duration in Hours
6/1/18 8:35 AM 6/1/18 1:23 PM 0.2 4.8
6/1/18 11:31 PM 6/2/18 8:16 AM 0.365 8.76
[/pre]
Formula in C2: =B2-A2
Formula in D2: =C2*24


---- Andy

There is a great need for a sarcasm font.

 

[Sethanye]

Hey Both of you,

Thank you so much for the help on this, I do appreciate it and it did make the formula a lot simpler, I was approaching it from the wrong angle.

Much appreciated