countdown timer

[ss]

I am using this formula for a countdown timer which gives me the days
left and part day as a % ....=E9-NOW()
Is there a way to give the display in days and hours?

thanks

 

[GS]

I am using this formula for a countdown timer which gives me the days

left and part day as a % ....=E9-NOW()
Is there a way to give the display in days and hours?

thanks

Check out the TEXT() function...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[joeu2004]

I am using this formula for a countdown timer which gives
me the days left and part day as a % ....=E9-NOW()
Is there a way to give the display in days and hours?

How does E9-NOW() give you time remaining "as a %"?

I know you can choose the Percentage format. But it is not meaningful as
such, IMHO.

Anyway, arguably:

=INT(E9-NOW()) & " days, " & HOUR(E9-NOW()) & " hours"

Issues:

1. It is not a good idea to call NOW() twice in the same formula. There is
a chance that they are inconsistent, namely when the formula is evaluated
within 15.625 milliseconds of the next hour. It would be better to put
=NOW() into a single cell, and reference that cell in any formula where you
need its value.

2. It might be tempting to use =TEXT(E9-NOW(),"d:hh"). But that works only
by coincidence when the number of remaining days is 31 or less. The
specifier "d" really formats the day of the month, not number of days. So,
for example, if E9-NOW() is 32 days, "d" returns 1 because 32 is interpreted
as Feb 1, 1900.

3. Both HOUR() and the format specifier "hh" return the hour of the day.
They do not round minutes. If that is acceptable, fine. Otherwise, note
that ROUND((E9-NOW())*24,0) returns the rounded number of hours.

 

[ss]

How does E9-NOW() give you time remaining "as a %"?

I know you can choose the Percentage format. But it is not meaningful as
such, IMHO.

Anyway, arguably:

=INT(E9-NOW()) & " days, " & HOUR(E9-NOW()) & " hours"

Issues:

1. It is not a good idea to call NOW() twice in the same formula. There
is a chance that they are inconsistent, namely when the formula is
evaluated within 15.625 milliseconds of the next hour. It would be
better to put =NOW() into a single cell, and reference that cell in any
formula where you need its value.

2. It might be tempting to use =TEXT(E9-NOW(),"d:hh"). But that works
only by coincidence when the number of remaining days is 31 or less. The
specifier "d" really formats the day of the month, not number of days.
So, for example, if E9-NOW() is 32 days, "d" returns 1 because 32 is
interpreted as Feb 1, 1900.

3. Both HOUR() and the format specifier "hh" return the hour of the day.
They do not round minutes. If that is acceptable, fine. Otherwise, note
that ROUND((E9-NOW())*24,0) returns the rounded number of hours.

joeu2004 Apologies regarding my terminology.
The result does not show the % symbol but shows this format for dd/hh
51.30 which I assume is a % of the day (.30).
I will digest your comments and have anothe attempt.

 

[joeu2004]

joeu2004 Apologies regarding my terminology.
The result does not show the % symbol but shows this format for dd/hh
51.30 which I assume is a % of the day (.30).
I will digest your comments and have anothe attempt.

 

[joeu2004]

[....]
Apologies regarding my terminology.
The result does not show the % symbol but shows this format
for dd/hh 51.30 which I assume is a % of the day (.30).

I wonder if you mean that when you format =E9=NOW() as either General or
Number, it displays 51.30.

In that case, the decimal fraction (0.30) is a indeed a __fraction__ of a
day, which we could interpret as (about) 30% of a day.

To explain further.... Excel time is stored as a decimal fraction of a day,
namely h/24 + m/1440 + s.sss/86400.

(Excel formats seconds to only 3 decimal places, but the actual value might
have even greater precision.)

So, 0.30 represents about 24*.30 = 7.2 hours. Perhaps even more, since 0.30
is rounded for appearance purposes. For example, 7.25 hours might be
displayed as 0.30, but it is actually about 0.302083333333333.

Returning to my previous point about the "d" format specifier.... The
Custom format d:hh would display 51.30 as 20:07 because 51 represents Feb
20, 1900. Presumably, that is not what you would want to see.

INT(51.30) extracts the integer part, namely 51. HOUR(51.30) uses the
fractional part (0.30) to determine hours of the day (7). There are other
ways to extract and represent hours of the day.

An alternative to my previous wordy representation of days and hours might
be: =INT(E9-NOW()) & TEXT(E9-NOW(),":hh"). That would result in "51:07"
for your example.

However, that might be confusing because most people would read that as 51
hours 7 minutes.