subtract one hour from time

[J.W. Aldridge]

I am converting EST to CST and rounding down to nearest half hour.

The rounding portion, I got....

=FLOOR(F2,1/48)

Having trouble subtracting one hour from the result.

thanx

 

[Dave Peterson]

=FLOOR(F2,1/48) - time(1,0,0)
or
=FLOOR(F2,1/48) - 1/24

 

[J.W. Aldridge]

Thanx Mr. Dave, but still getting ######### as a result.

 

[J.W. Aldridge]

I think its the fact that the time is 00:00 (midnight). Is there a way
to include the 00 time to revert back to the previous day... ie 23:00

 

[T. Valko]

Try this:

=FLOOR(F2+(F2<TIME(1,0,0)),1/48) - TIME(1,0,0)

 

[Dave Peterson]

If the resulting time is negative, then you'll see this #### displayed.

You could change a setting:
(In xl2003 menus)
Tools|Options|Calculation tab|check the 1904 date system
And you'll see negative time.

Be aware that if your workbook contains dates, then they'll change (each date
will be 4 years and one day later).

Or maybe you can use a formula like:
=mod(FLOOR(F2,1/48) - time(1,0,0),1)
or
=mod(FLOOR(F2,1/48) - 1/24,1)

 

[J.W. Aldridge]

thanx all!