formula for negative time
 

Jose,
Excel does not have a concept of negative time (or date for that matter) and
will produce an error.
VBA can work with negative time though, so may be UDF will work for you.

Depends on your aim.

NickHK

"Jose G. Jasso" <Jose G. wrote in message
...
trying to see if a formula in excel can calculate negative time. Example
after 40 hours would be displayed as negative time.

formula for negative time
 

"NickHK" wrote:

Jose,
Excel does not have a concept of negative time (or date for that matter) and
will produce an error.
VBA can work with negative time though, so may be UDF will work for you.

Depends on your aim.

NickHK

"Jose G. Jasso" <Jose G. wrote in message
...
trying to see if a formula in excel can calculate negative time. Example
after 40 hours would be displayed as negative time.


Thank you for the response. your advice does help, but I'm bent on trying to get this to work in excel somehow. if you have any other advice please reply.

formula for negative time
 

Jose,
Depends what you mean by "negative time" then.
You can display a number like -24, that indicates a deduction of 24 hours
from another number, but Excel cannot show this formatted as a Time (i.e.
with a value of -1).

NickHK

"Jose G. Jasso" wrote in message
...


"NickHK" wrote:

Jose,
Excel does not have a concept of negative time (or date for that matter)
and
will produce an error.
VBA can work with negative time though, so may be UDF will work for you.

Depends on your aim.

NickHK

"Jose G. Jasso" <Jose G. wrote in
message
...
trying to see if a formula in excel can calculate negative time.
Example
after 40 hours would be displayed as negative time.


Thank you for the response. your advice does help, but I'm bent on
trying to get this to work in excel somehow. if you have any other advice
please reply.

formula for negative time
 

"NickHK" wrote in message
Excel does not have a concept of negative time

Actually it does, sort of, at least for elapsed times. Times of day (clock
time) must be treated differently. You can do any sort of calculations with
negative times. The only restriction is that Excel can't display negative
time unless you are using the 1904 Date System. Enable the 1904 Date System,
and enter the following formula:

=TIME(9,0,0)-TIME(12,0,0)

Set the format to "hh:mm" and you'll get "-03:00" in the cell. This is
negative 3 hours, *not* 9PM the previous day.

The actual value of the cell is -0.125, or negative 1/8 of a day. This is
used for elapsed time, not time of day. 9 hours minus 12 hours is -3 hours,
not 9PM the previous day.

To get time of day, you still need to add 1 if the result is negative:

=D3-D4+(D3<D4)

where D3 is =TIME(9,0,0)
and D4 is =TIME(12,0,0)

This returns the expected 21:00:00, or 9PM, but then you're into positive
times.

Not terribly intuitive, but it does work.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)


"NickHK" wrote in message
...
Jose,
Excel does not have a concept of negative time (or date for that matter)
and
will produce an error.
VBA can work with negative time though, so may be UDF will work for you.

Depends on your aim.

NickHK

"Jose G. Jasso" <Jose G. wrote in message
...
trying to see if a formula in excel can calculate negative time. Example
after 40 hours would be displayed as negative time.

formula for negative time
 

Chip,
I did have some memory of possibilities, so thanks for the clarification.
Depending on what the OP is actually looking for, this may be an option.
Although to me "-3.00 AM" does not have much meaning if it does not relate
to a clock time.

Out if curiousity, why do the 2 date systems work differently ?
Something to do Lotus/Mac compatibility ?

NickHK

"Chip Pearson" wrote in message
...
"NickHK" wrote in message
Excel does not have a concept of negative time

Actually it does, sort of, at least for elapsed times. Times of day (clock
time) must be treated differently. You can do any sort of calculations
with
negative times. The only restriction is that Excel can't display negative
time unless you are using the 1904 Date System. Enable the 1904 Date
System,
and enter the following formula:

=TIME(9,0,0)-TIME(12,0,0)

Set the format to "hh:mm" and you'll get "-03:00" in the cell. This is
negative 3 hours, *not* 9PM the previous day.

The actual value of the cell is -0.125, or negative 1/8 of a day. This is
used for elapsed time, not time of day. 9 hours minus 12 hours is -3
hours,
not 9PM the previous day.

To get time of day, you still need to add 1 if the result is negative:

=D3-D4+(D3<D4)

where D3 is =TIME(9,0,0)
and D4 is =TIME(12,0,0)

This returns the expected 21:00:00, or 9PM, but then you're into positive
times.

Not terribly intuitive, but it does work.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)


"NickHK" wrote in message
...
Jose,
Excel does not have a concept of negative time (or date for that matter)
and
will produce an error.
VBA can work with negative time though, so may be UDF will work for you.

Depends on your aim.

NickHK

"Jose G. Jasso" <Jose G. wrote in
message
...
trying to see if a formula in excel can calculate negative time.
Example
after 40 hours would be displayed as negative time.

formula for negative time
 

Something to do Lotus/Mac compatibility ?

Actually for compatibility with DOS. Excel was originally written for the
Mac.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)


"NickHK" wrote in message
...
Chip,
I did have some memory of possibilities, so thanks for the clarification.
Depending on what the OP is actually looking for, this may be an option.
Although to me "-3.00 AM" does not have much meaning if it does not relate
to a clock time.

Out if curiousity, why do the 2 date systems work differently ?
Something to do Lotus/Mac compatibility ?

NickHK

"Chip Pearson" wrote in message
...
"NickHK" wrote in message
Excel does not have a concept of negative time

Actually it does, sort of, at least for elapsed times. Times of day
(clock
time) must be treated differently. You can do any sort of calculations
with
negative times. The only restriction is that Excel can't display negative
time unless you are using the 1904 Date System. Enable the 1904 Date
System,
and enter the following formula:

=TIME(9,0,0)-TIME(12,0,0)

Set the format to "hh:mm" and you'll get "-03:00" in the cell. This is
negative 3 hours, *not* 9PM the previous day.

The actual value of the cell is -0.125, or negative 1/8 of a day. This is
used for elapsed time, not time of day. 9 hours minus 12 hours is -3
hours,
not 9PM the previous day.

To get time of day, you still need to add 1 if the result is negative:

=D3-D4+(D3<D4)

where D3 is =TIME(9,0,0)
and D4 is =TIME(12,0,0)

This returns the expected 21:00:00, or 9PM, but then you're into positive
times.

Not terribly intuitive, but it does work.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)


"NickHK" wrote in message
...
Jose,
Excel does not have a concept of negative time (or date for that
matter)
and
will produce an error.
VBA can work with negative time though, so may be UDF will work for
you.

Depends on your aim.

NickHK

"Jose G. Jasso" <Jose G. wrote in
message
...
trying to see if a formula in excel can calculate negative time.
Example
after 40 hours would be displayed as negative time.

formula for negative time
 

??

The 2 date systems are there for lotus compatability / original Excel/Mac

but why does the different time display have anything to do with dos
Surely if Excel was written for Mac it used the 1904 system

Excel for Windows changed to 1900 for compatability with Lotus


Where does Dos come into it ?
Not challenging your point, just interested.

Steve


On Wed, 06 Dec 2006 06:19:20 -0000, Chip Pearson wrote:

Something to do Lotus/Mac compatibility ?

Actually for compatibility with DOS. Excel was originally written for
the
Mac.