Excel Date Functions: TODAY, NOW, DATEDIF, NETWORKDAYS & More

Most Excel users learn SUM, VLOOKUP, and a couple of IF formulas — and then they get stuck the first time they need to do real math with dates. The excel date functions library is its own little universe, and once you understand how Excel actually stores dates, the rest of it stops feeling like guesswork.

Here's the core idea. Excel doesn't store dates as text. It stores them as numbers. January 1, 1900 is the number 1. January 2, 1900 is 2. December 31, 2025 is 46022. Time is the decimal part — noon is .5, 6 PM is .75. That's it. Every formula in this guide works because of that one rule.

That's why you can subtract two dates and get "days between" for free. It's why TODAY() and NOW() return numbers that look like dates but behave like math. And it's why dates before 1900 cause weird errors that frustrate finance and HR teams pulling old records.

This guide covers every function you'll actually use. TODAY and NOW for live dates. DATE, YEAR, MONTH, DAY for constructing and dismantling them. WEEKDAY, WEEKNUM, and ISOWEEKNUM for reporting. EOMONTH and EDATE for month-end and offset logic. DATEDIF — Microsoft's hidden, undocumented gem — for age and tenure. NETWORKDAYS and WORKDAY for business calendars. TEXT and DATEVALUE for bridging text and dates. YEARFRAC for fractional years. And the format codes that decide how all of it looks.

You'll also get the gotchas: leap years, the phantom February 29, 1900 bug Microsoft kept for Lotus compatibility, the difference between Excel for Web and Desktop, and what to do when a column of dates is really text masquerading as dates.

Read it once, build the example sheet alongside, and you'll have date math locked down for the rest of your career. If you need to brush up on basic syntax first, the excel formulas primer is the place to start.

Before any function makes sense, you need the serial number system in your head. Excel stores every date as an integer counting days from January 1, 1900. That date is the number 1. January 2, 1900 is 2. June 15, 2024 is 45458. December 31, 9999 is 2958465, which is also the maximum date Excel will store.

Type any date into a cell — 6/15/2024, June 15 2024, 2024-06-15 — and Excel converts the text to its serial number behind the scenes, then formats it back as a date for display. You can prove this by typing a date, then changing the cell format to General or Number. The number you see is the serial.

Time works the same way but as a decimal fraction of a day. 6:00 AM is 0.25, noon is 0.5, 6:00 PM is 0.75, 11:59 PM is roughly 0.9993. A full datetime — say June 15, 2024 at 3:30 PM — is stored as 45458.6458333. The integer part is the date, the decimal part is the time. Add or subtract dates and you're doing arithmetic on these numbers. That's the entire trick.

This explains why =B2-A2 returns the number of days between two dates without any function call at all. It also explains why =(B2-A2)*24 returns total hours, and =(B2-A2)*24*60 returns total minutes. You're just multiplying days by the appropriate conversion.

