Excel date functions: TODAY, DATE, DATEDIF, WEEKDAY and the working set

Excel’s everyday date work comes down to a small working set of formulas: =TODAY() puts the current date in a cell, =DATE(2026,8,15) builds a date from a year, a month and a day, =DATEDIF(A2,B2,"D") counts the days between two dates, and =TEXT(A2,"dddd") turns a date into its weekday name. Around those four sit DAY, MONTH and YEAR for pulling a date apart, WEEKDAY for numbering the day of the week, EOMONTH for month-end deadlines, and NETWORKDAYS for working days.

All of them trade in the same raw material: an Excel date is a serial number, a count of days in which January 1, 1900 is stored as 1, and the cell’s format only decides how that number is displayed. That mechanic, and everything about changing how a date looks, is covered in Excel date formats; this page is about the functions that read and produce the numbers.

How do I put today’s date in a cell?

TODAY takes no arguments and returns the serial number of the current date. If the cell’s format was General, Excel switches it to Date, so what you see is a date, not the raw count. The point of the function is that it moves: Microsoft describes it as the way to show the current date on a worksheet regardless of when you open the workbook. Open the file next Tuesday and the cell says next Tuesday.

=NOW() is the same idea including the time: it returns the current date and time in one serial number, with the time stored in the decimal part (0.5 is 12:00 noon). Per Microsoft, its result changes only when the worksheet is calculated or when a macro containing it runs; it does not update continuously. If either function seems stuck, the settings that control recalculation are the first suspect; Excel formula not calculating walks through them. In Excel for Windows the setting lives under File > Options > Formulas, under Calculation options.

Because dates are numbers, arithmetic on TODAY just works: Microsoft’s own examples include =TODAY()+5 for the date five days from now.

Build a date from its parts with DATE

DATE(year, month, day) exists for exactly this: three separate values, often what an import hands you as separate columns, combined into a date Excel can calculate with. Microsoft’s page even documents the pattern for a text import in the YYYYMMDD shape: =DATE(LEFT(C2,4),MID(C2,5,2),RIGHT(C2,2)) slices the string and rebuilds it as a date.

DATE’s arguments have two documented surprises:

• Months and days roll over. A month greater than 12 is added onto the year: DATE(2008,14,2) returns February 2, 2009. A day beyond the end of the month rolls forward the same way: DATE(2008,1,35) returns February 4, 2008. Useful when you compute the parts, surprising when you mistype them.
• Give the year four digits. The documented rule is that a year from 0 through 1899 is added to 1900, so DATE(108,1,2) means January 2, 2008, and a two-digit “26” would land you in 1926, not 2026.

Pull the day, month or year out of a date

DAY, MONTH and YEAR each take one date and return one integer: the day from 1 to 31, the month from 1 (January) to 12 (December), the year from 1900 to 9999. The results are numbers, never names; August comes back as 8. They combine naturally with DATE, and Microsoft’s DATE page shows the classic move, an anniversary shifted whole years forward: =DATE(YEAR(C2)+5,MONTH(C2),DAY(C2)). The same shape with TODAY gives Microsoft’s age formula, =YEAR(TODAY())-1963, a person’s age as of this year’s birthday.

How do I get the day of the week from a date?

Two formulas answer this, and they return different things. For the day’s name, TEXT renders the date with a weekday format code; for a number you can calculate or sort on, WEEKDAY returns an integer.

WEEKDAY’s optional second argument picks the numbering scheme: with the default (1 or omitted) the week runs 1 (Sunday) through 7 (Saturday), with return type 2 it runs 1 (Monday) through 7 (Sunday), which would make the Saturday above a 6, and further documented types shift the start day around the week. The name route works for months too: =TEXT(A2,"mmmm") returns August and "mmm" returns Aug. The format codes and their traps live with the TEXT function; the one thing to carry from there is that TEXT output is text and stops calculating.

There is also a no-formula route when the underlying date should stay put and only the display should change. Microsoft’s page on showing dates as days of the week walks it: select the cells, open the Number Format list on the Home tab, choose More Number Formats, and on the Number tab under Category choose Custom, then type dddd (or ddd for the short form) in the Type box. The cell still holds the date; only the display changes to the weekday name.

DATEDIF: the difference between two dates

DATEDIF is the odd one out: its own documentation opens with a warning. “Excel provides the DATEDIF function in order to support older workbooks from Lotus 1-2-3.” Microsoft’s page adds that it may calculate incorrect results in some scenarios, so treat it as a legacy convenience rather than a precision instrument. It is still the shortest route to a date gap in the unit you want:

The third argument is a unit code: "D" for days in the period, "M" for complete months, "Y" for complete years, and three partial units that ignore pieces of the dates, "YM" (months, ignoring days and years), "YD" (days, ignoring the years) and "MD" (days, ignoring months and years). That last one carries an explicit caution: Microsoft recommends against "MD", because it “may result in a negative number, a zero, or an inaccurate result”. A #NUM! error means the start date is later than the end date.

Microsoft notes the function is useful for calculating ages, and also points out the simpler route for the most common case: for plain days between two dates, no function is needed at all. =B2-A2 returns 195 for the dates above, because subtracting one stored day count from another is ordinary arithmetic.

EOMONTH: deadlines that land on month end

EOMONTH returns the serial number of the last day of the month a given number of months away from the start date, which is why Microsoft’s page frames it around maturity dates and due dates that fall on month end. Payment terms like “end of the month following the invoice” become one formula. A start date that is not a valid date returns a #NUM! error, and so does a months shift that yields an invalid date.

NETWORKDAYS: working days between two dates

NETWORKDAYS returns the number of whole working days between the start and end dates: weekends are excluded, and so is anything in the optional holidays argument, which accepts a range of cells holding the dates to skip. Microsoft’s framing is employee benefits that accrue per day worked, but the same call counts out project timelines and delivery windows. If any argument is not a valid date, the function returns a #VALUE! error. Weekends here mean Saturday and Sunday; for a working week with different weekend days, Microsoft points at NETWORKDAYS.INTL, which takes the weekend pattern as a parameter.

Why is my date formula not working?

The failure that runs through this whole family is a date that is not a date. Microsoft’s function pages repeat the same warning almost verbatim on DAY, MONTH, YEAR, WEEKDAY, EOMONTH and NETWORKDAYS: enter dates with the DATE function or through other formulas, because a date typed in as text can cause problems. A text entry that merely looks like a date has no serial number behind it, so it will not sort as a date, and functions built for serial numbers can misread or reject it. Spotting one, and the DATEVALUE fix, are covered in Excel date formats.

The short version

• Dates are serial numbers; these functions read and produce them, and the display is the cell format’s job, covered under Excel date formats.
• =TODAY() is the moving current date, =NOW() adds the time; both refresh when the worksheet recalculates.
• =DATE(2026,8,15) builds a date from parts and rolls over out-of-range months and days; DAY, MONTH and YEAR take one apart into numbers.
• Weekday names come from =TEXT(A2,"dddd"), weekday numbers from =WEEKDAY(A2), 1 (Sunday) through 7 (Saturday) by default.
• Gaps: =B2-A2 for days, DATEDIF for months and years (a Lotus 1-2-3 leftover, avoid its “MD” unit), EOMONTH for month-end due dates, NETWORKDAYS for working days minus holidays.
• The recurring trap is a date stored as text: convert it before calculating. And when two versions of a workbook disagree about a date, compare the two files to see every cell that changed, including a date that changed type on the way.