What Are Date Functions?
Date functions in Excel and Google Sheets let you calculate differences between dates, find the end of a month, add months to a date, count business days, and much more. These functions are essential for project timelines, invoicing, payroll, age calculations, and any spreadsheet that tracks time-based data.
The most commonly used date functions are DATEDIF for date differences, EOMONTH for month-end, EDATE for adding months, WORKDAY and NETWORKDAYS for business day calculations, and TODAY / NOW for current dates and times.
How to Use This Generator
- Go to the ExcelForm tool on the homepage
- Click the Generate tab
- Describe your date calculation in plain English — be specific about start/end dates, what to count, and any holidays to exclude
- Select your environment (Excel, Google Sheets, or Both)
- Click Generate Formula and copy the result into your spreadsheet
Example: Date Formula Generator in Action
"Calculate how many business days between the start date in A2 and end date in B2, excluding holidays in E2:E10."
=NETWORKDAYS(A2,B2,$E$2:$E$10)
"Find the last day of the month for the date in A2."
=EOMONTH(A2,0)
Date Function Reference
=DATEDIF(A2,B2,"Y")=EOMONTH(A2,0)=EDATE(A2,3) adds 3 months.=NETWORKDAYS(A2,B2)=WORKDAY(A2,10)=TODAY()) or current date and time (=NOW()). These update automatically when the spreadsheet recalculates.=WEEKDAY(A2,2) returns 1 for Monday through 7 for Sunday.=YEAR(A2), =MONTH(A2), =DAY(A2)Common Mistakes to Avoid
- Dates stored as text instead of actual date values — use
VALUEorDATEVALUEto convert, or--double-negative trick - Forgetting that Excel dates are serial numbers — if your formula returns a number, the cell just needs Date formatting (Ctrl+1)
- Using
DATEDIFwithout checking unit capitalization — "M" works but "m" can fail in some locales - Not accounting for holidays in
NETWORKDAYS— always include your holiday list as the third argument for accurate counts
DATEDIF — The Hidden Powerhouse
DATEDIF is one of Excel's most useful yet least-documented functions. Microsoft intentionally hides it from the function wizard, but it works in every version of Excel and Google Sheets. It calculates the complete difference between two dates in years, months, or days.
The unit argument controls what gets returned: "Y" for complete years, "M" for complete months, "D" for total days, "YM" for months excluding years, "MD" for days excluding months and years, and "YD" for days excluding years.
Working with Business Days, Holidays, and Time Zones
Calculating business days is one of the most common date tasks in professional spreadsheets. NETWORKDAYS counts weekdays (Monday-Friday) between two dates and optionally excludes holidays. WORKDAY finds a date N business days from a start date.
For custom weekend schedules (like Friday-Saturday in some countries), use NETWORKDAYS.INTL and WORKDAY.INTL which let you specify which days are weekends using a string code.
Frequently Asked Questions
How do I calculate days between two dates in Excel?▾
=B2-A2. For complete months, use DATEDIF: =DATEDIF(A2,B2,"M"). For complete years: =DATEDIF(A2,B2,"Y"). To exclude weekends, use NETWORKDAYS: =NETWORKDAYS(A2,B2).What is the DATEDIF function in Excel?▾
DATEDIF calculates the difference between two dates in years, months, or days. Syntax: =DATEDIF(start_date, end_date, unit). Units: "Y" for years, "M" for months, "D" for days, "YM" for months excluding years, "MD" for days excluding months and years. Note: DATEDIF is not documented by Microsoft but works in all Excel versions.How do I get the last day of the month?▾
EOMONTH: =EOMONTH(A2,0). The 0 means the end of the current month. For next month's end, use 1: =EOMONTH(A2,1). For the previous month's end, use -1. In Google Sheets, EOMONTH works identically.How do I calculate business days between dates?▾
NETWORKDAYS to count business days excluding weekends: =NETWORKDAYS(A2,B2). To also exclude holidays, add a third argument with the holiday range: =NETWORKDAYS(A2,B2,$E$2:$E$10). For custom weekends (e.g., Friday-Saturday), use NETWORKDAYS.INTL.How do I add months to a date in Excel?▾
EDATE: =EDATE(A2,3) adds 3 months. =EDATE(A2,-1) subtracts 1 month. EDATE handles month-end correctly — adding 1 month to January 31 gives February 28 (or 29 in a leap year).Why does my date formula return a number instead of a date?▾
How do I calculate age from date of birth?▾
DATEDIF for accurate age: =DATEDIF(A2,TODAY(),"Y"). For age in years and months: =DATEDIF(A2,TODAY(),"Y")&" years "&DATEDIF(A2,TODAY(),"YM")&" months".