Countdown formula

Discuss the spreadsheet application

Countdown formula

Postby Spiceskull » Tue Mar 11, 2008 11:39 am

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.
Win XP Pro
OOo 2.0.2
Spiceskull
 
Posts: 12
Joined: Tue Dec 11, 2007 3:28 pm

Re: Countdown formula

Postby Villeroy » Tue Mar 11, 2008 12:15 pm

=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. 
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 12.04, Apache OpenOffice 4.x
User avatar
Villeroy
Volunteer
 
Posts: 17274
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Countdown formula

Postby James » Tue Mar 11, 2008 12:53 pm

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!) 
James
www.8daysaweek.co.uk - A User-Focused OOo site.
Windows Easy Installation CDs & OOo on USB Keys, OOo for Mac OS X + Ubuntu CDs

Please read: Survival Guide for the forum
OOo 3.1.1 on Ubuntu 9.x + Windows XPP, Mac OS X
User avatar
James
 
Posts: 264
Joined: Sun Oct 07, 2007 7:23 am
Location: UK

Re: Countdown formula

Postby Villeroy » Tue Mar 11, 2008 1:12 pm

James wrote: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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 12.04, Apache OpenOffice 4.x
User avatar
Villeroy
Volunteer
 
Posts: 17274
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Countdown formula

Postby James » Tue Mar 11, 2008 1:35 pm

Of course :oops:
James wrote: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 
James
www.8daysaweek.co.uk - A User-Focused OOo site.
Windows Easy Installation CDs & OOo on USB Keys, OOo for Mac OS X + Ubuntu CDs

Please read: Survival Guide for the forum
OOo 3.1.1 on Ubuntu 9.x + Windows XPP, Mac OS X
User avatar
James
 
Posts: 264
Joined: Sun Oct 07, 2007 7:23 am
Location: UK

Re: Countdown formula

Postby Villeroy » Tue Mar 11, 2008 2:30 pm

James wrote:Of course :oops:
James wrote: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").
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 12.04, Apache OpenOffice 4.x
User avatar
Villeroy
Volunteer
 
Posts: 17274
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Countdown formula

Postby Villeroy » Tue Mar 11, 2008 2:48 pm

@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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 12.04, Apache OpenOffice 4.x
User avatar
Villeroy
Volunteer
 
Posts: 17274
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Countdown formula

Postby James » Tue Mar 11, 2008 2:57 pm

Villeroy wrote:"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.

Villeroy wrote: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.

Villeroy wrote: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 :idea: 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 :)
Attachments
Countdown.ods
(9.26 KiB) Downloaded 125 times
James
www.8daysaweek.co.uk - A User-Focused OOo site.
Windows Easy Installation CDs & OOo on USB Keys, OOo for Mac OS X + Ubuntu CDs

Please read: Survival Guide for the forum
OOo 3.1.1 on Ubuntu 9.x + Windows XPP, Mac OS X
User avatar
James
 
Posts: 264
Joined: Sun Oct 07, 2007 7:23 am
Location: UK

Re: Countdown formula

Postby Villeroy » Tue Mar 11, 2008 3:03 pm

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" 
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 12.04, Apache OpenOffice 4.x
User avatar
Villeroy
Volunteer
 
Posts: 17274
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Calc

Who is online

Users browsing this forum: Villeroy and 21 guests