Calculating times and hours

[jkerlin]

I am trying to create a timecard that calculates hours and minutes based on actual times that I input into a cell.

To break this down:

Morning
7:00am (A1) to 12:10pm (B1) [both formatted as h:mm AM/PM] so the total hours is =(B1-A1), or 5:10 (C1), formatted as [h]:mm;

Afternoon
12:50pm (D1) to 3:25pm (E1) (both formatted as h:mm AM/PM) so the total hours is =(E1-D1), or 2:35 (F1), formatted as [h]:mm;

Now I want to add an overtime column (G1) that will subtract the [original hours] from the [original hours + the overtime hours], with the original MORNING hours always being 5:10 and the original AFTERNOON hours always being 2:35. So say I worked 2 hours of overtime in the afternoon, making cell (E1) 5:25 instead of 3:25. My total hours in (F1) would then be 4:35 instead of 2:35.

In my OT cell (G1), I used this formula:

=((C1)-"5:10")+((F1)-"2:35")

Essentially, this is taking the total hours in the AM (5:10), subtracting 5:10 since that is the original hours, which equals 0, since no OT hours were worked in the AM. In the afternoon, this formula takes the total hours in the PM (4:35), and subtracts 2:35, since 2:35 is the original amount of hours, which should equal 2:00 hours.

When I enter these figures into my spreadsheet, it will calculate the 2:00 hours, but if I try to drag and copy the formula, I get ####. It also won't add properly when I try to SUM all the OT cells.

Does anyone have ANY idea how to make this work? It's literally driving me insane.
Thanks!

[zbor]

Attach a sample workbook. 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.

[jkerlin]

I tried attaching, please let me know if it worked.

[JohnTopley]

Try

In OT

=ROUND((D5)-TIME(5,10,0)+(G5)-TIME(2,35,0),10)

[Aardigspook]

Welcome to the forum.

The #### is because Excel is finding a negative time (and it doesn't understand 'zero' time either, sometimes). If you change the 3:25 in F2 to 3:25:01, you'll get the answer you expect. However, if you change it to 3:24, you'll get the ##### error again, because the 'overtime' is negative.
Try using this instead - it will put in 0 'manually' if the overtime calculates to less than or equal to 0:

Please Login or Register  to view this content.

I haven't done much testing on this, so let me/us know if it doesn't work for some of your times.

Edit: I combined your two times into one, but you could keep them separate:

Please Login or Register  to view this content.

[jkerlin]

THANK YOU! This does seem to work! The only thing now, I have one line with only morning hours and no afternoon hours. So it only goes:
MORNING
7:00 AM to 12:00 PM totaling 5:00 hours. When I copied the formula from above into all 5 OT cells for the week, I still got #### in this last row, I'm assuming because the time is different and we aren't adding any afternoon hours either. How do I modify that formula to make this last row work?

[Aardigspook]

Which formula are you using - mine or John's?

[JohnTopley]

Something like...

=ROUND((D9)-TIME(5,0,0)+IF(G9>0,G9-TIME(2,35,0),0),10)

You will get an error if you subtract 5:10.

I find your OT calculation most confusing: most OT calculations are done based on a standard day of say 8 hours: hours in excess of this are OT.

[jkerlin]

Sorry--I didn't see your post before I responded. I have never used this site before so please bear with me!

I used John's formula and it worked in all my rows (Monday thru Thursday), except Friday. Friday hours show 7:00 am to 12:00 pm with no afternoon hours, so I wasn't sure how to adjust his formula accordingly.

@Aardigspook - I could not get your formula to work correctly.

[jkerlin]

For whatever reason, this isn't letting me respond to your individual messages.

@JohnTopley - You are a lifesaver. That formula worked. THANK YOU. And I completely agree. We have a 36-hour work week here, so the numbers on this timecard are very confusing.

Thank you both for your assistance!

[Aardigspook]

You're welcome - glad we (John, at least) could be of help. Thanks for the feedback.
If that's your problem solved, please mark the thread as solved so others know there's an answer here - see my sig for how.