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"

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 is off by 31 days.
It seems like no matter what date I put in, it wont return more than 30 or so days.

What's the trick?

"MartyD" nForGmail wrote:
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!!!