How long until launch? Subtract NOW() from a target date-time and you get the gap as a fraction of days — then split it into days, hours, and minutes for a live countdown.
B1−NOW() is the gap in days; INT takes the whole days and TEXT formats the leftover as hours and minutes.
The example
Counting down to a target moment.
| A | B | |
|---|---|---|
| 1 | Target | 7/1/2026 9:00 AM |
| 2 | Now | 6/17/2026 2:30 PM |
| 3 | Remaining | 13d 18h 30m |
The formula
Days, hours, and minutes remaining:
How it works
It’s all built on date arithmetic:
- Dates and times are numbers — whole part = days, fractional part = time of day. So
B1−NOW()is the gap measured in days. INT(…)grabs the whole days.TEXT(gap, "h\h m\m")formats the leftover fraction as hours and minutes — the backslashes make the letters literal.- Because it uses
NOW(), the countdown updates whenever the sheet recalculates (press F9 or on any edit).
Whole days only? If you don’t need the clock, =INT(B1) - TODAY() gives a clean “days remaining” count that won’t shift through the day.
Try it: interactive demo
Pick a target date and time.
Variations
Whole days only
Stable day count, ignores time:
Total hours remaining
Multiply the day-gap by 24:
Past-due check
Flag if the target has passed:
Pitfalls & errors
NOW() is volatile. It recalculates on every change and on F9, so the countdown shifts constantly — expected for a live timer, but it makes the workbook recalc more often.
Negative when past. Once the target passes, the gap goes negative and TEXT formatting looks wrong. Wrap with an IF to show “Overdue”.
Escape the letters. In the TEXT format, h and m are codes; use \h and \m (or quotes) to show literal “h” and “m” labels.
Practice workbook
Frequently asked questions
How do I make a countdown timer in Excel?
How do I count just the days until a date?
How do I flag when the target has passed?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class