Date arithmetic
Working with dates and times — calculate age, days/months/years between, business days, fiscal quarters, overdue flags. Every date recipe and DATEDIF gotcha.
20 pages · 2 reference, 18 how-to
Reference
How-to guides
How to calculate age from a date of birth
Turn a birth date into an age in years with DATEDIF and TODAY. Works the same in Excel and Google Sheets.
How to calculate days between two dates
Subtract one date from another to get the number of days, or count working days with NETWORKDAYS. Works in both apps.
How to add months to a date
Shift a date forward or back by a number of months with EDATE — it handles month lengths correctly. Works in both apps.
How to flag overdue dates
Mark rows whose due date has passed by comparing it to TODAY() with IF. Works the same in Excel and Google Sheets.
How to calculate age from a birthday
Use DATEDIF with TODAY() to calculate a person's exact age in years from a birth date cell in Excel and Google Sheets.
How to calculate the days between two dates
Subtract one date from another, or use DAYS(), to find the number of calendar days between two dates in Excel and Google Sheets.
How to calculate the months between two dates
Use DATEDIF with "M" or the MONTHS formula to calculate the complete months between two dates in Excel and Google Sheets.
How to calculate the years between two dates
Use DATEDIF with "Y" or YEARFRAC to count the complete years or fractional years between two dates in Excel and Google Sheets.
How to add days to a date
Add a number directly to a date cell to move forward by that many calendar days in Excel and Google Sheets.
How to add months to a date
Use EDATE to add a specific number of months to a date in Excel and Google Sheets, correctly handling month-end dates.
How to subtract one date from another
Subtract two date cells to find the number of days between them, or use DATEDIF for months and years, in Excel and Google Sheets.
How to count business days between two dates
Use NETWORKDAYS to count working days (Mon-Fri) between two dates, with optional holidays, in Excel and Google Sheets.
How to add business days to a date
Use WORKDAY to calculate a future date after adding a specific number of business days, skipping weekends and optional holidays.
How to highlight overdue dates
Use Conditional Formatting with a formula like =A2<TODAY() to automatically highlight past-due dates red in Excel and Google Sheets.
How to get the fiscal year from a date
Calculate the fiscal year from a date by adjusting the month offset relative to your fiscal year start using IF or YEAR and MONTH.
How to calculate how many days something is overdue
Find how many days past the due date a task or invoice is using TODAY() minus the due date, clamped to zero for items not yet due.
DATEDIF vs YEARFRAC: measuring durations in Excel and Sheets
DATEDIF returns whole completed years/months/days; YEARFRAC returns a fractional year. Here is when to use each in Excel and Sheets.
How to sum values by fiscal quarter
Sum values by fiscal quarter in Excel or Google Sheets with SUMPRODUCT and MOD — handle any fiscal year start month without helper columns or pivot tables.