LXLogicExcel
🔥
0
⭐
0

Excel Date Functions: TODAY, NOW, DATE, DATEDIF, DAYS, EOMONTH and More

7 min read•1,250 words•Practice this →

Ready to practice?

Apply what you just learned with interactive exercises.

Start Lesson 43 →

Excel stores dates as serial numbers — January 1, 1900 is 1, January 2, 1900 is 2, and so on. This means you can do math on dates directly: subtract two dates to get the number of days between them. The date functions covered here build on this foundation to let you calculate ages, project deadlines, find end-of-month dates, count working days, and more.

TODAY and NOW

These two functions return the current date and time without any arguments.

TODAY()

=TODAY() returns today's date as a date serial number, formatted as a date. It recalculates every time the workbook opens or recalculates. Examples:
  • =TODAY() returns today's date (e.g., 4/16/2026)
  • =TODAY() - A1 returns the number of days between today and the date in A1
  • =TODAY() + 30 returns the date 30 days from today
Use case: Build formulas that stay current without manual updating. =IF(A1 < TODAY(), "Overdue", "On track") automatically flags past-due items.

NOW()

=NOW() returns the current date and time. The time portion updates whenever the sheet recalculates. Example:
  • =NOW() returns something like 4/16/2026 14:35
  • =NOW() - A1 returns the number of days (and fractional days) since A1
Format the cell as "h:mm AM/PM" to display just the time, or "yyyy-mm-dd hh:mm" for a full timestamp.

DATE — Build a Date from Parts

DATE creates a date serial number from year, month, and day values. Syntax: =DATE(year, month, day) Examples:
  • =DATE(2026, 6, 15) returns the date June 15, 2026
  • =DATE(YEAR(A1), MONTH(A1) + 1, 1) returns the first day of the next month
  • =DATE(2026, 13, 1) returns February 1, 2027 — Excel handles month overflow
Use case: Reconstruct dates when year, month, and day are in separate columns. =DATE(C1, B1, A1) joins day in A1, month in B1, year in C1 into a proper date.

DATE also handles out-of-range values gracefully. =DATE(2026, 1, 0) returns December 31, 2025 (day 0 = last day of previous month), which is useful for end-of-month calculations.

YEAR, MONTH, DAY — Extract Date Parts

These three functions decompose a date into its components.

Syntax:
  • =YEAR(date) — returns the four-digit year
  • =MONTH(date) — returns the month number (1–12)
  • =DAY(date) — returns the day of the month (1–31)
Examples:
  • =YEAR(TODAY()) returns 2026
  • =MONTH("4/16/2026") returns 4
  • =DAY(A1) returns the day number from the date in A1
Practical use: Group data by year or month. =YEAR(A1)&"-"&TEXT(MONTH(A1),"00") creates a "2026-04" style year-month label for pivot table grouping.

DATEDIF — Calculate Periods Between Dates

DATEDIF calculates the difference between two dates in years, months, or days. It is a legacy function — not shown in autocomplete — but it still works in all Excel versions. Syntax: =DATEDIF(startdate, enddate, unit) Unit options:
UnitReturns
"Y"Complete years
"M"Complete months
"D"Total days
"YM"Months excluding complete years
"YD"Days excluding complete years
"MD"Days excluding complete months
Examples:
  • =DATEDIF(A1, TODAY(), "Y") returns a person's age in complete years
  • =DATEDIF(A1, B1, "M") returns the number of complete months between A1 and B1
  • =DATEDIF(A1, B1, "D") returns total days (same as =B1 - A1)
Age formula (years and months): 
=DATEDIF(A1, TODAY(), "Y") & " years, " & DATEDIF(A1, TODAY(), "YM") & " months"

Returns something like "32 years, 4 months".

Caution: startdate must be earlier than enddate or DATEDIF returns a #NUM! error. The "MD" unit has known bugs with certain date combinations — use =DAYS(B1,A1) - DATEDIF(A1,B1,"M")*30 as a workaround if precise day counts matter.

DAYS — Simple Day Count

DAYS returns the number of days between two dates. It is simpler than DATEDIF when you just need a day count. Syntax: =DAYS(enddate, startdate) Example:
  • =DAYS("12/31/2026", "1/1/2026") returns 364
  • =DAYS(B1, A1) returns the days from A1 to B1
Note the argument order: end date first, then start date. This returns a positive number when end is after start.

For the same result, you can simply subtract: =B1 - A1 when both cells are formatted as dates. DAYS is explicit about intent and handles text date strings directly.

EOMONTH — End of Month

EOMONTH returns the date of the last day of a month, a specified number of months before or after a start date. Syntax: =EOMONTH(start_date, months)
  • months = 0 returns the last day of the current month
  • months = 1 returns the last day of the next month
  • months = -1 returns the last day of the previous month
