Problem calculating Days/Hours etc between 2 dates

[Paul Sheppard]

I want to calculate how many Days/Hours/Minutes/Seconds between Now and
a future date

eg how many Days/Hours/Minutes/Seconds between Now and 08:25 on 9th
September 2005

I have used the formula below

=(INT(B7-TODAY())-(MOD(B7,1)-MOD(NOW(),1)<0))&" Days
"&TEXT(MOD(B7-NOW(),1),"hh")&" Hours "&TEXT(MOD(D6-NOW(),1),"mm") &"
Minutes "&TEXT(MOD(D6-NOW(),1),"ss") &" Seconds "

This returns the result 32 Days 14 Hours 01 Minutes 58 Seconds , which
is wrong as there are 34 Days until 9th September, what is wrong with my
formula?

When I F9 to update only the seconds are changing

Second question is there any way to automate this so that it counts
down on the screen without having to recalculate

Paul

 

[KL]

Hi Paul,

You can try this:

=DATEDIF(NOW(),B7,"d")&" Days "&TEXT(B7-NOW(),"hh ""Hours ""mm ""Minutes
""ss ""Seconds""")

Regards,
KL


"Paul Sheppard" <[email protected]>
wrote in message
news[email protected]...

 

[Bob Phillips]

=INT(B7-TODAY())&" Days "&TEXT(MOD(B7-TODAY(),1),"hh")&" Hours
"&INT(MOD(MOD(B7-TODAY(),1)*24,1)*60)&"
Minutes "&ROUND(MOD(MOD(MOD(B7-TODAY(),1)*24,1)*60,1)*60,0)&" Seconds "


The second part is more complex as you will need APIs to link to a timer so
that it doesn't tie up your machine. See here for an example

http://tinyurl.com/c3xu5

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Paul Sheppard" <[email protected]>
wrote in message
news[email protected]...