Countdown formula

[Spiceskull]

Hi,

I'm scratching my head here, trying to work out what to do - I need to set a timer to countdown from a predetermined time to "now".

The details: there are DD:HH:MM:SS until an event occurs, and I want to be able to see at a glance how much time is left. I also want to be able to format the "time left" to change so that it "looks urgent" although I can do this by a simple condition of IF(condition)THEN(format)

My problem is how to go about setting the timer...can anyone help? To further complicate things I have a list of events, all having different lengths to completion, so this needs to be formula based.

Thanks,
Adam.

[Villeroy]

=NOW()-A1 returns the count of days between now and the time in A1. A1 should be a date+time such as "1999-12-31 23:59" (or any other formatting). If the year and month are static and A1 has the number of day: =NOW()-DATE(1999;12;A1).
Notice that no number formatting will ever change actual cell values. Number formatting shows the same numbers in different ways.

Edit: For got the most important hint: You get your actual cell values by removing all number formatting and/or through function N(). A format string like "DD:HH:MM:SS" does not tell anything since behind that value there might be a year and month as well.

[James]

This might help, I only took a few seconds to do it though, so I suggest you test thoroughly for absent-mindedness

Edit: Deleted file which used HOURS(B2;A2) to calculate hours, but obviously as it doesn't take into account the date, fails if the target hour is less than the hour NOW() (returns a minus!)

[Villeroy]

This might help, I only took a few seconds to do it though, so I suggest you test thoroughly for absent-mindedness

Sorry, this does not work. Just change the hour of B2 to be smaller than the current hour.
Function DAYS() is fine here, but may return strange #MACRO errors in other situations. In the end it is completely obsolete since DAYS(x;y) always returns exactly the same result as x-y.
Simply calculate the difference =B2-A2, round as you like and finally apply a nice number format.

[James]

Of course

absent-mindedness

What format will show Days, then HH:MM:SS?

That's why I split the cells out when playing with it, shouldn't have bothered with HOURS(), was just showing the calculations that can be done on dates/times.

Edit: Deleted attachment, see new attachment below

[Villeroy]

Of course

absent-mindedness

What format will show Days, then HH:MM:SS?

"DD HH:MM:SS" does not work because the day number "DD" is not the same as the number of days. I believed "# HH:MM:SS" could do that, since "#" shows the integer part of your calculation result and "HH:MM:SS" is a way to show the part behind the dot (time as fraction of a day).
Unfortunately, Calc does not accept "# HH:MM:SS" as valid umber format string.
I would use the calculation result in whatever number format for further calculation and if I desparately need to display "# HH:MM:SS" I would use a conversion to text: =TEXT(A1;"#")&" "&TEXT(A1;"HH:MM:SS").

[Villeroy]

@Spiceskull
"DD HH:MM:SS" does not work because the day number "DD" is not the same as the number of days.
If this is confusing, do the following learning-by-doing experiment.
Put this forula into a cell on top of some free space:
=ROW(A1)/4-2
and drag down some hundred rows to get a sequence -1.75, -1.5, -1.25, ... , 0 , 0.25, 0.5, ...
Then copy to the right some columns to get copies of the same sequence.
Now keep one column of plain numbers and format the other columns differently. Lets start with ISO date/time "YYYY-MM-DD HH:MM", another column as date only, time only, one "DD" and one "#".
"DD" interpretes a number as date and shows the day number (e.g. the "31" out of "1999-12-31").
"#" shows the rounded integer part of the number. When a number is interpreted as date, the integer part of a number is the count of days.

[James]

"DD HH:MM:SS" does not work because the day number "DD" is not the same as the number of days.

Yes, it's a calculated date element of the result.

I believed "# HH:MM:SS" could do that, since "#" shows the integer part of your calculation result and "HH:MM:SS" is a way to show the part behind the dot (time as fraction of a day).
Unfortunately, Calc does not accept "# HH:MM:SS" as valid umber format string.

Yes, I'd tried that.

I would use the calculation result in whatever number format for further calculation and if I desparately need to display "# HH:MM:SS" I would use a conversion to text: =TEXT(A1;"#")&" "&TEXT(A1;"HH:MM:SS").

Ah brain kicks into gear! Doesn't work exactly like that though, as the days part is rounded up, e.g. 2.9 days (2 days, so many hours) shows as: 3 HH:MM:SS.

So, how about =TEXT(ROUNDDOWN(B2-A2;0);"#")&" days, "&TEXT(B2-A2;"HH:MM:SS")

or even =TEXT(ROUNDDOWN(B2-A2;0);"#")&" days, "&TEXT(B2-A2;"H")&" hours, "&MINUTE(B2-A2)&" mins, "&TEXT(B2-A2;"S")&" secs" - couldn't get TEXT(B2-A2;"mm") to work, refers to month.

Interesting excercise

[Villeroy]

Yepp, you found my mistake. Should be ROUNDDOWN(A1;0) or INT(A1) in order to cut off the number's day portion.

Edit: Take "A1" as "any number"