Excel countdown

[Art MacNeil]

Hello,

I would like to find a formula in excel that will countdown years, months,
days, hours, minutes and seconds from one point in time to another. Datedif
works for years, months and days, but I can't find a function that
calculates hours, minutes and seconds. Does such a thing exist?

Thank you for your time,

Art.

 

[Peo Sjoblom]

This formula counts down from now until Christmas, refresh by pressing F9

=IF(TODAY()>DATE(YEAR(TODAY()),12,24),"Wait Until After New
Year",DATEDIF(NOW(),DATE(YEAR(TODAY()),12,24),"YM")&" Month(s),
"&DATEDIF(NOW(),DATE(YEAR(TODAY()),12,24),"MD")&" Day(s),
"&TEXT(23-HOUR(NOW()),"###")&" Hour(s), "&TEXT(59-MINUTE(NOW()),"###")&"
Minute(s), and "&TEXT(59-SECOND(NOW()),"###")&" Second(s) until Christmas.")

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon

 

[Art MacNeil]

Excellent, thank you.

This formula counts down from now until Christmas, refresh by pressing F9

=IF(TODAY()>DATE(YEAR(TODAY()),12,24),"Wait Until After New
Year",DATEDIF(NOW(),DATE(YEAR(TODAY()),12,24),"YM")&" Month(s),
"&DATEDIF(NOW(),DATE(YEAR(TODAY()),12,24),"MD")&" Day(s),
"&TEXT(23-HOUR(NOW()),"###")&" Hour(s), "&TEXT(59-MINUTE(NOW()),"###")&"
Minute(s), and "&TEXT(59-SECOND(NOW()),"###")&" Second(s) until
Christmas.")

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon

 

[Art MacNeil]

Hello,

I tried this formula and it works if you're counting down to a future
date, as long as the ending time is midnight. Is there a way to count down
from today (or some other date) to a future date and display the result as a
sentence that shows years (if applicable), months, days, hours, minutes and
seconds?

In this case I use the following format for time:

dddd, mmmm d, yyyy h:mm:ss AM/PM


My starting time is almost always =NOW() (with the format above) and the
future date/time uses the same formatting.

Thank you for any help,

Art.

 

[Peo Sjoblom]

With a future date and time in A1 you can try

=DATEDIF(NOW(),A1,"Y")&" Years "&DATEDIF(NOW(),A1,"YM")&"
Months,"&DATEDIF(NOW(),A1,"MD")&" Days,
"&TEXT(MOD(TIME(HOUR(A1),MINUTE(A1),SECOND(A1))-TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW())),1),"h
""hours, ""m ""minutes and "" s"" seconds""")

however there is a problem and that is that datedif works per full day so if
the future date is Jan 24 2009 11:30 AM then the formula will not change
the days until midnight so even though it really should be 1 day 16 hours
something (PT) something it will still display 2 days until midnight. One
might be able to dodge that using IF functions or MAX/MIN in some ways..


--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon

 

[Art MacNeil]

That's perfect. I can live with the restriction of Datedif displaying 2
days until midnight.

Thank you for your help,

Art.