Detailed Countdown

M

MartyD

I'm trying to create a countdown to an event.
In A2 I have the date and time as 12/1/2012 2:00:00 PM
In A1 I have: =A2-NOW() and it is formatted as: d "Days" h "Hours" m
"Minutes" ss "Seconds"

The trouble is it doesn't return the correct number of days.

Right now it returns: "20 Days 14 Hours 05 Minutes 07 Seconds" which i
off by 31 days.
It seems like no matter what date I put in, it wont return more than 3
or so days.

What's the trick
 
J

joeu2004

MartyD said:
I'm trying to create a countdown to an event.
In A2 I have the date and time as 12/1/2012 2:00:00 PM
In A1 I have: =A2-NOW() and it is formatted as:
d "Days" h "Hours" mm "Minutes" ss "Seconds" [....]
It seems like no matter what date I put in, it wont
return more than 30 or so days.

You cannot format elapsed number of days using the d specifier.

The d specifier formats the day of the month, not the number of days.

Try:

=INT(A1) & TEXT(A1,""" days"" h ""hours"" m ""minutes"" s ""seconds""")

Note the use of two double-quotes within the quoted string.
 
M

MartyD

'joeu2004[_2_ said:
;1606270']"MartyD
I'm trying to create a countdown to an event.
In A2 I have the date and time as 12/1/2012 2:00:00 PM
In A1 I have: =A2-NOW() and it is formatted as:
d "Days" h "Hours" mm "Minutes" ss "Seconds"- [....]-
It seems like no matter what date I put in, it wont
return more than 30 or so days.-

You cannot format elapsed number of days using the d specifier.

The d specifier formats the day of the month, not the number of days.

Try:

=INT(A1) & TEXT(A1,""" days"" h ""hours"" m ""minutes"" s ""seconds""")

Note the use of two double-quotes within the quoted string.

Works GREAT!
Thank You!!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top