negative time input

[Big Mac]

I would like to be able to do is to input a negative. All of the previous
posts seem to show how to show the result of subtracting a large number of
hours from a smaller number - The 1904 format will give the negative answers
results.

I would like to be able to enter -ve times such as -1hr 24m in one of the
input cells in order total a row of times e.g.

A1 B1 C1
2:00 -1:24 = 0:34 =sum(a1:b1) etc
2:00 -3:13 = -1:13

is this possible?

Thanks in advance

 

[David Biddulph]

Under the 1904 system you can include negative times, but it still won't let
you type in -1:24. You can, however, type in -"1:24" and format the cell as
time.

 

[Big Mac]

That was superb- Thanks David

 

[Marcelo]

Because Excel stores dates and times as numeric values, it's possible to add
or subtract one from the other.

However, if you have a workbook containing only times (no dates), you may
have discovered that subtracting one time from another doesn't always work.
Negative time values appear as a series of hash marks (########), even though
you've assigned the [h]:mm format to the cells.

By default, Excel uses a date system that begins with January 1, 1900. A
negative time value generates a date/time combination that falls before this
date, which is invalid.

The solution is to use the optional 1904 date system. Select Tools, Options,
click the Calculation tab, and check the 1904 date system box to change the
starting date to January 2, 1904. Your negative times will now be displayed
correctly, as shown below.

Be careful if you workbook contains links to other files that don't use the
1904 date system. In such a case, the mismatch of date systems could cause
erroneous results.

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Big Mac" escreveu:

 

[Big Mac]

Thank you marcello, that works nicely as well as Davids solution. I have also
formatted the cells as hh:mm;[Red]-hh:mm for good measure.
--

Because Excel stores dates and times as numeric values, it's possible to add
or subtract one from the other.

However, if you have a workbook containing only times (no dates), you may
have discovered that subtracting one time from another doesn't always work.
Negative time values appear as a series of hash marks (########), even though
you've assigned the [h]:mm format to the cells.

By default, Excel uses a date system that begins with January 1, 1900. A
negative time value generates a date/time combination that falls before this
date, which is invalid.

The solution is to use the optional 1904 date system. Select Tools, Options,
click the Calculation tab, and check the 1904 date system box to change the
starting date to January 2, 1904. Your negative times will now be displayed
correctly, as shown below.

Be careful if you workbook contains links to other files that don't use the
1904 date system. In such a case, the mismatch of date systems could cause
erroneous results.

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Big Mac" escreveu:

That was superb- Thanks David

 

[David Biddulph]

I had taken it as a given from your original message that you were already
using the 1904 system.
--
David Biddulph

Thank you marcello, that works nicely as well as Davids solution. I have
also
formatted the cells as hh:mm;[Red]-hh:mm for good measure.

Because Excel stores dates and times as numeric values, it's possible to
add
or subtract one from the other.

However, if you have a workbook containing only times (no dates), you may
have discovered that subtracting one time from another doesn't always
work.
Negative time values appear as a series of hash marks (########), even
though
you've assigned the [h]:mm format to the cells.

By default, Excel uses a date system that begins with January 1, 1900. A
negative time value generates a date/time combination that falls before
this
date, which is invalid.

The solution is to use the optional 1904 date system. Select Tools,
Options,
click the Calculation tab, and check the 1904 date system box to change
the
starting date to January 2, 1904. Your negative times will now be
displayed
correctly, as shown below.

Be careful if you workbook contains links to other files that don't use
the
1904 date system. In such a case, the mismatch of date systems could
cause
erroneous results.
"Big Mac" escreveu:

That was superb- Thanks David
:

Under the 1904 system you can include negative times, but it still
won't let
you type in -1:24. You can, however, type in -"1:24" and format the
cell as
time.
I would like to be able to do is to input a negative. All of the
previous
posts seem to show how to show the result of subtracting a large
number of
hours from a smaller number - The 1904 format will give the
negative
answers
results.

I would like to be able to enter -ve times such as -1hr 24m in one
of the
input cells in order total a row of times e.g.

A1 B1 C1
2:00 -1:24 = 0:34 =sum(a1:b1) etc
2:00 -3:13 = -1:13

is this possible?

Thanks in advance