A countdown typed into a spreadsheet as a number is dead on arrival - it is wrong tomorrow. A countdown built on the TODAY function is alive: it recalculates every time the file opens, so the number you see is always the number for today. Building one takes a single formula, and this guide starts there, then adds the three upgrades most people actually need: a working-days version with holidays removed, a weeks-and-days version, and a cell that flips itself into a days since count when the date passes. Everything works the same in Excel and Google Sheets.
The one-formula countdown
Put your target date in cell A2 - say December 25, 2026 - and in the cell beside it enter =A2-TODAY(). Spreadsheets store dates as day numbers, so subtracting today from the target gives the count of midnights between them, which is the standard count used across this site. On November 22, 2026 the formula returns 33; open the file the next morning and it returns 32, with no edit from you. TODAY() is volatile - it refreshes on every recalculation - and it carries no time component, so the result is a clean date count that changes only at midnight, never during the day. That stability is the difference between a countdown and a stopwatch, and it is why this formula, not a NOW()-based one, is the right base. For a one-off count with no spreadsheet, the days until calculator does the same subtraction; the sheet earns its place when you track several dates at once or revisit them daily.
Working days: NETWORKDAYS and a holiday list
For deadlines, swap the subtraction for =NETWORKDAYS(TODAY(),A2), which counts Monday to Friday days from today through the target, inclusive. For the Christmas example it returns 25. Then add your holidays: list them in a spare range, say E2:E12, and use =NETWORKDAYS(TODAY(),A2,E2:E12). With Thanksgiving Day in that list, the Christmas count drops to 24 - the honest working figure from our Christmas guide. Two warnings from experience. First, NETWORKDAYS counts today if today is a weekday, while the plain subtraction never counts today; for a Sunday start like ours the two methods agree, but on a weekday start the NETWORKDAYS figure reads one higher than a strict days remaining figure, and you should know which you are quoting. Second, if your weekend is not Saturday and Sunday, use NETWORKDAYS.INTL instead, which takes a weekend code - the method behind our guide on counting weekdays only.

Weeks and days, and a sentence instead of a number
Raw days are exact but hard to say out loud, so build the display version: =INT((A2-TODAY())/7)&" weeks, "&MOD(A2-TODAY(),7)&" days to go" returns text like "4 weeks, 5 days to go" for the Christmas example. The INT takes whole weeks, the MOD keeps the leftover days, and the ampersands glue the sentence together. A sentence cell is worth building for any sheet other people read, because it carries its units with it - nobody has to guess whether 33 means days, and nobody can paste it into the wrong column and lose the meaning. Keep the plain numeric cell too, hidden if you like: sorting, charts and conditional formatting all want numbers, and a traffic-light rule on the numeric cell (red under 7, amber under 30) turns a list of dates into a dashboard that warns you by itself.
The flip: one cell for before and after
The neatest trick in this guide handles what happens when the date passes. Wrap the count: =IF(A2<TODAY(),"Passed "&TODAY()-A2&" days ago",A2-TODAY()&" days to go"). Before the date, the cell counts down. On the morning after, the same cell reads "Passed 1 days ago" - fix the grammar if it bothers you with a nested IF for the singular, or leave it; the count is right. This is the spreadsheet version of the flip the days between calculator performs on the homepage, where a past target becomes a days since count automatically, and it means one tracker row serves an event for its whole life: wedding countdown in spring, days married in autumn, same cell. Our guide on reading a countdown both ways explains why the pair of numbers belongs together.
Two maintenance notes. If a cell shows hashes or a date in January 1900, it is a formatting problem, not a formula problem: a negative count cannot be shown in a date format, so set the number format to plain Number or use the IF version, which returns text. And in Google Sheets, TODAY follows the spreadsheet's time zone setting, found under File, Settings - a shared sheet set to another region can legitimately show a count one different from yours near midnight, which is the time zone effect from our countdown mistakes guide wearing a settings menu.
Scaling up: a tracker for many dates
One countdown is a formula; twenty countdowns are a tracker, and trackers need two more habits. First, keep one row per event with three columns - event name, target date, and the live count - and let every other figure (weeks, working days, the sentence version) derive from those, so there is exactly one date to correct when something moves. Second, sort or filter by the count column, never by the date column alone: the count already accounts for today, so the row that needs you floats to the top as its number shrinks. Add a months view for long spans with =DATEDIF(TODAY(),A2,"m")&" months, "&DATEDIF(TODAY(),A2,"md")&" days", which returns the calendar breakdown - for a target of June 20, 2027 it reads "6 months, 29 days" on November 22, 2026 - alongside the day total. A tracker built this way replaces a wall of sticky notes, and unlike the sticky notes, it notices overnight when something becomes urgent.
Because both programs store dates as serial numbers, a countdown file moves between Excel and Google Sheets without the formulas changing - the same =A2-TODAY() works in both, and a sheet built in one opens in the other with its counts intact. Build it wherever your other lists live; the countdown does not care. What it does care about is the one habit from the start of this guide: the target must be a real date in a cell, and today must always come from TODAY(), never from your keyboard.
Frequently asked questions
What is the basic live countdown formula?
Put the target date in cell A2 and use =A2-TODAY(). TODAY() returns the current date and recalculates every time the sheet opens, so the result is always the current count. With December 25, 2026 in A2, the formula returns 33 on November 22, 2026 and 32 the next morning, untouched.
How do I count working days only in the countdown?
Use =NETWORKDAYS(TODAY(),A2), which counts Monday to Friday between today and the target, including both endpoints. Add a holiday list as the third argument - =NETWORKDAYS(TODAY(),A2,E2:E12) - to remove those too. For the Christmas example that gives 25 weekdays, or 24 with Thanksgiving in the holiday list.
Why does my countdown cell show hashes (#####) or a date in 1900?
Hashes mean the result is negative and the cell is formatted as a date - once the target passes, A2-TODAY() goes negative and a date format cannot show it. Format the cell as a plain number. A result displayed as a date in 1900 is the same issue in reverse: the count is being formatted as a date instead of a number.
How do I show days since once the date has passed?
Wrap the count in an IF: =IF(A2<TODAY(),"Passed "&TODAY()-A2&" days ago",A2-TODAY()&" days to go"). Before the date it counts down; after it, the same cell counts up in days since wording, so one cell serves the event for its whole life.
Do the same formulas work in Google Sheets?
Yes. TODAY, NETWORKDAYS and the subtraction method work identically in Google Sheets, and Sheets recalculates TODAY whenever the sheet is opened or edited. One difference: Sheets updates the date by the spreadsheet's own time zone setting, so check that setting if a shared sheet shows a count one day off from yours.
