 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / March 2006Excel countdown
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. 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.")  SignatureRegards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon > Hello, > [quoted text clipped - 7 lines] > > Art. Excellent, thank you. > This formula counts down from now until Christmas, refresh by pressing F9 > [quoted text clipped - 16 lines] >> >> Art. 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. > This formula counts down from now until Christmas, refresh by pressing F9 > [quoted text clipped - 16 lines] >> >> Art. 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.. SignatureRegards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon > Hello, > [quoted text clipped - 35 lines] >>> >>> Art. That's perfect. I can live with the restriction of Datedif displaying 2 days until midnight. Thank you for your help, Art. > With a future date and time in A1 you can try > [quoted text clipped - 50 lines] >>>> >>>> Art. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.