Webbo3 Data Analysis Bootcamp · Excel Module · Lesson 7

Excel Date Functions: TODAY, NOW, DATE, DATEDIF, EDATE, EOMONTH, NETWORKDAYS, and WORKDAY

A hands on lesson on getting, building, measuring, and shifting dates in Excel, with exact syntax, real scenarios, and the genuine quirks each function comes with.

Dates show up everywhere in real spreadsheet work: tracking deadlines, calculating someone's exact age, finding out how many working days are left before a project is due, or figuring out when a subscription renews. The good news is that every date function in Excel works on the same foundation you already learned earlier in this bootcamp: a date is just a number underneath, counting days from a fixed starting point. Once you trust that fact, every function in this lesson is just a different way of doing arithmetic on that number.

1. TODAY and NOW: Current Date and Time

Syntax: =TODAY() and =NOW()

Both functions take no arguments at all, just empty parentheses. TODAY returns only the current date. NOW returns the current date plus the current time, stored as a decimal fraction tacked onto that same date number. If you only care about the date and never the time, default to TODAY, since it keeps your formulas simpler and your displayed values cleaner.

A practical use case. A simple way to flag overdue tasks in a tracker is =IF(TODAY()>C2,"OVERDUE","On track"), where C2 holds a deadline date. Because TODAY recalculates automatically, that formula keeps reassessing every single day without you ever touching it again.

The one behaviour that genuinely surprises people. Both TODAY and NOW are what Excel calls volatile functions, meaning they only recalculate when the workbook itself recalculates, which happens automatically whenever you open the file, edit any cell, or press F9. If you open a workbook today, leave it open without touching anything, and come back tomorrow, the date displayed will not silently update on its own in the background. You need some kind of recalculation trigger to happen first. This matters for anyone building a dashboard meant to sit open on a screen showing "today's date" continuously throughout the day.

If you want a date that never changes, do not use TODAY or NOW at all. Press Ctrl + ; (semicolon) to insert a static, frozen date stamp into a cell, or Ctrl + Shift + ; to insert a static current time. Unlike the formulas, these shortcuts type in a fixed value that will never move again, which is exactly what you want for something like a permanent "date submitted" log entry that should not silently change every time the file reopens.

2. YEAR, MONTH, DAY, HOUR, MINUTE: Extracting Date Parts

These five functions each pull one specific piece out of a full date or time value. =YEAR(A2) returns just the year as a four digit number, =MONTH(A2) returns the month as a number from 1 to 12, and =DAY(A2) returns the day of the month as a number from 1 to 31. If your cell also holds a time component, =HOUR(A2) returns the hour as a number from 0 to 23, and =MINUTE(A2) returns the minute as a number from 0 to 59.

A real use case for this bootcamp. If your dataset has a column of order timestamps and you want to group sales by year for a yearly summary, =YEAR(A2) copied down the column gives you a clean year value you can then feed into a pivot table or a SUMIF formula, without needing the full date and time cluttering your grouping logic.

A practical combination. Calculating someone's age in years using only what you have learned so far would look like =YEAR(TODAY())-YEAR(B2), where B2 holds a date of birth. This is a reasonable first attempt, but it has a real flaw: it only compares year numbers, so it will say someone born in December is already a year older the moment January 1st arrives, even though their actual birthday has not happened yet. You will see the proper fix for this exact problem in the DATEDIF section below.

3. DATE and DATEVALUE: Constructing Dates

DATE syntax: =DATE(year, month, day)

DATE builds a genuine, working Excel date out of three separate numbers you supply. This matters constantly when your raw data arrives split across three columns, for example a year column, a month column, and a day column from an old export, and you need to reassemble them into one real date Excel can actually calculate with. =DATE(2026,6,22) returns 22 June 2026 as a proper date value, not as text that merely looks like a date.

DATEVALUE syntax: =DATEVALUE(date_text)

