Problem calculating Days/Hours etc between 2 dates
 

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


--
Paul Sheppard
------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=393641

Hi Paul,

You can try this:

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

Regards,
KL


"Paul Sheppard"
wrote in message
news:Paul.Sheppard.1tdzma_1123405501.6905@excelfor um-nospam.com...

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


--
Paul Sheppard
------------------------------------------------------------------------
Paul Sheppard's Profile:
http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=393641

=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"
wrote in message
news:Paul.Sheppard.1tdzma_1123405501.6905@excelfor um-nospam.com...

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


--
Paul Sheppard
------------------------------------------------------------------------
Paul Sheppard's Profile:
http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=393641