# Time Calc 100th of a Minute Broken by Day Worked

#### spyldbrat

##### Board Regular
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.

Example:
 Date In 1 Out 1 Unpaid Break Hours Total Time Worked Shift End Date MINUTES Current Day MINUTES Next Day 9/15/2020 10:55 18:02 0​ 7.03​ 09/15/20​ 7.03​ 0.00 9/13/2020 7:11 6:45 0​ 23.57​ 09/14/20​ 13.07​ 10.50 9/16/2020 10:55 18:02 0.3​ 6.83​ 09/16/20​ 6.73​ 0.00 9/15/2020 0:00 0:00 0​ 0.00​ 09/16/20​ 0.00​ 0.00

### Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

#### jasonb75

##### Well-known Member
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
@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.

#### jasonb75

##### Well-known Member
if you recall, I told you it was not working on my end
I replied to that but there was nothing else from you afterwards...

#### spyldbrat

##### Board Regular
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.

Replies
2
Views
155
Replies
8
Views
710
Replies
1
Views
149
Replies
7
Views
245
Replies
4
Views
262

Threads
1,182,107
Messages
5,933,694
Members
436,905
Latest member
Ibraeh

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

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