Time Calculation Question

[spyldbrat]

I am trying to create a formula so that it calculates time to the hundredths of a minute based on the conditions below. The best someone was able to do was to give the result in 3 separate columns - 1 column displayed the hours, one displayed the minutes and one displayed the total hours. However, I really need the result to to be in 1 column.

If L = 0 then 0

If P = F then subtract I from H and display in 100ths of a minute

If P is > F, then subtract I from AC1 (midnight) and display in 100ths of a minute

This is the formula I currently have. I couldn’t figure out how to add the second condition.

=IF(L2=0,0,IF(P2=F2,L2,HOUR($AC$1-H2)+MINUTE($AC$1-H2)/60))

 

[jasonb75]

The formula works fine for me, with only a screen capture to go on best guess would be that the date in P2 is 9 July and not 7 September.

 

[KRice]

Here is a revision that rounds according to the rules mentioned...

MrExcel20200915.xlsx
	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Date Shift Start		Time In	Time Out	Lunch (decimal hours)	Total Hrs Worked (rounded 1/4 h)					Date Shift End	Total Hours Worked (decimal)	Hours Current Day	Hours Next Day		Hours Var
2	9/7/2020		16:32	20:33	0	4.00					9/7/2020	4.02	4.02	0.00		-0.02
3	9/6/2020		16:32	20:09	0	3.50					9/6/2020	3.62	3.62	0.00		-0.12
4	9/12/2020		22:36	1:18	0	2.75					9/13/2020	2.70	1.40	1.30		0.05
Sheet1

Cell Formulas
Range	Formula
Q2:Q4	Q2	=IF(ROUND(((P2+I2)-(F2+H2))*24,2)-J2>=0,ROUND(((P2+I2)-(F2+H2))*24,2)-J2,"not possible")
R2:R4	R2	=IF(F2=P2,Q2,IF(P2=F2+1,(P2-(F2+H2))*24,IF(P2>F2+1,"shift too long",IF(P2<F2,"not possible"))))
S2:S4	S2	=IFERROR(Q2-R2,"")
K2:K4	K2	=IF(MOD(Q2,0.25)<0.125,FLOOR(Q2,0.25),CEILING(Q2,0.25))
U2:U4	U2	=IFERROR(K2-Q2,"")

 

[spyldbrat]

@jasonb75 the date in P is set to the correct format of MM-DD-YY. There is a formula in P that increases the date from F by 1 if the time out is greater than the time in: =IF(I2<=H2,F2+1,F2). I am not sure if this can be causing the issue? I am also including a screen shot of the formatting from P. The same format also exists in F.

Currently the formulas are the following:
Q: =ROUND(DOLLARFR(MOD(((P2+I2)-(F2+H2))-J2,1)*24,100),2)
R: =ROUND(DOLLARFR(MOD((MIN(P2+I2)-(F2+H2))-J2,1)*24,100),2)
S: =IF(P2>F2,R2-Q2,"0.00")

The formula in R only partially works. It does not calculate up to midnight in R if the shift ends on the next day:

 

[jasonb75]

The formula in R is probably not working because part of it is missing, if I use the longer version from my post it works fine.

=ROUND(DOLLARFR(MOD((MIN(P2+I2,F2+1)-(F2+H2))-J2,1)*24,100),2)

Also, you don't need all of that if S2, the short version in my post works just as well.

 

[spyldbrat]

When I replaced S2 with above, the time is calculating for the shifts that cross midnight calculate correctly. BUT, now it seems it's not calculating correctly for the shifts that DON'T cross midnight?

 

[jasonb75]

Sorry, that was my bad. I meant the shorter version in my earlier post.
=Q2-R2
The IF part of your original formula in S2 is not needed. Also note that it is Q2-R2, not R2-Q2 as you have in your formula.

 

[spyldbrat]

I can correct the problem in S - but the formula in R is still not working?

 

[jasonb75]

Do your time columns have dates hidden by formatting?

This is the only reason I can think of for the formula to fail, I get the correct results using the data from your screen captures.

If this is the cause of the error then it would have been immediately apparent if you had followed the request in post 7 and used XL2BB to post your data examples instead of screen captures (which only allow us to see the visual data, not the actual data).

 

[spyldbrat]

@jasonb75 - that was the problem!!!! Thank you for ALL of your help!