Cells formatted as "time" do not calculate correctly

Forum Users: I have a spreadsheet where the Over Time column does not calculate correctly. I have copied my excel sheet and posted below - also I copied the formula for the Over Time next to the column. would appreciate if someone could help me.


Today's Date 4/18/18 8:00

Current Time 12:26 6:00
0:30
Driver Bid Start P&D Hours Total Hrs O/T
5:00 5:00 12:30 13:00 4:30 Formula for Overtime: =IF(AA24="","",IF(Y24<=$AA$2,"",Y24-$AA$2))
5:00 10:30 1:26 1:56 17:26 The following two columns show O/T that are errors....
5:00 12:00 23:56 0:26 15:56

Thanks for helping me on this.

TigerA

Attach a sample workbook (not image).

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Note: When Drivers start later and as the day progresses, the Overtime column starts reflecting excessive hours. Earlier driver start times reflect the correct overtime.

Cells are formatted as Time, i.e., 13:30. Also I have a problem adding the time at the bottom of the sheet using the SUM formula.

Appreciate your help in this matter.

TigerA

Thank you - I'll make the corrections when I get to work. I really appreciate your response, I have been wrestling with this for a week.

TigerA

Thanks - I am in the process reformatting the cells as you stated in your reply. Is there a way I can put time in the Current Time field and use the "Refresh" button to update the time. For example, if I put in 09:00 and at 9:30 I want to see the "Total Hours", it would update the field. The Refresh button is much easier than typing in the time - we refer to this sheet constantly.

You have been extremely helpful - Thanks. TigerA

Please Login or Register  to view this content.

This ???

Thanks John - it works perfectly. Thanks so much for your help.

This was a two week case of frustration - TigerA.

Team Excelforum - Is there a macro or IF statement or ISNUMBER statement that would check a cell to determine if it has a macro in it - and if it does, will overwrite that macro with a VALUE?

Where do I go to post a new thread? I have looked over the header and cannot find where to post. Could someone help me?

TigerA