Time/Date subtract >24 hours

[ET902]

I am trying to show the date and time when I subtract hours:min from a date
and time. Currently the formula I am using is $I$64-TIME(F14,G14,H14) where
I64 contains the date and time, F14 is hours, G14 is minutes and H14 is
seconds. Everything works great until I get to 24 hours. The date reverts
back to the original date, but the time is correct. How do I get the date to
calculate correctly?

 

[Toppers]

try:

=$I$64-TIME(F14,G14,H14)-INT(F14/24)

 

[ET902]

Ta Da!! That did it.

But exactly what does the Int (f14/24) do?

 

[Toppers]

Dates are held as whole (Integer) numbers and times as "fractions/decimals"
of a day: 24 hours = 1 , 12 hours=0.5.

For example, today (12th Sept 2006 ) at 12 midday will be stored in Excel as
38972.50000 (in $I$64) ; the 38972 is the date serial number, where
01/01/1900=1.

Dividing the hours (F1) by 24 will return the number of days and the INT
will remove any "part" day i.e. return a whole number. Hence, if F1=27, then
F1/24=1.125 and INT(F1/24)=1 ; if F1=12 then F1/24=0.5 and INT(F1/24)=0

So using today's date serial ( 38972) with F1=27, then INT(F1/24)=1 so we
would get 38972-1 = 38971 i.e 11th September 2006.

HTH

 

[ET902]

Outstanding. I have now met my "learn something everyday" requirement. Thanks
Topper for the explanation.