Subtracting hours from time to calculate overtime hours

**nabilishes** · 10-16-2012, 03:03 AM

Ive 4 column
First column (A1) is "Time In". Second column (B1) is "Time Out" and the Third Column (C1) is the "Hours Work" and Fouth (D1) is "Overtime"
I have succesfully calculate the time difference to get the "hours work". To do this i subtracted B1-A1. Both cells are formatted with hh:mm

THe problem is now i want to calulcate the overtime hours and minutes. The required amount of hours per day is let say 8 hours. So i want to subtract 8 hours with the "Hours Work" column to get the value.
The value can be a negative value or positve value. I couldt get i working.

Any inputs?

**Dave H9** · 10-16-2012, 03:18 AM

in cell d1 -

Please Login or Register  to view this content.

Where 0.33 is excels decimal equivalent for 8 hours.

Dave H

**nabilishes** · 10-16-2012, 03:29 AM

If my C1 column is 08:45 which means ive worked for 8 hours and 45 minutes, when i minus it with 0.33, the Overtime (D1) came as 00:49. It is supposed to be 0:45

And when the "Hours Work" (C1) is 06:45 which means ive worked for 6 hours and 45 minutes, when i minus the value with 0.33, only ####### showed in the D1 cell

**Dave H9** · 10-16-2012, 03:34 AM

Ah yes I had my cell set to two decimal places. Actual decimal for 08:00 is 0.333333333333333.

Excel, as far as I know cannot display negative times, I'll have a think of a way around it.

**Kevin UK** · 10-16-2012, 03:38 AM

Hi nabilishes

See if this is what you require!

Formula:

Please Login or Register  to view this content.

**nabilishes** · 10-16-2012, 04:02 AM

@Kevin UK
it worked if C1 is 08:45. it shows an overtime of 45 minutes
But if C1 is 06:30 which means that ive worked for 6 hours and 30 minutes, at the overtime column it displays 06:30, THe value should be a minus 1:30 minutes to show that i am short of 1 hour and 30 mins

**nabilishes** · 10-16-2012, 04:07 AM

Originally Posted by Dave H9

Ah yes I had my cell set to two decimal places. Actual decimal for 08:00 is 0.333333333333333.

Excel, as far as I know cannot display negative times, I'll have a think of a way around it.

Dave, ive tried before with this (see below) because i know excel cannot display negative time. With this formula it shows a negative value, but in text form. The problem is i couldn´t add the total overtime hours at the end of the month. Thats why i created this post to ask for antother method for the subtraction.

p/s: G14 is my C1 in my actual table

Formula:

Please Login or Register  to view this content.

p/s/s: The semicolon is used instead of a coma in the formula because the excel at my office is installed in a german environment. Hence the difference. Below is the formula for a normal windows environment Excel

Formula:

Please Login or Register  to view this content.

**Kevin UK** · 10-16-2012, 04:08 AM

Sorry my mistake, if the hours are less the 8 it should return nothing, here is the revision:

Formula:

Please Login or Register  to view this content.

**nabilishes** · 10-16-2012, 04:14 AM

i would like to show the shortage hours as well, so that at the end of the month i could add all the overtime/shortage hours and see the extra hours i had worked or to fill up incomplete working hours.

**Kevin UK** · 10-16-2012, 04:26 AM

Try this!

Formula:

Please Login or Register  to view this content.

**nabilishes** · 10-16-2012, 04:39 AM

This is something similar to my version in post #7.
It works but the problem is when trying to add the results at the end of the month, it will show #VALUE! in the cell. I think this is due to the negative value being a text. I had this problem as well.
For instance, when D1 is 00:45 and D2 is -1:30, adding =D1+D2 doesnt give out the result but instead #VALUE!

**Kevin UK** · 10-16-2012, 04:44 AM

As far as I am aware, you can not display Time as a negative value, that is why you are getting errors when you try to sum the column, but I stand corrected on that.
You probably be better off having another column for times under you standard times.