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

 

[KRice]

I don't understand what you are trying to accomplish. What are the units of the values in the cells you mention...for example, IF P=F, you want H-I and the result should be displayed in 100ths of a minute, but what is in cells H, I, P, F, AC1? Are these times, dates, seconds, etc.?

 

[spyldbrat]

Sorry.
F and P are dates. The rest is time displayed as 13:30.

 

[KRice]

Thanks for the additional detail, but I still don't understand what you are trying to accomplish. Your first post mentions that if P>F, then subtract I from AC1, yet your formula shows $AC$1-H2. Which is it...I or H? What value is in $AC$1...what does it look like?

It appears that you want to calculate the difference between two times, possibly the difference between two times on different days. What is the relationship between the times in H2 and I2 to the dates in F2 and P2? Does the combination of F2 and H2 describe one point in time, and the combination of P2 and I2 describe the other point in time? Which is expected to occur first?

You want a solution that "calculates time to the hundredths of a minute". What does this mean? If the difference between two times is 2 hours and 30 minutes, or 2.5 hours, this result could be shown as 150.00 minutes (a result expressed in minutes and displayed with a precision of hundreths of a minute), or would the result be shown as 15,000 hundredths of a minute (fifteen thousand hundreths of a minute)?

 

[spyldbrat]

I do apologize....

My spreadsheet has data that shows a start date, end date, a clock in and clock out and the total time worked. The time worked is calculating to the nearest quarter of the hour. The end date displays the following day's date if the clock out occurred after midnight.

I need the total time worked to calculate time based on 100ths of a minute. Additionally, I need this time show the total hours, to the 100th of a minute, to break the hours down on the the current day and and next day.

I was able to convert the total time worked to 100'ths of minute, but I am unable to calculate total time worked for the current day.


Column E - Shift Start Date (Date Format)
Column H - Clock in time (show in 13:30 format)
Column I - Clock Out time (show in 13:30 format)
Column K - Total time worked - rounded to the nearest
Column N - Shift End Day (Date Format)

 

[spyldbrat]

I do apologize....

My spreadsheet has data that shows a start date, end date, a clock in and clock out and the total time worked. The time worked is calculating to the nearest quarter of the hour. The end date displays the following day's date if the clock out occurred after midnight.

I need the total time worked to calculate time based on 100ths of a minute. Additionally, I need this time show the total hours, to the 100th of a minute, to break the hours down on the the current day and and next day.

I was able to convert the total time worked to 100'ths of minute, but I am unable to calculate total time worked for the current day.


Column E - Shift Start Date (Date Format)
Column H - Clock in time (show in 13:30 format)
Column I - Clock Out time (show in 13:30 format)
Column K - Total time worked - rounded to the nearest
Column P - Shift End Day (Date Format)

Column Q shows the total time worked (I minus H) in 100ht's of a minute. This is working correctly. =HOUR(I2-H2)+MINUTE(I2-H2)/60

Column R is NOT working correctly - but it should do the following:

1. Look at column K (Total time worked) - if it's 0.00, then it should make the result 0.00
2. If Colum K is not a 0.00, then look at Column P (Shift end date). If Column P is the same value as Column E (Shift Start date), then just put the value of column Q (the total time worked converted to 100th's of a minute).
3. If Column P is greater than Column E (meaning the time worked crossed midnight), then subtract the value in AK1 (which is just showing the time of midnight) from column H (the clock in time) and display the time in 100ths of a minute.

This is formula I currently have in Col R: =+IF(L2=0,0,IF(P2=F2,L2,HOUR($AA$1-H2)=Minutes($AA$1-H2)/60))

Below is a screen shot of the column in question.

Columns S, T and U will calculate based on the value in R.

In the example above, the second line in column R should read 3.62.

I hope this helps.

 

[ebea]

Use the XL2BB add in, to upload your example, showing all the columns in your eample, instead of a photo. It would much easier for people, so they don't have to use time to write in data.

 

[jasonb75]

Is this what you're trying to do? There appear to be some discrepancies in the required accuracy between the screen captures and the descriptions that you have provided.

Book2 (version 1).xlsb
	F		H	I	J	K					P	Q	R	S
2	09/07/20		16:32	20:33	0	4					09/07/20	4.02	4.02	0.00
3	09/06/20		16:32	20:09	0	3.5					09/06/20	3.62	3.62	0.00
4	09/12/20		22:36	01:18	0	2.75					09/13/20	2.70	1.40	1.30
Sheet7

Cell Formulas
Range	Formula
Q2:Q4	Q2	=ROUND(DOLLARFR(MOD(((P2+I2)-(F2+H2))-J2,1)*24,100),2)
R2:R4	R2	=ROUND(DOLLARFR(MOD((MIN(P2+I2,F2+1)-(F2+H2))-J2,1)*24,100),2)
S2:S4	S2	=Q2-R2
K2:K4	K2	=MROUND(Q2,0.25)

 

[KRice]

Thanks for the additional details. I think the solution by @jasonb75 may be what you want, although I have a few questions. It appears that you want decimal hours, not minutes...could you confirm this please? Also, when the Total hours worked are rounded to a quarter hour in column K, what rounding rule do you apply? If someone works just past the quarter hour, do they receive credit for that full quarter hour, or not. Essentially, this asks whether the time worked is rounded up or down to the nearest quarter of an hour. Is it conceivable that a shift would ever span two midnight hours...I suspect not, but wanted to confirm.

Here is another version similar to Jason's that handles the rounding differently (rounds up).

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.5	3.75					9/7/2020	3.52	3.52	0.00		0.23
3	9/6/2020		16:32	20:09	0	3.75					9/6/2020	3.62	3.62	0.00		0.13
4	9/11/2020		22:36	1:18	0	26.75					9/13/2020	26.70	shift too long			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	=IFERROR(CEILING(Q2,0.25),"")
U2:U4	U2	=IFERROR(K2-Q2,"")

 

[spyldbrat]

To answer yours questions the time is displayed in decimal format and rounding is to the nearest quarter of an hour. (53 minutes to 7 minutes is round to .00 and 8 minutes to 22 minutes is rounded to 15, or .25 minutes).

Is this what you're trying to do? There appear to be some discrepancies in the required accuracy between the screen captures and the descriptions that you have provided.

Book2 (version 1).xlsb
	F		H	I	J	K					P	Q	R	S
2	09/07/20		16:32	20:33	0	4					09/07/20	4.02	4.02	0.00
3	09/06/20		16:32	20:09	0	3.5					09/06/20	3.62	3.62	0.00
4	09/12/20		22:36	01:18	0	2.75					09/13/20	2.70	1.40	1.30
Sheet7

Cell Formulas
Range	Formula
Q2:Q4	Q2	=ROUND(DOLLARFR(MOD(((P2+I2)-(F2+H2))-J2,1)*24,100),2)
R2:R4	R2	=ROUND(DOLLARFR(MOD((MIN(P2+I2,F2+1)-(F2+H2))-J2,1)*24,100),2)
S2:S4	S2	=Q2-R2
K2:K4	K2	=MROUND(Q2,0.25)

Jason, it looks like the formula in R isn't calculating correctly?

=ROUND(DOLLARFR(MOD((MIN(P3+I3,F3+1)-(F3+H3))-J3,1)*24,100),2)