NEGATIVE HOURS

[André Lavoie]

Would like to cumulate work time in a schedule. I have to compare worked
time to a maximun dayly working time of 7:00 hours. so if I worked 5 hours
out of 7 I would like to obtain minus 2 hours (-2:00) I tried a1-a2 format
to day and time... No succes

Any ideas ???

André Lavoie

 

[Tom Ogilvy]

If you use the 1904 date system in excel, you can use negative time
(tools=>options=>calculate tab).

However, this will probably make all your dates off by 4 years.

If you want to use decimal number of hours you can

=(a1-a2)*24

 

[Jerry W. Lewis]

It also has the potential to cause confusion, since 1/24 and -1/24 are
both displayed as 1:00:00 AM (i.e. there is no formatted indication that
the time is negative).

Jerry

 

[JE McGimpsey]

In MacXL, -1/24 displays as -1:00:00 AM.

Is this a bug in WinXL?

 

[Jerry W. Lewis]

In Excel XP, the first time format is listed as "*1:30:55 PM" in the
Time category and "[$-409]h:mm AM/PM;@" in the Custom category. It does
not show the sign for negative times in the 1904 date system. All other
time formats do show the sign (I never use the 1904 system in practice,
and got snookered by it being the first one, sorry). Interestingly,
that custom format in Excel 2000 does display the sign on negative
times. I don't have access to Excel 2003 tonight to see what it does.

Jerry

 

[JE McGimpsey]

In XL03, the *13:30:55 format also shows 1:00:00 for -1/24.

The format note indicates that that format "switch(es) date or time
orders with the operating system", whatever that means.

Frankly, if that's not a bug, it's at least a terrible design decision.

 

[Tom Ogilvy]

Excel 97 SR2 on Win 98 SE displayed the negative sign as you show the MAC
doing.

 

[Jerry W. Lewis]

While we are on the subject of design decisions, does MacXL date display
-1 as -1/2/1904 in the 1904 date system?

Jerry

In XL03, the *13:30:55 format also shows 1:00:00 for -1/24.

The format note indicates that that format "switch(es) date or time
orders with the operating system", whatever that means.

Frankly, if that's not a bug, it's at least a terrible design decision.

In Excel XP, the first time format is listed as "*1:30:55 PM" in the
Time category and "[$-409]h:mm AM/PM;@" in the Custom category. It does
not show the sign for negative times in the 1904 date system. All other
time formats do show the sign (I never use the 1904 system in practice,
and got snookered by it being the first one, sorry). Interestingly,
that custom format in Excel 2000 does display the sign on negative
times. I don't have access to Excel 2003 tonight to see what it does.

 

[JE McGimpsey]

Yup. Definitely a bad design decision, IMO. There are certainly
legitimate uses for "negative hours". I can't think of a single use for
negative dates.

In my production apps, I often redefine the Date Style to something like

mm/dd/yy;-0;0;@