Excel Date Formulas: Complete Guide With Real Examples 2026

Excel date formulas turn your spreadsheet into a real scheduling engine. They calculate ages, count working days, project deadlines, and flag overdue invoices without you touching a calculator. The catch is that Excel does not store dates the way humans read them. Behind every date you see is an integer counting days since January 1, 1900. Understand that one fact and the entire date function family clicks into place. Practice with the free Excel practice test on PracticeTestGeeks (500+ questions with answer explanations).

If you are sitting an Excel certification or a hiring test, expect at least three date-related questions on the practice section. Examiners love DATEDIF, NETWORKDAYS, and EOMONTH because they reveal whether you understand serial dates or just memorized formulas. The good news? Once you nail the dozen core functions, every date problem becomes a quick recipe. Drill these alongside the broader Excel formulas guide and you will lock in the patterns.

Microsoft Excel Exam at a Glance

Facts verified Oct 17, 2026 against Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business. Dates that have passed are removed automatically.

Free Excel Study Tools

Interactive Excel resources on PracticeTestGeeks, free to start:

• Free Excel Questions and Answers — 16 questions with instant scoring and answer explanations
• Excel flashcards — review key terms and concepts card by card
• Excel study guide — topic-by-topic overview of what the exam covers
• Excel cheat sheet — one-page printable summary for last-minute review

The foundation is =DATE(year, month, day). Feed it three integers and it returns a real serial date that you can format any way you like. =DATE(2026,5,14) returns 46150 under the hood, formatted as 5/14/2026 by default. The function is smart about overflow — pass =DATE(2026,13,5) and you get January 5, 2027 because Excel rolls month 13 forward into the next year.

Most analysts never type DATE by hand. They get dates from imports or formulas like TODAY() and NOW(). TODAY() returns just the date with no time component. NOW() returns date plus current time, recalculating every time the workbook opens or any cell updates. Both are volatile, so use sparingly on huge sheets where constant recalculation hurts performance.

The simplest math you can do with dates is subtraction. =B1-A1 returns the number of days between two dates because Excel just subtracts the underlying serial numbers. That alone solves half of all date problems. The other half need helper functions like DATEDIF, EDATE, and EOMONTH, which we will walk through next.

DATEDIF deserves special attention because it is technically undocumented in modern Excel. Microsoft inherited it from Lotus and never officially added it to the help files, but every version since Excel 5 supports it. The hidden unit codes are where the magic lives: "y" for whole years, "m" for whole months, "d" for total days, "yd" for days ignoring years, "ym" for months ignoring years, and "md" for days ignoring months. Combine them and you get a human-readable age like 34 years, 2 months, and 11 days.

The formula looks like this: =DATEDIF(A1,B1,"y")&" years, "&DATEDIF(A1,B1,"ym")&" months". String concatenation with ampersands stitches the parts together. This is a favorite interview puzzle because it tests whether you know DATEDIF exists in the first place. The Excel cheat sheet has the full list of unit codes pinned at the top.

Time values are dates with a fractional component. Noon on January 1, 2026 is 46023.5 because half a day equals 0.5. That is why you can do =B1-A1 on two timestamps and get the elapsed hours as a decimal. Multiply by 24 to get hours, by 1440 to get minutes, by 86400 to get seconds. The math feels weird the first time, but it is incredibly consistent once you accept it.

One classic gotcha is summing hours that cross 24. By default, Excel rolls over after 24 hours, so 18:00 plus 10:00 displays as 4:00. To force a running total, apply the custom format [h]:mm — the square brackets tell Excel to keep counting past 24. This shows up constantly on timesheet questions and tripping over it once is enough to remember the fix forever.

Conditional formatting on dates is where business dashboards get their color. Highlight every row where the due date is in the past with the rule =$B2<TODAY(). Use =$B2-TODAY()<=7 to flag anything due in the next week. The dollar sign locks the column so the rule applies row-wise across your data range. One formula, every overdue task glowing red — no macros required.

You can chain this with NETWORKDAYS to build a real project tracker. =NETWORKDAYS(TODAY(),B2) returns business days remaining until deadline. Negative results mean the deadline already passed. Combine with IF to swap colors at different thresholds: red for less than 3 days, amber for less than 7, green otherwise. This pattern appears on advanced Excel hiring tests because it proves you can compose multiple functions cleanly.

Pros and Cons at a Glance

YEAR, MONTH, and DAY extract components from a real date. =YEAR(A1) returns just the year as a number, MONTH returns 1 to 12, DAY returns 1 to 31. Use them inside pivot tables or as helper columns for grouping. WEEKNUM goes a step further and returns the ISO week of the year, which is gold for fiscal reporting.

