Time Calculation Question

spyldbrat

Board Regular
Joined
May 5, 2002
Messages
200
Office Version
  1. 365
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))
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
956
Office Version
  1. 2019
Platform
  1. Windows
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.?
 
Last edited:

spyldbrat

Board Regular
Joined
May 5, 2002
Messages
200
Office Version
  1. 365
Sorry.
F and P are dates. The rest is time displayed as 13:30.
 

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
956
Office Version
  1. 2019
Platform
  1. Windows
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

Board Regular
Joined
May 5, 2002
Messages
200
Office Version
  1. 365

ADVERTISEMENT

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

Board Regular
Joined
May 5, 2002
Messages
200
Office Version
  1. 365
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.
1600530336863.png


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

Board Regular
Joined
Jul 12, 2008
Messages
206
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Dec 30, 2008
Messages
11,885
Office Version
  1. 365
Platform
  1. Windows
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
FHIJKPQRS
209/07/2016:3220:330409/07/204.024.020.00
309/06/2016:3220:0903.509/06/203.623.620.00
409/12/2022:3601:1802.7509/13/202.701.401.30
Sheet7
Cell Formulas
RangeFormula
Q2:Q4Q2=ROUND(DOLLARFR(MOD(((P2+I2)-(F2+H2))-J2,1)*24,100),2)
R2:R4R2=ROUND(DOLLARFR(MOD((MIN(P2+I2,F2+1)-(F2+H2))-J2,1)*24,100),2)
S2:S4S2=Q2-R2
K2:K4K2=MROUND(Q2,0.25)
 

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
956
Office Version
  1. 2019
Platform
  1. Windows
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
FGHIJKLMNOPQRSTU
1Date Shift StartTime InTime OutLunch (decimal hours)Total Hrs Worked (rounded 1/4 h)Date Shift EndTotal Hours Worked (decimal)Hours Current DayHours Next DayHours Var
29/7/202016:3220:330.53.759/7/20203.523.520.000.23
39/6/202016:3220:0903.759/6/20203.623.620.000.13
49/11/202022:361:18026.759/13/202026.70shift too long 0.05
Sheet1
Cell Formulas
RangeFormula
Q2:Q4Q2=IF(ROUND(((P2+I2)-(F2+H2))*24,2)-J2>=0,ROUND(((P2+I2)-(F2+H2))*24,2)-J2,"not possible")
R2:R4R2=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:S4S2=IFERROR(Q2-R2,"")
K2:K4K2=IFERROR(CEILING(Q2,0.25),"")
U2:U4U2=IFERROR(K2-Q2,"")
 

spyldbrat

Board Regular
Joined
May 5, 2002
Messages
200
Office Version
  1. 365
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
FHIJKPQRS
209/07/2016:3220:330409/07/204.024.020.00
309/06/2016:3220:0903.509/06/203.623.620.00
409/12/2022:3601:1802.7509/13/202.701.401.30
Sheet7
Cell Formulas
RangeFormula
Q2:Q4Q2=ROUND(DOLLARFR(MOD(((P2+I2)-(F2+H2))-J2,1)*24,100),2)
R2:R4R2=ROUND(DOLLARFR(MOD((MIN(P2+I2,F2+1)-(F2+H2))-J2,1)*24,100),2)
S2:S4S2=Q2-R2
K2:K4K2=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)

1600617247877.png
 

Watch MrExcel Video

Forum statistics

Threads
1,122,207
Messages
5,594,841
Members
413,944
Latest member
3xc3ln00b

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top