How to Create a Countdown Timer in Google Sheets
Today we’re going to learn how to create a countdown timer in Sheets using some built-in functions. that will DATEDIFwhich calculates the number of days, months or years between two dates, and NOW() which return today’s date. The syntax for the DATEDIF formula is DATEDIF(start_date, end_date, unit) with the start and end date required to refer to a cell that already contains a date or a function that returns a date, such as NOW(). The unit is an abbreviation for the unit of time you want to calculate, such as e.g. below:
• “Y”: the number of whole years between start_date and end_date.
• “M”: the number of whole months between start_date and end_date.
• “D”: the number of days between start_date and end_date.
• “MD”: the number of days between start_date and end_date after subtracting whole months.
• “YM”: the number of whole months between start_date and end_date after subtracting whole years.
• “JT”: The number of days between start_date and end_date, provided start_date and end_date were within a year of each other.
Source: Google Docs Editors Help
For example, if you want to calculate how many months and days are left until American Thanksgivingwhich falls on November 24 this year, would you First create a cell that references the date of November 24thwhich you will need to refer to later in the DATEDIF Function. Once this cell is created, Proceed to create two more cells, one for the number of months and one for the number of days.
Next we can create our formulas. In the Months cell, enter the formula =DATEDIF(NOW(),A2,"M") With NOW() represents today’s date, A2 referring to the cell where you entered the Thanksgiving date and “M‘ stands for the number of months. However, since there is more than a month but not exactly two months between the two dates, there are days in between that are not accounted for.
To calculate the number of days remaining after this month, in the Days cell, enter the formula =DATEDIF(NOW(),A2,"MD") With “MD‘ which represents the number of days between the two events after the month has been subtracted.
As you can see in this example, there’s a month and 30 days left until Thanksgiving instead of two whole months. That’s because Months and years are only counted if they are equal to or greater than “day”. So if Thanksgiving falls on November 25th instead of the 24th and today is September 25th, it would be counted as 2 months instead of 1 month and changed.
I just want the steps!
1. Create a cell with the date you want to count to
2. Create another cell to enter your countdown formulaand type in
=DATEDIF(NOW(),A2,"M")where “A2” refers to the cell where you entered the date you want to count down to and “M” represents months as the unit you want to count in.3. If you also want to calculate the number of days left after the month is over, Create another cell and use the formula
=DATEDIF(NOW(),A2,"MD").