For age calculations from a birthday, the cleanest formula is =DATEDIF(A1,TODAY(),"y"). It rolls forward only on the actual birthday, unlike YEAR(TODAY())-YEAR(A1), which gives the wrong age for anyone whose birthday has not happened yet this year. This is the most common interview gotcha for spreadsheet roles, and it appears in nearly every certification exam. Practice it inside the SUM formula in Excel tutorial when you build aggregations by age band.

Power users build dynamic date ranges with the SEQUENCE function in Microsoft 365. =SEQUENCE(30,1,TODAY(),1) spills the next 30 days down a column starting from today. Wrap it in TEXT or format the cells as dates and you have an instant calendar. Combine with WORKDAY and you generate a list of business days only. This was nearly impossible before dynamic arrays — now it is one line.

Sorting dates is straightforward once they are real serials. Just sort the column. Text dates sort alphabetically, which puts 1/2/2026 before 12/1/2025 because "1" comes before "12" in a string comparison. If your sort looks wrong, the dates are text. Fix the data, not the sort settings. The remove spaces in Excel guide also covers cleaning imported text that masquerades as dates.

Mac Excel handles dates identically to Windows in all modern versions. Older Mac versions (pre-2011) used a 1904 date system as their default, which offset every date by four years. You can still find legacy files in this mode under Options > Calculation. If you copy data from a 1904 workbook to a 1900 workbook, dates jump by 1,462 days. Always check both files use the same base date before pasting, or use Paste Special > Values plus a manual adjustment.

For VBA users, the Date data type stores values identically to worksheet cells but the syntax for parsing is different. Application.WorksheetFunction.NetworkDays works in macros, but native VBA functions like DateSerial and DateAdd are usually faster. The Excel VBA guide breaks down when to call worksheet functions versus native VBA, including the timing differences that matter on large loops.

Beyond the basics, dynamic arrays bring whole new patterns to date formulas. =FILTER(A2:C100,B2:B100>=TODAY()) returns only the rows where the date column is today or later. No helper column, no manual filter. Pair with SORT to get a clean upcoming-events list that updates automatically. This is what hiring managers mean when they say "modern Excel" on a job description.

UNIQUE plus YEAR gives you every distinct year in your dataset with one line. =UNIQUE(YEAR(A2:A1000)) returns 2024, 2025, 2026 — whatever appears in your data. Drop it next to a SUMIFS column and you have an instant annual summary without building a pivot table. This pattern is fast, transparent, and survives data changes without manual refresh steps.

Conditional formatting on date ranges deserves its own section. Highlight every row in the current quarter with =CEILING(MONTH($B2),3)=CEILING(MONTH(TODAY()),3). The CEILING trick rounds months up to the nearest multiple of three, giving you 3, 6, 9, 12 — exactly the quarter ends. Compare to today and the rule fires for any row in the same quarter. Reviewers love this kind of formula composition because it shows you understand both date arithmetic and rule logic.

Auditing a workbook full of date formulas is easier than it looks. Use Formulas > Evaluate Formula to step through any nested expression one operation at a time. Excel shows the intermediate value at each step, so you can spot exactly where DATEDIF returned a surprise or where a TEXT call broke the serial. This tool is criminally underused — turn it on the first time a date formula misbehaves and you will never debug blind again.

Fiscal year math trips up almost everyone the first time. Most companies do not run on a January-to-December calendar. A July-to-June fiscal year means May 2026 belongs to fiscal year 2026, but September 2026 belongs to fiscal year 2027. The formula =YEAR(A1)+IF(MONTH(A1)>=7,1,0) handles this in one line. Adjust the 7 to whatever month your fiscal year starts in. This single trick saves analysts hours when building rolling annual reports.

Quarter calculations follow the same pattern. ="Q"&ROUNDUP(MONTH(A1)/3,0) returns Q1 through Q4 for a calendar year. For a fiscal year starting in July, shift the months first: ="Q"&ROUNDUP(MOD(MONTH(A1)-7,12)/3+0.01,0). The MOD function wraps the months around so July becomes 1, August becomes 2, and so on. Ugly but functional. Most CFOs do not care about elegance — they care about the right answer.

Building a calendar grid in Excel is a classic exam question. Start with =DATE(2026,1,1) in one cell and use =A1+1 in the next to spill seven days across a row. Drop down to the next row and reference the cell one week back: =A1+7. Now drag and Excel fills in every date for the year. Apply conditional formatting based on MONTH to alternate colors between months. Add WEEKDAY rules to flag weekends. This is the kind of practical project that proves you can wield date functions without looking up syntax mid-meeting.

