Subtracting a specific time within a formula

[SandsB]

I'm using the 1904 date system and I can subtract a time value in one cell from the time value in another cell even it it results in a negative number. What I can't do is subtract a time value in one cell from a time specified in a formula like this:

=C1-02:00:00

C1 is a time in hh:mm:ss format.

Is this possible?

 

[AlphaFrog]

Try something like this...
=C1-TIMEVALUE("02:00:00")

Or This...
=C1-TIME(2,0,0)

Or just this...
=C1-("02:00:00")

 

[SandsB]

That returns #VALUE!

 

[T. Valko]

I'm using the 1904 date system and I can subtract a time value in one cell from the time value in another cell even it it results in a negative number. What I can't do is subtract a time value in one cell from a time specified in a formula like this:

=C1-02:00:00

C1 is a time in hh:mm:ss format.

Is this possible?

Why don't you just enter the other time value in a cell?

D1 = 2:00:00

=C1-D1

Or...

=C1-TIME(2,0,0)

Assuming 02:00:00 is 2:00 AM or 2hrs 0mins.

 

[AlphaFrog]

My guess is C1 is just text that looks like time, but Excel doesn't see it as a serial time value.

Or double check you really have the 1904 date system selected

Can you give an example of what exactly is in C1?

 

[SandsB]

What the.....?

If I use C1-("02:00:00") in one cell it works. The exact same formula pasted into another cell returns #VALUE!. I checked the cell format and both are Time values of 37:33:55. Why would the two cells produce different results?

 

[Toonies]

Hi for Negative Time check this out

http://www.mrexcel.com/forum/showthread.php?t=514190&highlight=negative+time

it may save you having to use the 1904 date