Excel Date Formulas: TODAY, DATE, DATEDIF, EDATE, and More

Dates in Excel are stored as numbers (January 1, 1900 = 1, today is somewhere around 46,000). That's why date math works with simple addition and subtraction, and why all the date functions are consistent and predictable once you understand the basics.

TODAY and NOW

Returns today's date. Updates every time the workbook recalculates. Use it for calculating age, days overdue, or anything relative to the current date.

Returns the current date and time. Useful for timestamping but recalculates constantly — use sparingly in large files.

Days between two dates

Since dates are numbers, subtraction just works:

Format the result as a number (not a date) to see the count of days. Or use:

DATEDIF — years, months, or days between dates

DATEDIF is hidden (not in autocomplete) but fully functional. It calculates the difference in years, months, or days:

The third argument controls the unit:

Age calculation example — "3 years, 7 months":

EDATE — add or subtract months

Adds (or subtracts, with a negative number) a number of months to a date. Useful for contract renewals, billing cycles, and subscription dates:

Returns the date exactly one year after the date in A2.

EOMONTH — last day of a month

Returns the last day of the month, offset by the number of months you specify. 0 = current month, 1 = next month, -1 = last month:

Returns the last day of the current month. Common for financial reporting cutoffs.

WORKDAY — skip weekends and holidays

Adds business days to a date, skipping weekends. Add a range of holiday dates as the third argument to skip those too:

Returns the date 5 business days after A2.

Counts the number of working days between two dates.

Protect date calculations from dirty inputs

Excel stores dates as serial numbers, so a date-looking text value may not behave like a date in subtraction, WORKDAY, or NETWORKDAYS. Test imported columns before building deadlines. If the source is text in a known format, convert it deliberately with DATE, DATEVALUE, or a controlled import step rather than changing the display format and assuming the underlying value changed.

Blank dates need an explicit policy. A formula such as =IF(A2="","",NETWORKDAYS(A2,TODAY())) keeps an empty ticket from being treated as an old ticket. For a due-date flag, check both that a date exists and that it is not already complete: =IF(AND(E2<>"",E2<TODAY(),D2<>"Done"),"Overdue","OK").

Remember that DATEDIF can return an unexpected result when the end date is earlier than the start date. Validate the date order first when users can edit both fields, and apply the correct number format to the result.

Sources

• Microsoft Support: DATE function
• Microsoft Support: Date and time functions reference

The Griddy way

Date logic gets complex fast — fiscal year vs. calendar year, quarter boundaries, SLA calculations, anniversary dates. Describe what you need:

"Calculate how many business days each ticket has been open, excluding weekends and the holidays listed in the Holidays tab"

Griddy writes the NETWORKDAYS formula with your holiday range reference included.