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.