Having problems w/Excel and negative time.

November 9, 2010 at 05:06:05
Specs: Windows XP
Using columns
A - Name
B - Time In (formatted to ex. 1:30 pm)
C - Time Out (same as above)
D - Break (entered as positive time, always .50)
E - Total (using formula of ((C2-B2)*24)-D2

Works fine until my 2nd shift. Ex. 3:30pm to 1:00am is figuring as -15. I need that to be a positive number as I have 5 worksheets (Mon, Tues, Wed, Thurs, Fri) that I use a sixth sheet to total the hours for the week.

HELP!!!!!!


See More: Having problems w/Excel and negative time.

Report •


#1
November 9, 2010 at 05:39:53
Try Custom Formatting your total hours as: [H]:MM:SS

The square brackets around the H are required.

MIKE

http://www.skeptic.com/


Report •

#2
November 9, 2010 at 06:14:45
Got the dreaded ########. Expanding the column didn't work.

Report •

#3
November 9, 2010 at 07:04:19
If your going to be doing TIME math that crosses over Midnight then using DATE-TIME format makes life easier, so that:

10:30 PM becomes 11/08/2010 10:30 PM
05:30 AM becomes 11/09/2010 05:30 AM

Confusing TIME with Numbers:

Break (entered as positive time, always .50)

How much TIME is that supposed to be?
Fifty Minutes?
Or Fifty percent of an Hour? IE A half hour or 30 Minutes?

Is it ALWAYS fifty? Never any other number?

A quick and easy way if the .50 is supposed to be 30 Minutes:

((C2-B2)*24)-TIME(,30,)

If it really is 50 Minutes then:

((C2-B2)*24)-TIME(,50,)

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
November 9, 2010 at 07:32:03
That worked like a charm! Thanks so much. They are picky about showing the silly break even though it is the same every day for every person. It's half an hour for the .50. Thank you again! I am set with my workbook. You made me a hero to my other branch. :0)

Report •

#5
November 9, 2010 at 08:04:10
Glad I could help.

MIKE

http://www.skeptic.com/


Report •

Ask Question