Subtracting dates can be easy. However, formatting the answer to be shown as a countdown to an event is a little tricky. But we can create a countdown timer in Excel to do just that:
First, let’s subtract our dates, to get the number of days between now and the event. This is done by using the NOW function, which has time/date capability, or the TODAY function, which only has date capability. We are going to use the NOW function.
Since we are using the NOW function, the date of the event has been formatted in date/time.
Now we need to subtract the dates:
Remember to subtract the later date from the current date, otherwise you will return a negative number.
Once this is done, if you want something other than the number of days between the dates to show, you will need to format the answer differently. To format the answer as hours/minutes/seconds remaining before the event, right-click on the cell to format and select Format Cells.
Under the Number tab, select Time in the Category section, then select 37:30:55.
Another way to get the same outcome is to select Custom in the Category section, under the Number tab, and select [h]:mm:ss;@.
Both setups will give you the following:
Note: Time will automatically tick off in the background, but will update only when the workbook is reopened, or when a different cell is updated/changed.