DATEVALUE solves a different, equally common problem. Sometimes a date arrives in your spreadsheet looking exactly like a date to your eyes, "22/06/2026" for example, but it is actually stored as plain text, often because it was imported from a CSV file or copied from a website. Text dates align to the left of the cell, just like any other text, and every date function in this lesson will refuse to work on them correctly until they are converted into a real date value. =DATEVALUE("22/06/2026") converts that text string into the genuine underlying serial number Excel needs, which you can then format however you like. A fast way to check whether a date is real or just text impersonating one: select the cell and glance at its alignment. If it sits to the left, it is text, not a true date, no matter how convincing it looks.

4. DATEDIF: Calculating Age or Duration

Syntax: =DATEDIF(start_date, end_date, unit)

DATEDIF calculates the difference between two dates in completed years, months, or days, depending on which unit code you give it. This is the correct, accurate way to calculate age, far more reliable than the rough YEAR subtraction shown earlier, because DATEDIF actually checks whether the full year, month, or day has genuinely completed, not just whether the calendar year number has changed.

The six unit codes. "y" returns the number of complete years. "m" returns the number of complete months. "d" returns the total number of days. The other three are combination units used to build a precise breakdown: "ym" returns the remaining months after subtracting whole years, "md" returns the remaining days after subtracting whole months and years, and "yd" returns the remaining days as though both dates fell in the same year. Combining all three into one readable sentence, =DATEDIF(B2,TODAY(),"y")&" years, "&DATEDIF(B2,TODAY(),"ym")&" months, "&DATEDIF(B2,TODAY(),"md")&" days" produces a genuinely accurate age breakdown, for example "27 years, 4 months, 12 days."

Something worth knowing before you go searching for DATEDIF in the ribbon. DATEDIF is what Excel calls an undocumented function. It works completely reliably in every modern version of Excel, but Excel will not show it in the Insert Function dialog, will not autocomplete it as you type, and will not pop up the helpful argument tooltip that other functions get. You simply have to type the full formula out from memory, since Excel offers no guided help for this one specific function.

A genuine, documented bug worth knowing about. The "md" unit has a known reliability issue and can occasionally return an inaccurate or even negative result, particularly around month boundaries where months have different lengths. If you are building something precise that depends on the exact remaining day count, a safer workaround that avoids this specific bug is =end_date-EDATE(start_date,DATEDIF(start_date,end_date,"m")), which calculates the same remaining days but through subtraction rather than through the buggy "md" unit directly. For straightforward age in whole years, which covers the overwhelming majority of real use cases, the plain "y" unit is completely reliable and this workaround is unnecessary.

5. EDATE and EOMONTH: Moving Forward or Backward in Months

EDATE syntax: =EDATE(start_date, months)

EDATE shifts a date forward or backward by a whole number of months while trying to keep the same day number. =EDATE(A2,3) moves three months into the future, and =EDATE(A2,-3) moves three months into the past, using a negative number. If A2 holds 15 March 2026, =EDATE(A2,1) returns 15 April 2026. This is exactly the formula you want for subscription renewal dates, loan repayment schedules, or any anniversary style date that should land on the same day each cycle.

A genuine edge case worth knowing. If your start date falls on the 31st of a month, and the target month you are shifting into does not have 31 days, EDATE does not error out or skip ahead. It clamps the result down to the actual last valid day of that shorter month instead. Shifting 31 January forward by one month with EDATE returns 28 February, not an invalid 31 February or a quietly wrong 3 March.

EOMONTH syntax: =EOMONTH(start_date, months)

EOMONTH looks nearly identical to EDATE in its syntax, but it returns something different: the very last day of the shifted month, completely ignoring the day number of your original start date. =EOMONTH(A2,0) with a months argument of zero returns the last day of the current month, regardless of which day A2 actually points to. =EOMONTH(A2,1) returns the last day of next month. This is the standard formula for generating month end deadlines, financial reporting cutoffs, or quarter end dates in a financial model. The clean rule to remember: use EDATE when you need to preserve the same day number across a shift, like an anniversary or renewal date, and use EOMONTH when you specifically need the last day of a month, regardless of what day you started counting from.

6. NETWORKDAYS and WORKDAY: Working Day Calculations

NETWORKDAYS syntax: =NETWORKDAYS(start_date, end_date, [holidays])

