How long is left on each deadline, worked out fresh every morning without anyone touching the sheet.
The formula
=DATEDIF(TODAY(),B2,"d") in column C
What each part does
TODAY()
Today, read fresh every time the sheet opens.
B2
The date being counted to.
"d"
Counted in days. Use "m" for whole months, "y" for years.
How it works, and what to watch for
TODAY carries no arguments and takes the date from the machine, so the column moves on by itself each day the file is opened.
DATEDIF counts forwards only. A date that has already passed returns #NUM! rather than a negative number, which surprises people the first time it happens.
A plain subtraction, =B2-TODAY(), gives the same figure and does go negative once the date is past, which is often more useful for a list of deadlines.
Questions
Why do I get #NUM!?
The date has passed. Either use =B2-TODAY() instead, or wrap the DATEDIF in IFERROR to show a note of your own.
How do I count working days only?
NETWORKDAYS(TODAY(),B2) skips weekends, and takes a list of holidays as a third argument.
Why is the answer a date instead of a number?
The cell has inherited a date format from the column beside it. Set it back to General or Number.
Set by your computer's language, not by the file. A frequent cause of a correct-looking formula being rejected.
Excel version
XLOOKUP, IFS, TEXTBEFORE and TEXTJOIN need Microsoft 365 or 2021; older versions return #NAME? instead. Choosing the older one switches every walkthrough to a formula that works there.
