Time Calc 100th of a Minute Broken by Day Worked

spyldbrat

Board Regular
Joined
May 5, 2002
Messages
200
Office Version
  1. 365
I have shifts that cross midnight (starts working today 2300 and leaves tomorrow at 0700). I need to calculate the total time worked through midnight so that the hours worked on each day displays in it's respective column and the time needs to display in 100ths of a minute. However, if the Total Hours Worked are "0.00", then I want my result to be 0.00. The formula I have works but once my shift crosses midnight, it errors out:

=IF(L20=0,0,IF(N20=E20,L20,HOUR($AC$1-I20)+MINUTE($AC$1-I20)/60))

Col E: Shift Date
Col I: Clock In
Col J: Clock Out
Col K: Lunch deduction (which did not include in my formula)
Col L: Total Time Worked (in Hundredths of a Minute). This field is automatically calculated by our system.
Col N: Shift End date
Col O: Current day's hours
Col P: Next day's hours
AC1: I have "12:00:00 AM" entered in this cell. (Not in screen shot)

Column P calculates the time by subtracting the Total Hours Worked (L) from the Minutes Current Day (P) providing the date in column N (Shift End Date) is greater than the Shift Date (Column E):
=IF(N20>E20,O20-L20,"0.00")

Here is a screen shot of the columns in question as well as an example of how I need to see my result.
1600947322926.png

Example:
DateIn 1Out 1Unpaid Break HoursTotal Time WorkedShift End DateMINUTES Current DayMINUTES Next Day
9/15/202010:5518:02
0​
7.03​
09/15/20​
7.03​
0.00
9/13/20207:116:45
0​
23.57​
09/14/20​
13.07​
10.50
9/16/202010:5518:02
0.3​
6.83​
09/16/20​
6.73​
0.00
9/15/20200:000:00
0​
0.00​
09/16/20​
0.00​
0.00
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,007
Office Version
  1. 2019
Platform
  1. Windows
Same formula that I provided for you in your earlier thread a couple of weeks ago. (The formula in Q2).

The data appears to have moved to different columns but the same method applies.
 

spyldbrat

Board Regular
Joined
May 5, 2002
Messages
200
Office Version
  1. 365
@jasonb75 - I didn't realize I could use the same formula. But, either way, if you recall, I told you it was not working on my end and it is still not working. :(
 

spyldbrat

Board Regular
Joined
May 5, 2002
Messages
200
Office Version
  1. 365
I think I missed your response but your response didn't resolve it anyway. I am going to go back to that thread and share some screen shots/formula's for you.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,139
Messages
5,546,160
Members
410,731
Latest member
keobongmacao
Top