Dates and Times — AI Calculates Tenure, Payment Terms, and Deadlines

Chapter 6: Date & Time Functions

Date functions are the most underestimated category in Excel. Attendance tracking, payment terms, contract expiry alerts, project scheduling — nearly every operational workflow depends on them. This chapter covers the underlying logic of how Excel stores dates, dives deep into DATEDIF, WORKDAY, and EDATE, and walks through 15 real-world cases with complete AI prompt workflows.

Workplace Scenarios for Date Functions

Date functions appear in virtually every professional context:

• Attendance management: Calculate working days per month, excluding weekends and public holidays. Compare expected vs. actual attendance days.
• Payment terms: 45-day payment terms from invoice date — when exactly does it fall due? How many days remain? Flag anything under 30 days.
• Contract expiry alerts: Dozens of contracts with different end dates. Which ones expire within 30 days? Which have already lapsed?
• Project scheduling: Project kicks off March 1st, needs 60 working days — what's the finish date after factoring in holidays?
• Seniority calculation: Hire date July 15, 2018 — what's the employee's tenure in years and months today? This directly impacts leave entitlements and pay grades.

How Excel Stores Dates

Excel dates are serial numbers. January 1, 1900 = 1. Each subsequent day adds 1. So January 1, 2024 = 45292. This means you can do arithmetic directly on dates — subtracting two dates gives the number of days between them.

ℹ️ Note

Why this matters: When you see a number appear where you expected a date, it's not a bug — the underlying value is correct, but the cell format needs to be set to "Date." The value and the display are separate things.

Quick Examples

Dynamic report title: Display "Data as of: January 15, 2024" that updates automatically.

="Data as of: "&TEXT(TODAY(),"mmmm d, yyyy")

Calculate age: A2 contains birth date.

=DATEDIF(A2,TODAY(),"Y")

Current month header:

=TEXT(TODAY(),"mmmm yyyy")&" Sales Report"

DATEDIF — Excel's Secret Hidden Function

DATEDIF exists in Excel and works perfectly — but it's absent from the formula autocomplete list and from official documentation. It was inherited from Lotus 1-2-3, retained for compatibility, but never formally promoted due to edge-case bugs. It remains one of the most useful date functions for HR and finance work.

Syntax:

=DATEDIF(start_date, end_date, unit)

⚠️ Warning

Known bug: The "MD" unit can return incorrect results when the start date falls near month-end. For precision-critical work, ask AI to provide an alternative formula that avoids this edge case.

Case 1: Employee Tenure (Years + Months) A2 holds hire date. Display tenure as "3 years 9 months" in B2.

=DATEDIF(A2,TODAY(),"Y")&" years "&DATEDIF(A2,TODAY(),"YM")&" months"

Case 2: Contract Days Remaining C2 is the contract expiry date. Show days remaining in D2, or "Expired" if past.

=IF(C2


  Case 3: Payment Due Date Alert (with Conditional Formatting)
  E column has invoice due dates. Show days remaining in F column. Apply red formatting for under 30 days, yellow for 30-60 days.

=E2-TODAY()

Conditional formatting rules (select entire data range A2:F100): Red — formula: =$F2=30,$F2

Workday Calculations: WORKDAY / NETWORKDAYS / WORKDAY.INTL

• WORKDAY(start, days, [holidays]): Date N working days from start

• NETWORKDAYS(start, end, [holidays]): Count working days between two dates

• WORKDAY.INTL(start, days, [weekend], [holidays]): Custom weekend definition (6-day work weeks, shift schedules)

  Case 1: Project Deadline (N Working Days Out) A2 is project start date. Calculate deadline after 45 working days.

=WORKDAY(A2,45)

Case 2: Working Days Between Two Dates C2 is contract start, D2 is delivery deadline. How many working days available?

=NETWORKDAYS(C2,D2)

Case 3: Excluding Custom Company Holidays Company has a 4-day shutdown (Dec 28-31). These are stored in H2:H5. Project starts E2 and needs 30 working days.

=WORKDAY(E2,30,H2:H5)

Case 4: Expected Attendance Days per Month Calculate monthly expected working days, excluding weekends and all public holidays stored in Sheet2 column A.

=NETWORKDAYS(DATE(YEAR(TODAY()),MONTH(TODAY()),1),EOMONTH(TODAY(),0),Sheet2!A:A)

Case 5: Using AI to Generate a Holiday List You need a complete list of public holidays for your country in NETWORKDAYS-ready format.

Generate a complete list of public holidays for [country] in [year], one date per line in YYYY-MM-DD format, ready to paste into Excel column A for use as a NETWORKDAYS holiday parameter. Include only actual non-working days (exclude compensatory working days on weekends).

Date Format Conversion: TEXT + Dates

TEXT converts a date serial number to a formatted string. Common format codes:

• "yyyy-mm-dd" → 2024-01-15

• "mmmm d, yyyy" → January 15, 2024

• "dddd" → Monday

• "ddd" → Mon

• "m/d/yyyy" → 1/15/2024 (US format)

  Case 1: Convert 8-Digit Text Date to Real Date System export gives dates as "20240115" text strings. Convert to proper Excel dates.

=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))

Case 2: Extract Day of Week Scheduling table with dates in column A. Show day of week in column B.

EDATE / EOMONTH: Month-End and Month-Offset Dates

• EDATE(date, months): Same day N months forward or back

• EOMONTH(date, months): Last day of month N months away

  Case 1: Contract Renewal Date Contract expires in A2. Renewal date is 1 month after expiry, same day.

  Case 2: Quarter-End Date Calculate the last day of the current quarter dynamically.

=EOMONTH(TODAY(),MOD(3-MOD(MONTH(TODAY()),3),3))

Case 3: Generate Full-Year Month-End List Finance needs a column of all 12 month-end dates for a given year.

Comprehensive Case: Automated Attendance Tracker

Building a complete attendance system using date functions and AI-assisted prompt design.

Date Function Quick Reference

Previous ← Chapter 5: Text Functions Next Chapter 7: Math & Statistics Functions →