Making a countdown timer in google tables
If you have nothing to do while waiting for a new project, and you only have Google tables at hand, you can make a countdown timer, for example, to find out how much free time you have left.
To make a timer, it’s enough to use a couple of functions:
ТДАТА (NOW) - displays the current date and time in the “date” format.
DATEDIF - Counts the number of days, months, and years remaining between two dates. By default, the current day is taken into account.
DAYS - Counts the number of days between two dates.
HOUR - Returns the hour component at a given time, and can also calculate the difference in hours between two dates.
MINUTE - works similarly to the HOUR function, only for minutes.
Suppose we want to find out how many days are left until the birthday of the habr - May 26 according to Wikipedia.
In order for the timer to be constantly updated and display relevant information, it is necessary to change the calculation settings in the table.
In the open table, select the "File" menu item, go to "Table Settings" and on the "Calculations" tab, select "Calculations Updates" - "When Changes Every Minute".
In the example, I made a timer of two types:
to display the remaining days, hours and minutes,
and also
to display months, days, hours and minutes.
The only difference is that in order to calculate the number of remaining whole months and days it is necessary to use the RANDATE function, while the DAYS function is sufficient to display only the number of days.
There is also a SECOND function that allows you to display seconds, but due to the fact that the time range of the calculation is limited to reading “once per minute” - the seconds timer simply will not have time to update.
The following cells are used in the example:
A2 - the end date, from which we count
B2 - the current date, you can also use the NOW function instead.
Months
Number of days excluding whole months:
Number of days in total
Clock
Minutes
In the simplest embodiment, we display the timer values in separate cells:
Using the CONCATENATE function, you can display all the data in one cell.
For this example, the formula has the form
or to count only days
You can also optionally install add-ons in the table that will send notifications under certain conditions, for example, when a certain number of remaining days is reached. But they only work if the table is open in a window.
An example file is located at the link .
To make a timer, it’s enough to use a couple of functions:
ТДАТА (NOW) - displays the current date and time in the “date” format.
DATEDIF - Counts the number of days, months, and years remaining between two dates. By default, the current day is taken into account.
DAYS - Counts the number of days between two dates.
HOUR - Returns the hour component at a given time, and can also calculate the difference in hours between two dates.
MINUTE - works similarly to the HOUR function, only for minutes.
Suppose we want to find out how many days are left until the birthday of the habr - May 26 according to Wikipedia.
Initial settings
In order for the timer to be constantly updated and display relevant information, it is necessary to change the calculation settings in the table.
In the open table, select the "File" menu item, go to "Table Settings" and on the "Calculations" tab, select "Calculations Updates" - "When Changes Every Minute".
Timer Formulas
In the example, I made a timer of two types:
to display the remaining days, hours and minutes,
and also
to display months, days, hours and minutes.
The only difference is that in order to calculate the number of remaining whole months and days it is necessary to use the RANDATE function, while the DAYS function is sufficient to display only the number of days.
There is also a SECOND function that allows you to display seconds, but due to the fact that the time range of the calculation is limited to reading “once per minute” - the seconds timer simply will not have time to update.
We write formulas for counting time
The following cells are used in the example:
A2 - the end date, from which we count
B2 - the current date, you can also use the NOW function instead.
Months
Number of days excluding whole months:
Number of days in total
Clock
Minutes
In the simplest embodiment, we display the timer values in separate cells:
Using the CONCATENATE function, you can display all the data in one cell.
For this example, the formula has the form
or to count only days
You can also optionally install add-ons in the table that will send notifications under certain conditions, for example, when a certain number of remaining days is reached. But they only work if the table is open in a window.
An example file is located at the link .