Excel & Sheets Date & Time Formulas
Generate date and time formulas for Excel and Google Sheets. Pick a platform and operation — get a copy-ready formula with explanation.
Select a platform and operation above
How to Use This Tool
Select Excel or Google Sheets from the platform tabs, then choose an operation. The tool generates a practical formula with cell references (like A1, B1) and an explanation of each argument. Most formulas work identically in both platforms — the tool highlights the differences where they exist.
All formulas assume dates are in cells. If you need to hard-code a date, use DATE(2026,3,26) — never type a date string directly into a formula, because date parsing depends on your locale settings.
Quick Reference: Core Date Functions
| Function | Excel | Google Sheets | Returns |
|---|---|---|---|
| Current date | =TODAY() | =TODAY() | Today's date (no time) |
| Current date+time | =NOW() | =NOW() | Current date and time |
| Create a date | =DATE(2026,3,26) | =DATE(2026,3,26) | March 26, 2026 |
| Extract year | =YEAR(A1) | =YEAR(A1) | e.g. 2026 |
| Extract month | =MONTH(A1) | =MONTH(A1) | 1–12 |
| Extract day | =DAY(A1) | =DAY(A1) | 1–31 |
| Day of week | =WEEKDAY(A1) | =WEEKDAY(A1) | 1=Sun ... 7=Sat |
| Week number | =WEEKNUM(A1) | =WEEKNUM(A1) | 1–54 |
| Days between | =B1-A1 | =B1-A1 | Number of days |
| Add months | =EDATE(A1,3) | =EDATE(A1,3) | Date + 3 months |
| End of month | =EOMONTH(A1,0) | =EOMONTH(A1,0) | Last day of A1's month |
| Business days | =NETWORKDAYS(A1,B1) | =NETWORKDAYS(A1,B1) | Working days between |
DATEDIF — The Hidden Function
DATEDIF calculates the difference between two dates in full years, months, or days. It's one of Excel's most useful date functions but is undocumented — it doesn't appear in autocomplete or the function wizard. It was inherited from Lotus 1-2-3 for backwards compatibility and works identically in Google Sheets.
| Unit | Formula | Returns |
|---|---|---|
| "Y" | =DATEDIF(A1,B1,"Y") | Complete years between dates |
| "M" | =DATEDIF(A1,B1,"M") | Complete months between dates |
| "D" | =DATEDIF(A1,B1,"D") | Total days between dates |
| "YM" | =DATEDIF(A1,B1,"YM") | Months remaining after full years |
| "MD" | =DATEDIF(A1,B1,"MD") | Days remaining after full months |
| "YD" | =DATEDIF(A1,B1,"YD") | Days remaining after full years |
Combine units for a human-readable age: =DATEDIF(A1,TODAY(),"Y")&" years, "&DATEDIF(A1,TODAY(),"YM")&" months"
Date Format Codes
Used with TEXT() to format dates as strings, or in cell format codes (Format Cells → Custom).
| Code | Meaning | Example |
|---|---|---|
| yyyy | 4-digit year | 2026 |
| yy | 2-digit year | 26 |
| mmmm | Full month name | March |
| mmm | Abbreviated month | Mar |
| mm | Month number (zero-padded) | 03 |
| m | Month number (no padding) | 3 |
| dddd | Full weekday name | Thursday |
| ddd | Abbreviated weekday | Thu |
| dd | Day number (zero-padded) | 26 |
| d | Day number (no padding) | 26 |
| hh | Hour (zero-padded, 24h or 12h) | 14 |
| mm | Minute (when after hh or before ss) | 30 |
| ss | Second (zero-padded) | 45 |
| AM/PM | 12-hour period | PM |
Common Format Patterns
| Desired output | TEXT formula | Result |
|---|---|---|
| ISO date | =TEXT(A1,"yyyy-mm-dd") | 2026-03-26 |
| US date | =TEXT(A1,"mm/dd/yyyy") | 03/26/2026 |
| EU date | =TEXT(A1,"dd/mm/yyyy") | 26/03/2026 |
| Long date | =TEXT(A1,"mmmm d, yyyy") | March 26, 2026 |
| With weekday | =TEXT(A1,"dddd, mmmm d") | Thursday, March 26 |
| 24-hour time | =TEXT(A1,"hh:mm:ss") | 14:30:45 |
| 12-hour time | =TEXT(A1,"h:mm AM/PM") | 2:30 PM |
| Full timestamp | =TEXT(A1,"yyyy-mm-dd hh:mm") | 2026-03-26 14:30 |
Platform Differences
Most date functions are identical across Excel and Google Sheets. Here are the notable differences.
| Feature | Excel | Google Sheets |
|---|---|---|
| Custom weekends | WORKDAY.INTL, NETWORKDAYS.INTL | WORKDAY.INTL, NETWORKDAYS.INTL |
| Parse text to date | DATEVALUE("3/26/2026") | DATEVALUE("3/26/2026") — more flexible |
| ISO week number | ISOWEEKNUM(A1) | ISOWEEKNUM(A1) — identical |
| Epoch conversion | Manual formula | Manual formula (same approach) |
| Date serial epoch | Jan 1, 1900 = 1 (with 1900 bug) | Dec 30, 1899 = 0 (no bug) |
| LAMBDA functions | Excel 365 / 2021+ | Supported |
| Array formulas | Ctrl+Shift+Enter (legacy) or dynamic | Automatic expansion |
How Excel Stores Dates
Excel stores every date as a serial number — the count of days since January 1, 1900. March 26, 2026 is serial number 46107. Times are stored as decimal fractions of a day: 0.5 = noon, 0.75 = 6 PM. This is why =B1-A1 returns days — you're subtracting two numbers.
If a cell shows a number like 46107 instead of a date, the cell format is "Number" or "General". Select the cell, press Ctrl+1 (or Cmd+1 on Mac), and choose a Date format. The underlying value doesn't change — only the display does.
The 1900 bug: Excel incorrectly treats 1900 as a leap year (February 29, 1900 exists in Excel but never actually happened). This was intentionally preserved from Lotus 1-2-3 for compatibility. Serial numbers from March 1, 1900 onward are off by 1 compared to the actual day count. Google Sheets does not have this bug — its epoch starts at December 30, 1899 to stay compatible with Excel serial numbers for modern dates.
Common Patterns & Recipes
Calculate age from birthdate
List all business days in a month
Conditional formatting for overdue dates
Frequently Asked Questions
How do I get today's date in Excel?
=TODAY() returns the current date and updates automatically. =NOW() returns the current date and time. Both are volatile — they recalculate every time the workbook recalculates. To enter a static date that doesn't change, press Ctrl+; (semicolon).
How do I calculate the number of days between two dates?
Subtract: =B1-A1. For business days (excluding weekends): =NETWORKDAYS(A1,B1). To exclude weekends and holidays: =NETWORKDAYS(A1,B1,holidays_range). For a breakdown: =DATEDIF(A1,B1,"Y") for years, "M" for months, "D" for days.
How do I add months to a date in Excel?
=EDATE(A1,3) adds 3 months. It handles month-end correctly — January 31 + 1 month = February 28. For days, just add a number: =A1+30. For years: =EDATE(A1,12) or =DATE(YEAR(A1)+1,MONTH(A1),DAY(A1)).
Why does Excel show a number instead of a date?
Excel stores dates as serial numbers (days since 1/1/1900). If a cell shows 46107 instead of a date, format it: select the cell → Ctrl+1 → choose Date format. The value is correct — only the display needs changing.
What is DATEDIF and why can't I find it?
DATEDIF is an undocumented function inherited from Lotus 1-2-3. It calculates date differences in specified units (Y, M, D, YM, MD, YD). It doesn't appear in autocomplete or help, but it works in all versions of Excel and Google Sheets. Type it manually: =DATEDIF(start, end, "Y").
What's the difference between Excel and Google Sheets dates?
Most functions are identical. Key differences: Excel has a 1900 date bug (Feb 29, 1900 exists), Sheets doesn't. Sheets auto-expands array formulas; Excel needs dynamic arrays or Ctrl+Shift+Enter. Custom weekend handling uses different function names. For everyday date work, formulas are interchangeable.