Roundown and subtract time (Including Midnight)

[J.W. Aldridge]

I have info/times that come in EST starting with column F6 down.

I am trying to do two functions here but cant seem to get my formulas
to marry.

a) I want to round down the time to the beginning hour.
=(ROUNDDOWN(F6*24,0)/24)
b) I want to subtract an hour from whatever hour is given. = -"1:00"

Issue: If the hour is midnight 0:47, it should reflect 23:47 but it is
not when using this formula. It coming up ##### for any time during
midnight.

Midnight hours are the only issue.

=(ROUNDDOWN(F6*24,0)/24)-"1:00"

 

[Bob Phillips]

=(ROUNDDOWN(F6*24,0)/24)-"1:00"+(F6<TIME(1,0,0))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[J.W. Aldridge]

thanx!

worked purfectly!!

 

[daddylonglegs]

You could also use this formula

=FLOOR(MOD(F6-"1:00",1),"1:00")

format as time