Time Calc 100th of a Minute Broken by Day Worked

[spyldbrat]

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

 

[jasonb75]

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]

@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]

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]

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.