NETWORKDAYS counts the number of working days between two dates, automatically excluding Saturdays and Sundays, and optionally excluding any extra dates you specify as holidays, for example public holidays your team does not work. =NETWORKDAYS(B2,C2) returns the count of business days between a project start and end date, ignoring weekends. To also exclude public holidays, list those holiday dates somewhere on your sheet, then reference that range as the third argument, for example =NETWORKDAYS(B2,C2,$F$2:$F$10), locking the holiday range as an absolute reference so it does not shift if you copy the formula down a column of different project rows.

A detail that catches people off guard. NETWORKDAYS includes both the start date and the end date in its count, as long as neither one is a weekend or a listed holiday. If you give it the exact same date for both start and end, and that date is a normal weekday, it returns 1, not 0, because it is counting that single day as one working day, not measuring a gap of zero.

WORKDAY syntax: =WORKDAY(start_date, days, [holidays])

WORKDAY does the reverse calculation. Instead of counting working days between two known dates, you give it a starting date and a number of working days to add, and it tells you exactly which date you land on, automatically skipping weekends and any listed holidays along the way. =WORKDAY(B2,10) tells you the date exactly ten working days after the date in B2, which is precisely the formula you want for calculating a delivery deadline, a payment due date, or any "X business days from now" commitment. Use a negative number, for example =WORKDAY(B2,-5), to count backward instead, useful for working out a deadline that needs to fall five working days before some fixed event. One small but important detail: WORKDAY does not count the start date itself as one of the working days being added. It begins counting forward from the very next day, so the result always lands strictly after the original start date, never on it.

Using AI to Move Faster in Excel

Date logic is one of the areas where AI inside Excel earns its place quickly, because the functions in this lesson have real, documented quirks, undocumented status, clamping behaviour, and known bugs, that are genuinely easy to forget under pressure, even once you understand them properly.

1. Let Copilot recall the exact DATEDIF unit codes for you.
Since DATEDIF gets zero help from Excel's own interface, no autocomplete, no tooltip, it is one of the easiest formulas to misremember. Instead of guessing whether you need "ym" or "yd" for a specific breakdown, describe the outcome directly to Copilot, for example "Calculate this person's exact age in years, months, and days from their date of birth in B2." Copilot will typically generate the full three part DATEDIF combination correctly, including the right unit codes in the right order, which you can then read and learn from rather than memorising cold.

2. Ask Copilot to flag known function quirks before you build on top of them.
If you are about to rely heavily on DATEDIF's "md" unit, or you are unsure whether you need EDATE or EOMONTH for a specific deadline calculation, ask directly: "Are there any known issues with the formula I'm about to use for this date calculation?" This is exactly the kind of context a good prompt can surface quickly, the same caveats covered in this lesson, before you discover the bug the hard way inside a finished report.

3. Use natural language to build holiday aware working day formulas fast.
Setting up NETWORKDAYS or WORKDAY correctly with a custom holiday list, and locking that range as an absolute reference so it survives being copied down a column, has several small steps that are easy to fumble. Describe the actual business rule to Copilot, for example "Calculate the delivery date 15 working days from the order date in B2, skipping the public holidays listed in column F," and let it assemble the full formula, holiday range and all, in one pass.

4. Always check whether a date is real before trusting any date formula on it.
This rule does not change regardless of how the formula was generated. Before relying on any AI suggested date formula, confirm the source cells actually hold genuine dates rather than text that merely looks like one, since every function in this lesson silently fails or behaves unpredictably on text dates. A quick =ISNUMBER(A2) check returning TRUE confirms you are dealing with a real date before you build anything more complex on top of it.

A habit worth carrying from this lesson onward: whenever a date calculation needs to handle a genuine business rule, working days only, holidays excluded, same day of the month preserved across a renewal, pause and name that rule out loud to yourself, or to Copilot, before reaching for a formula. Dates are one of the few areas in Excel where the function names alone do not tell you everything you need to know, and knowing exactly which quirk applies to which function is what separates a date formula that works today from one that quietly breaks the first time it hits an edge case.

Next lesson: logical formulas, starting with IF, AND, and OR.