subtract one hour from time

  • Thread starter Thread starter J.W. Aldridge
  • Start date Start date
J

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
 
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
 
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)
 
Back
Top