One thing trips up almost everyone the first time: Excel includes a phantom February 29, 1900 in its serial system. The real year 1900 was not a leap year (century years divisible by 100 but not 400 aren't), but Lotus 1-2-3 had this bug and Microsoft kept it for compatibility. The practical effect is that any date math involving days before March 1, 1900 is off by one. For modern financial and HR data, you'll never notice.

One last serial-system fact worth knowing: Mac Excel used to default to a 1904 date system (serial 1 = January 2, 1904), which caused painful problems when sharing files between Mac and Windows users. Modern Excel for Mac defaults to the 1900 system to match Windows, but you may still see the older flag in files exchanged with older Mac workbooks. If dates suddenly look four years and one day off, check File → Options → Advanced → "Use 1904 date system" and untick it.

This serial-number foundation is what makes the rest of the date functions powerful. When you call YEAR(45458), Excel does the calendar math to return 2024. When you call EOMONTH(45458, 0), it adds days until it hits the last day of that month. None of these are mysterious — they're calendar arithmetic on integers, wrapped in friendly names.

Build the example sheet as you read: column A with dates, column B with formulas, column C with the result formatted as General to see the serial. You'll internalize the model in 10 minutes, and after that, the rest of this guide is just memorization.

Beyond these three, a few constructor helpers earn a place in your toolkit. DATEVALUE converts text that looks like a date into a real date serial — useful when you import CSV data and dates land as left-aligned text. =DATEVALUE("6/15/2024") returns 45458. If the text isn't recognizable as a date, DATEVALUE returns #VALUE!.

The opposite move is TEXT, which converts a date serial back to formatted text. =TEXT(A1, "mm/dd/yyyy") returns the date in A1 as the string "06/15/2024". This is essential when concatenating dates into strings: ="Report run on " & TEXT(TODAY(), "mmmm d, yyyy") produces "Report run on June 15, 2024". Without TEXT, you'd get the serial number embedded in the string.

Common TEXT format codes: m single-digit month, mm two-digit, mmm three-letter abbreviation (Jun), mmmm full name (June), mmmmm first letter only (J). Same pattern for days: d, dd, ddd (Sat), dddd (Saturday). Years are yy or yyyy.

Quick exercise: type today's date into A1, then in B1 try =TEXT(A1, "dddd, mmmm d, yyyy"). You'll get something like "Saturday, June 15, 2024." Useful for report headers and email automation.

Putting these together opens up most reporting work. Want to count sales in June 2024? =SUMIFS(B:B, A:A, ">="&DATE(2024,6,1), A:A, "<="&DATE(2024,6,30)). Want to flag every Friday in a date column? =IF(WEEKDAY(A2,2)=5, "Friday", "") using Monday-start convention so Friday is 5. Want to get the start of the current quarter? =DATE(YEAR(TODAY()), FLOOR(MONTH(TODAY())-1,3)+1, 1) — divides the current month by 3, floors it, and constructs the quarter's first day.

Custom day-of-week labels are a frequent ask. The cleanest pattern is CHOOSE with WEEKDAY: =CHOOSE(WEEKDAY(A1,2), "Mon","Tue","Wed","Thu","Fri","Sat","Sun"). Or just use TEXT: =TEXT(A1, "ddd") returns "Sat". TEXT is shorter but harder to translate to non-English; CHOOSE gives you total control.

One pattern that surprises newcomers: =DATE(YEAR(A1), MONTH(A1), 1) returns the first day of A1's month. Combine with EOMONTH(A1,0) for the last day, and you have a clean month range without hardcoding. Most production reports use this pattern to build dynamic date filters.

DATEDIF deserves the highlight because it solves problems no other function does cleanly. Age calculation: =DATEDIF(BirthDate, TODAY(), "Y") returns age in completed years. Tenure: =DATEDIF(HireDate, TODAY(), "Y") & " years, " & DATEDIF(HireDate, TODAY(), "YM") & " months" produces a clean "5 years, 3 months" string for HR dashboards. Days-since-last-purchase, contract age, equipment service intervals — DATEDIF is the workhorse.

The one quirk: DATEDIF returns errors for end < start and for some "MD" combinations near month boundaries (a known bug Microsoft has never fixed). Wrap in IFERROR if you're calculating against user input that might be inverted: =IFERROR(DATEDIF(A1, B1, "Y"), "check dates").

For business-day arithmetic — the kind finance and project teams need — Excel provides NETWORKDAYS and WORKDAY. NETWORKDAYS(start, end, [holidays]) returns the count of working days between two dates, excluding Saturdays, Sundays, and any optional holiday list. =NETWORKDAYS(A1, B1, Holidays!A:A) tells you how many actual working days a project will take.

WORKDAY does the opposite — given a start date and a number of working days, it returns the end date. =WORKDAY(TODAY(), 10, Holidays!A:A) returns the date 10 working days from now, skipping weekends and the holidays you supply. Project deadlines, SLA expirations, and payroll cutoffs lean heavily on WORKDAY.

Both functions have INTL variants — NETWORKDAYS.INTL and WORKDAY.INTL — that let you redefine "weekend." Middle East workbooks where Friday-Saturday is the weekend use =NETWORKDAYS.INTL(A1, B1, 7) (the 7 selects Friday-Saturday as weekend). Custom 4-day or 6-day work weeks are also supported via a string of 0s and 1s.

For fractional-year math — interest calculations, age in decimals, prorated-fee logic — YEARFRAC is the function. =YEARFRAC(A1, B1) returns the years between A1 and B1 as a decimal (e.g., 2.347 years). The optional third argument is a day-count basis: 0 for US 30/360, 1 for actual/actual, 2 for actual/360, 3 for actual/365, 4 for European 30/360. Finance teams use basis 1 or 3 most often; the rest exist for bond and treasury conventions.

DAYS is the simplest function in the family — =DAYS(end, start) returns the integer days between two dates. It's identical to =end-start, just named. Some style guides prefer DAYS for readability in long formulas. Note the argument order is end first, start second — opposite of NETWORKDAYS.

Date arithmetic without any function works the same way. =A1+30 adds 30 days to the date in A1. =A1-7 goes back a week. =(B1-A1)*24 converts the day difference into hours. =TIME(8,0,0)+A1 shifts a date by 8 hours. The serial-number model means addition, subtraction, multiplication, and division all work natively.

One slick pattern for shift scheduling: store start time as a date+time, then =A1+TIME(8,30,0) for the end of an 8.5-hour shift. =(EndTime-StartTime)*24 converts to billable hours. Combine with NETWORKDAYS and you can build a full timesheet system in 20 minutes — no add-ins required.

Once you can build dates and do arithmetic with them, the next skill is formatting them so humans can read the output. Excel separates the underlying serial number from the displayed format — same number, dozens of possible display options. You change the display by selecting the cell, pressing Ctrl+1 for Format Cells, and choosing Date or Custom.

The standard built-in date formats cover most needs: 6/15/2024 (short date), Saturday, June 15, 2024 (long date), 15-Jun-24, June 2024, and so on. Click Custom in Format Cells to see and edit the underlying format code — that's where the real power is.

Format code components: m month number, mm with leading zero, mmm three-letter (Jun), mmmm full name (June). d day, dd with leading zero, ddd three-letter (Sat), dddd full name (Saturday). yy two-digit year, yyyy four-digit. Time: h/hh hour, m/mm minute (context-dependent — Excel knows whether m after h means minute), s/ss second, AM/PM for 12-hour clock.

A handful of formats cover 90% of real reporting. m/d/yyyy for US standard. d-mmm-yy for compact international (15-Jun-24). mmm yyyy for month headers (Jun 2024). dddd, mmmm d, yyyy for formal letters. yyyy-mm-dd for ISO sort-friendly. h:mm AM/PM for clock time. [h]:mm:ss with brackets for elapsed time that can exceed 24 hours.

Custom formats can also include color and conditional logic with semicolons. The pattern is [positive];[negative];[zero];[text], and each section can have its own color tag in square brackets. For a due-date column that flags overdue items: [Red]"Overdue!";[Green]m/d/yyyy. The first section applies to negative numbers (which is what TODAY()-DueDate returns when overdue), the second to positive (still in the future).

For year-over-year comparison formulas, the common pattern is to compute current-period and prior-period totals, then take the difference. Sales this month vs last month: =SUMIFS(Sales, Date, ">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1), Date, "<="&TODAY()) - SUMIFS(Sales, Date, ">="&EDATE(DATE(YEAR(TODAY()),MONTH(TODAY()),1),-1), Date, "<"&DATE(YEAR(TODAY()),MONTH(TODAY()),1)). Ugly but it works — using EDATE to shift by exactly one month avoids end-of-month edge cases that pure subtraction misses.

For year-over-year, swap EDATE(date, -1) for EDATE(date, -12). Same logic, different offset. Some teams prefer to compute YoY change as a percentage: =(CurrentPeriod / PriorPeriod) - 1 formatted as percentage gives "15.3%" growth or "-4.2%" decline.

Excel for the Web and Excel Desktop now share near-identical date function support. Every function in this guide — TODAY, NOW, DATE, DATEDIF, NETWORKDAYS, WORKDAY, EOMONTH, EDATE, YEARFRAC, ISOWEEKNUM, DAYS — works in both environments as of 2024. The one historical gap was NETWORKDAYS.INTL and WORKDAY.INTL in older Web versions, but those have been at parity since 2022.

Errors in date math are almost always one of three culprits. #VALUE! means a function received text it can't interpret as a date. Imported CSVs often store dates as text — left-aligned by default rather than right-aligned. Wrap with DATEVALUE: =DATEVALUE(A1)+30 instead of =A1+30. Or fix the column once by selecting it and choosing Data → Text to Columns → Finish, which forces re-parsing.

#NUM! appears when a date is out of range. DATE(2024, 2, 30) doesn't error — it rolls to March 1 — but DATEDIF with end-before-start does. So does any direct math producing a serial above 2958465 (year 9999) or below 0. Real-world cause is usually swapped arguments in DATEDIF; check that start really is earlier than end.

#NAME? on DATEDIF specifically means you have a typo (DATEDIFF with two F's is the most common — that's the SQL Server function, not Excel's). Excel's autocomplete won't suggest DATEDIF, so people sometimes write DATEDIFF instinctively. Drop the second F and the formula works.

Leap year quirks: February 29 only exists every four years (except century years not divisible by 400). DATE(2024, 2, 29) is valid; DATE(2023, 2, 29) silently rolls to March 1, 2023. If you need to handle leap-year birthdays for tenure or contract calculations, EDATE preserves the day where possible and uses the last day of the target month otherwise — so a Feb 29 birthday on a non-leap year EDATE result is Feb 28, which matches legal convention in most jurisdictions.

Power Query — the data-cleaning tool built into Excel under Data → Get & Transform — handles dates with its own family of functions. Inside Power Query, dates are formal date types, not serial numbers, so #VALUE! issues from imports are easier to fix. Add a custom column with Date.AddMonths([StartDate], 6) or Date.NetworkDays([Start], [End], Holidays). The M-language functions mirror Excel's but with cleaner syntax.

Power Query is the right tool for date cleaning on imports — converting text-as-date columns en masse, parsing inconsistent date strings ("6/15/24" mixed with "June 15 2024"), and stripping time components from datetime columns. Once cleaned and loaded back into Excel, all the standard date functions work normally. For more on this pipeline, see the excel power query guide.

Two patterns worth memorizing for power-user work. First, the FLOOR pattern for bucketing dates: =FLOOR(A1, 7) + 1 snaps a date to the Monday of its week (since serial 1 was a Sunday, +1 shifts to Monday). Useful for weekly aggregation. Second, the SUMPRODUCT pattern for multi-criteria date filtering before SUMIFS existed: =SUMPRODUCT((YEAR(DateRange)=2024) * (MONTH(DateRange)=6) * SalesRange) sums sales where the date is in June 2024. SUMIFS is faster for huge ranges, but SUMPRODUCT remains useful for complex multi-condition logic where SUMIFS gets clunky.

One final piece of advice that pays off forever: store dates as dates. Don't store "06/15/2024" as text, don't store the day, month, and year in separate columns hoping to be clever, don't use Excel as a typewriter. A real date serial sorts correctly, filters correctly, math correctly, and renders in any locale. Every minute you spend forcing your raw data into the date serial system pays back ten-fold when you start running formulas. If you need to clean an imported column once, do it once with DATEVALUE or Power Query, then never look back.

One last set of tips you'll thank yourself for later. Keyboard shortcuts save real time on date-heavy work. Ctrl+; inserts today's date as a static value (not TODAY(), which keeps changing). Ctrl+Shift+; inserts the current time. Ctrl+Shift+3 applies the d-mmm-yy date format quickly. Ctrl+Shift+2 applies the h:mm AM/PM time format.

If you're building dashboards that need a date picker, the Forms control or ActiveX DTPicker gives users a calendar UI rather than a typed cell — useful when non-technical colleagues are filling out templates. Insert via Developer tab; the date selected lands as a serial in a linked cell, ready for any of the formulas in this guide.

For repeated date-cleaning chores, build a one-line LAMBDA function (Excel 365+). =LAMBDA(d, IF(ISTEXT(d), DATEVALUE(d), d)) defined in Name Manager as CleanDate becomes a reusable helper you can call as =CleanDate(A1) on any column where dates might be text. Stop copy-pasting the same IFERROR everywhere.

Bottom line: Excel date functions look intimidating because there are 20 of them and they have similar names. In practice, you'll use eight or ten regularly — TODAY, NOW, DATE, DATEDIF, EOMONTH, EDATE, NETWORKDAYS, WORKDAY, TEXT, DATEVALUE — and the rest only when you hit a specific edge case. Build the example sheet alongside this guide, save it as your reference, and date math becomes muscle memory within a week. Combine it with the excel formulas fundamentals and the pivot tables excel workflow and you'll handle 95% of business reporting without breaking a sweat.