Examples:
  • =EOMONTH(TODAY(), 0) returns the last day of the current month
  • =EOMONTH("3/15/2026", 1) returns April 30, 2026
  • =EOMONTH("2/1/2028", 0) returns February 29, 2028 (handles leap years)
Use case: Financial reporting periods. =EOMONTH(A1, 0) + 1 gives the first day of the next month (last day of month + 1 = first of next).

EDATE — Add Months to a Date

EDATE returns the date that is a specified number of months before or after a start date, landing on the same day of the month. Syntax: =EDATE(start_date, months) Examples:
  • =EDATE("1/31/2026", 1) returns February 28, 2026 (handles month-end correctly)
  • =EDATE(TODAY(), 6) returns the date 6 months from today
  • =EDATE(A1, -3) returns the date 3 months before A1
EDATE vs. adding 30 days: =A1 + 30 always adds exactly 30 days. =EDATE(A1, 1) adds one calendar month — March 15 + 1 month = April 15, regardless of how many days March has. Use EDATE for subscription expiry, contract end dates, and anything where "one month later" matters.

WEEKDAY — Day of the Week

WEEKDAY returns a number representing the day of the week for a given date. Syntax: =WEEKDAY(date, [return_type]) Return type options:
  • 1 (default): 1=Sunday through 7=Saturday
  • 2: 1=Monday through 7=Sunday
  • 3: 0=Monday through 6=Sunday
Examples:
  • =WEEKDAY(TODAY(), 2) returns 3 if today is Wednesday (1=Mon, 2=Tue, 3=Wed...)
  • =WEEKDAY("4/16/2026", 1) returns 5 (April 16, 2026 is a Thursday; type 1: 5=Thursday)
Use case: Identify weekends. =IF(WEEKDAY(A1, 2) > 5, "Weekend", "Weekday") flags Saturdays and Sundays (days 6 and 7 in type 2).

WORKDAY — Add Business Days

WORKDAY returns the date that is a specified number of working days (Monday–Friday) before or after a start date, optionally skipping holidays. Syntax: =WORKDAY(start_date, days, [holidays]) Examples:
  • =WORKDAY(TODAY(), 10) returns the date 10 business days from today
  • =WORKDAY("4/16/2026", 5) returns April 23, 2026 (5 business days later, skipping the weekend)
  • =WORKDAY(A1, 30, HolidayList) returns 30 business days after A1, skipping dates in HolidayList
Related function: WORKDAY.INTL lets you specify which days are the weekend (for non-Western work weeks). Reverse calculation: =NETWORKDAYS(start, end) counts the number of working days between two dates, the opposite of WORKDAY.

TEXT — Format Dates as Text

TEXT converts a date to a formatted text string. Use it when you need to embed a date inside a text string. Syntax: =TEXT(value, format_text) Date format codes:
FormatExample output
"D/M/YYYY"16/4/2026
"MMMM D, YYYY"April 16, 2026
"MMM YYYY"Apr 2026
"YYYY-MM-DD"2026-04-16
"DDD"Thu
"DDDD"Thursday
Examples:
  • =TEXT(TODAY(), "MMMM D, YYYY") returns "April 16, 2026"
  • ="Report for " & TEXT(TODAY(), "MMM YYYY") returns "Report for Apr 2026"
  • =TEXT(A1, "YYYY-MM-DD") formats a date cell in ISO format for data exports

Frequently Asked Questions

Why does Excel show a number instead of a date?

Excel stores dates as numbers. If a cell displays a number like 46123 instead of a date, the cell is formatted as General or Number. Select the cell, press Ctrl+1 to open Format Cells, and choose Date from the Number tab. Excel will display the date corresponding to that serial number.

What is the difference between DAYS and DATEDIF with "D"? =DAYS(B1, A1) and =DATEDIF(A1, B1, "D") both return the number of days between two dates, but with reversed argument order. DAYS puts end date first; DATEDIF puts start date first. The results are identical for the "D" unit. DATEDIF is more useful when you also need "Y" (years) or "YM" (remaining months). How do I calculate someone's age in Excel? =DATEDIF(birthdate, TODAY(), "Y") returns the number of complete years. For a more complete output: =DATEDIF(A1, TODAY(), "Y") & " years". Always use DATEDIF for age rather than =(TODAY()-A1)/365.25 — the division method gives slightly imprecise results because it does not account for full birthday completion. Why does WORKDAY skip holidays I provided?

The holidays argument must be a range of actual date values, not text strings. Make sure the cells in your holiday list are formatted as dates (not text) and that the dates are within the range being calculated. Also confirm there are no blank cells in the holiday range that might be interpreted as 0 (which Excel treats as January 0, 1900).

How do I get the first day of the current month? =DATE(YEAR(TODAY()), MONTH(TODAY()), 1) returns the first day of the current month. Or more concisely: =EOMONTH(TODAY(), -1) + 1 (last day of the previous month + 1 = first day of this month).
Practice Excel Date Functions: TODAY, NOW, DATE, DATEDIF, DAYS, EOMONTH and More →