Subscription and recurring billing logic leans heavily on EDATE. =EDATE(start_date, billing_cycle_months) returns the next billing date. Wrap it in a loop or drag down a column to generate an entire payment schedule. Combine with EOMONTH if your billing snaps to the last day of each month — common for SaaS contracts and rental agreements. The formula =EOMONTH(start, n) returns the end of the nth month from start, including or excluding the start month depending on whether n is zero or positive.

Holiday handling deserves its own deep dive. NETWORKDAYS accepts a third argument: a range of dates representing holidays to skip. =NETWORKDAYS(A1,B1,Holidays!A:A) reads from a dedicated sheet where you list every observed holiday. Update that sheet once a year and every NETWORKDAYS formula across your workbook adjusts automatically. This is dramatically better than hard-coding holiday lists in individual formulas.

NETWORKDAYS.INTL is the international cousin that lets you customize which days count as weekends. Pass "0000110" as the third argument and Friday-Saturday weekends apply — useful for Middle Eastern markets. Pass "0000011" and you get the standard Saturday-Sunday treatment. The string has seven positions, one per day, where 1 means weekend and 0 means working. This is a niche function that scores big on exams testing international scenarios.

Age-by-band reporting is another common requirement. You want to group people into 0-17, 18-34, 35-54, 55+. Use =LOOKUP(DATEDIF(A1,TODAY(),"y"),{0,18,35,55},{"Child","Young Adult","Adult","Senior"}) and Excel returns the right label for every row. LOOKUP with two array constants is faster than nested IF for clean banding logic. Pivot tables then count by band without any extra work.

One last performance note: if you have a huge sheet with thousands of date formulas, consider converting completed periods to static values. Right-click the column, Copy, Paste Special > Values. The dates stay intact but the formulas vanish, removing recalculation overhead. Only do this for closed periods where the math will never change. Live periods stay as formulas so updates flow through automatically.

Time zone math is messy in Excel because the application has no native time zone awareness. A timestamp is just a number. If you need to shift between time zones, store the offset as a separate column and add or subtract fractions of a day. Eastern time to Pacific is minus three hours, so =A1-3/24 does the conversion. For daylight saving, you need lookup tables tied to date ranges. Most teams hand this off to Power Query, which handles time zones natively through the DateTimeZone data type.

The TEXT function deserves a final mention because it bridges date math and display. =TEXT(A1,"dddd") returns the full weekday name like "Thursday". =TEXT(A1,"mmm yyyy") returns "May 2026". =TEXT(A1,"q") returns the quarter number. These formatted strings can feed pivot table groups, chart labels, or report headers. Just remember the result is text — math operations on the output will not work the way they would on a real date value.

For a deeper look at this topic, see our guide to excel date formulas.

Many readers pair this guide with the ms excel to check their readiness before exam day.

Many readers pair this guide with the excel download free to check their readiness before exam day.

Putting it all together, your Excel date toolkit should include DATE, TODAY, NOW, YEAR, MONTH, DAY, WEEKDAY, WEEKNUM, DATEDIF, EDATE, EOMONTH, NETWORKDAYS, WORKDAY, DATEVALUE, and TEXT. That is fifteen functions that cover 95% of real-world date scenarios. Master them in that order and you will handle any spreadsheet date problem confidently — whether it lands in a job interview, a certification exam, or a Friday-afternoon fire drill from your manager.

If you want structured drill time, the practice tests on this site cycle through every common date scenario including age calculation, business day counting, fiscal year shifts, and text-to-date conversion. Pattern recognition matters more than memorizing syntax — once you see twenty variants of "calculate days between" you stop second-guessing and just write the formula. Pair the drills with the excel open beside you for the first few sessions, then put it away once the muscle memory kicks in.

Sample Excel Practice Questions

Try these questions from our free Excel practice tests. The correct answer and an explanation follow each question.

1. Which feature adds interactive date-based filtering with a draggable time range to a PivotChart?

   • A. Timeline
   • B. Scroll bar
   • C. Form control spinner
   • D. Watch Window

Answer: A. Timeline

A Timeline slicer filters PivotTables and PivotCharts by date periods.

Answer: B. Ctrl+;

Ctrl+; (semicolon) inserts the current date as a static value into the active cell.

Answer: A. A) =SUMIF(SalesData[Region],"East",SalesData[Revenue])

The correct formula uses the SUMIF function, which is designed for conditional summing. The structured references 'SalesData[Region]' and 'SalesData[Revenue]' correctly refer to the entire data range of the 'Region' and 'Revenue' columns within the 'SalesData' table, making the formula dynamic and readable.

Answer: D. 1A

Cell references in the A1 style must begin with the column letter(s) followed by the row number.

Take the full Excel practice test