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

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

--
Regards,
Tom Ogilvy

"André Lavoie" wrote in message
.. .
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

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

Tom Ogilvy wrote:

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

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

Is this a bug in WinXL?


In article ,
"Jerry W. Lewis" wrote:

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).

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 wrote:

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

Is this a bug in WinXL?


In article ,
"Jerry W. Lewis" wrote:


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).

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 article ,
"Jerry W. Lewis" wrote:

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.

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

--
Regards,
Tom Ogilvy

"JE McGimpsey" wrote in message
...
In MacXL, -1/24 displays as -1:00:00 AM.

Is this a bug in WinXL?


In article ,
"Jerry W. Lewis" wrote:

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).

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

Jerry

JE McGimpsey wrote:

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 article ,
"Jerry W. Lewis" wrote:


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.

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;@

In article ,
"Jerry W. Lewis" wrote:

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