Use Advanced Date and Time Functions

In Excel, dates are not just text strings; they are actually serial numbers representing the number of days since January 1, 1900. Because dates are just numbers under the hood, you can easily perform mathematical operations on them.

Part 1: Dynamic References & The "Ghost Change" Effect

TODAY() & NOW()

• TODAY(): Returns the current date.
• NOW(): Returns the current date and the current time.
• Official Docs: TODAY | NOW

Part 2: Calculating Days and Workdays

WEEKDAY()

• What it does: Returns a number (1-7) representing the day of the week.
• Pro-Tip: Use return_type 2 to make Monday = 1 and Sunday = 7.
• Official Docs: WEEKDAY function

WORKDAY()

• What it does: Calculates a date $N$ days in the future, but automatically skips weekends (Saturday/Sunday).
• When to use it: Calculating invoice due dates or project deadlines that shouldn't land on a Sunday.
• Official Docs: WORKDAY function

Part 3: Month Management (EOMONTH)

In data analytics, we usually standardize dates to the beginning of the month so that data from Jan 5th and Jan 25th all group together.

EOMONTH() (End of Month)

• Syntax: =EOMONTH(start_date, months_offset)
• Official Docs: EOMONTH function

💡 The Month-Shift Cheatsheet

Use these formulas to instantly find reporting boundaries. (Assume A2 is your date).

Part 4: Practical Math

Since dates are numbers, you can add and subtract them directly: * Days between two dates: =End_Date - Start_Date * Date in 10 days: =A2 + 10

Part 5: Counting Working Days

Subtracting two dates gives the calendar day difference. For business reporting — SLAs, payroll, project tracking — you usually want working days only, with weekends and holidays excluded.

NETWORKDAYS()

• What it does: Returns the number of whole working days between two dates, automatically excluding Saturdays and Sundays. An optional third argument lets you list holidays.
• When to use it: Calculating SLA breach windows, billable workdays in a month, or how many business days a ticket has been open.
• Syntax: =NETWORKDAYS(start_date, end_date, [holidays])

• Official Docs: NETWORKDAYS function

• Example: excel =NETWORKDAYS(A2, B2, Holidays!$A$2:$A$30) Where A2 is the project start, B2 is the project end, and Holidays!$A$2:$A$30 is a static range of company holiday dates.

NETWORKDAYS.INTL() (International)

• What it does: Same as NETWORKDAYS, but lets you redefine which days count as "the weekend." Critical for international and Middle East workbooks where the week structure isn't Mon–Fri.
• When to use it: Working with calendars where the weekend isn't Saturday/Sunday — for example, an Israeli workweek (Sun–Thu, weekend = Fri/Sat) or a Saudi calendar (Sun–Thu, weekend = Fri/Sat in the modern Gregorian convention).
• Syntax: =NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])
• Official Docs: NETWORKDAYS.INTL function

The third argument accepts either a numeric code or a 7-character bitmask string.

The bitmask reads Mon, Tue, Wed, Thu, Fri, Sat, Sun. A 1 means "weekend (non-working)," a 0 means "working."

• Example (Israeli workweek — Friday and Saturday are the weekend): excel =NETWORKDAYS.INTL(A2, B2, 7, Holidays!$A$2:$A$30)
• Example (custom: only Friday off, using bitmask): excel =NETWORKDAYS.INTL(A2, B2, "0000100", Holidays!$A$2:$A$30)

WORKDAY.INTL()

• What it does: The international cousin of WORKDAY — projects a date forward (or backward) by N working days using a custom weekend definition.
• When to use it: Calculating due dates in regions whose work week is not Mon–Fri.
• Syntax: =WORKDAY.INTL(start_date, days, [weekend], [holidays])

• Official Docs: WORKDAY.INTL function

• Example (deliver 15 working days after A2 on an Israeli calendar): excel =WORKDAY.INTL(A2, 15, 7, Holidays!$A$2:$A$30)

Part 6: Measuring Elapsed Time

Subtraction tells you how many days are between two dates. The functions in this part let you express that distance in years, months, or fractional years — perfect for age, tenure, and pro-rated calculations.

DATEDIF() — The Hidden Veteran

• What it does: Returns the difference between two dates in a unit of your choice. Famous for being a "hidden" function: it has existed in Excel for decades but doesn't appear in IntelliSense and isn't documented inside the Excel app.
• When to use it: Calculating ages, employee tenure, contract duration in years/months/days, or "X years, Y months, Z days" breakdowns.
• Syntax: =DATEDIF(start_date, end_date, unit)
• Official Docs: DATEDIF function

• Example (employee age in whole years): excel =DATEDIF(BirthDate, TODAY(), "Y")
• Example (tenure as "5 years, 3 months"): excel =DATEDIF(HireDate, TODAY(), "Y") & " years, " & DATEDIF(HireDate, TODAY(), "YM") & " months"

YEARFRAC()

• What it does: Returns the difference between two dates as a fractional year (e.g., 1.75 years).
• When to use it: Pro-rating annual figures (insurance premiums, depreciation, interest), or computing a precise age including the fraction of the current year.
• Syntax: =YEARFRAC(start_date, end_date, [basis])
• Official Docs: YEARFRAC function

The basis argument controls the day-count convention — important in finance, where different markets use different rules for how many days are in a month or year.

• Example (pro-rate a $12,000 annual premium for the actual days served): excel =12000 * YEARFRAC(A2, B2, 1)

EDATE()

• What it does: Returns a date that is N whole months before or after a starting date — preserving the day-of-month (and gracefully handling end-of-month edge cases).
• When to use it: Computing renewal dates, maturity dates, "this date next year," or membership expirations.
• Syntax: =EDATE(start_date, months)

• Official Docs: EDATE function

• Example (subscription expires 12 months after sign-up): excel =EDATE(A2, 12)

• Example (3 months ago): excel =EDATE(TODAY(), -3)