luvv to helpDiscover the Best Free Online Tools
Topic 11 of 14

Date Functions

Learn Date Functions for free with explanations, exercises, and a quick test (for Data Analyst).

Published: December 20, 2025 | Updated: December 20, 2025

Table of Contents

Why this matters

Data Analysts compare time periods, calculate SLAs, build cohort charts, and align metrics to weeks and months. Strong date function skills let you:

  • Create month-over-month and year-over-year KPIs.
  • Calculate shipping/response SLAs using business days.
  • Build cohorts by signup month or week.
  • Group charts by calendar boundaries (week start, month end).
  • Clean messy date text into real, calculable dates.
Real tasks you will face
  • Compute invoice aging buckets (0–30, 31–60, 61–90, 90+ days).
  • Align transactions to the fiscal month start and end.
  • Find the last Friday of a month for reporting cutoffs.
  • Flag weekend orders and adjust to next business day.

Concept explained simply

In spreadsheets, a date is a number where the integer part counts days from a base date, and the decimal part is time. For example, 0.5 means noon of the base day. Because dates are numbers, you can add, subtract, and compare them.

  • Dates: whole days.
  • Times: fractions of a day (e.g., 6 hours = 6/24 = 0.25).
  • Date-times: integer + fraction (e.g., 45123.75).

Common functions (Excel and Google Sheets):

  • TODAY(), NOW() — current date/time (volatile).
  • DATE(y, m, d) — construct a date.
  • YEAR(), MONTH(), DAY(), HOUR(), MINUTE(), SECOND() — parts.
  • EDATE(date, months) — add/subtract months safely.
  • EOMONTH(date, months) — end of month offset.
  • WEEKDAY(date, [type]) — day of week.
  • WEEKNUM() / ISOWEEKNUM() — week numbers.
  • NETWORKDAYS(start, end, [holidays]) and NETWORKDAYS.INTL() — business days.
  • WORKDAY(start, days, [holidays]) and WORKDAY.INTL() — next workday.
  • DATEDIF(start, end, unit) — full years/months/days between.
  • TEXT(date, pattern) — format to text without changing the value.
  • DATEVALUE(text) and TIMEVALUE(text) — convert text to true values.
Mental model

Think in three steps:

  1. Offsets: Add days, months, or business days to move along the timeline (e.g., EDATE, WORKDAY).
  2. Boundaries: Snap to period starts/ends (e.g., first day of month with DATE(YEAR(d), MONTH(d), 1); last day with EOMONTH(d,0)).
  3. Labels: Convert dates into clear labels for grouping (e.g., TEXT(d, "yyyy-mm").
Locale and system notes
  • Parsing: 03/04/2025 may be March 4 or April 3. Prefer DATE(YEAR, MONTH, DAY) parts or unambiguous ISO-like text (yyyy-mm-dd).
  • Date systems: Excel can use 1900 or 1904 systems. Keep sheets consistent when copying.
  • Time zones: Spreadsheets do not manage time zones automatically. Convert timestamps before import or store as UTC plus offset.

Worked examples

1) Days between two dates

Order in A2, Ship in B2. Days to ship:

=B2 - A2

If you see a date instead of a number, change cell format to Number. For business days with holidays in H2:H10:

=NETWORKDAYS(A2, B2, $H$2:$H$10)
2) Start and end of month

Date in A2. First day of month:

=DATE(YEAR(A2), MONTH(A2), 1)

Last day of month:

=EOMONTH(A2, 0)
3) Add months safely

Add 6 months to A2:

=EDATE(A2, 6)

Subtract 1 month:

=EDATE(A2, -1)

Why not +30? Months vary in length; EDATE handles rollovers.

4) ISO week number and week start

ISO week number:

=ISOWEEKNUM(A2)

Week start (Monday) for A2:

=A2 - WEEKDAY(A2, 2) + 1
5) Cohort label (yyyy-mm)

Label for grouping:

=TEXT(DATE(YEAR(A2), MONTH(A2), 1), "yyyy-mm")

Hands-on exercises

Do these inside your spreadsheet. The exercises below are also listed in the Exercises section with full solutions.

Exercise 1 — Shipping SLA

  • [ ] Create Days_to_Ship = Ship_Date - Order_Date.
  • [ ] Create BizDays_to_Ship using NETWORKDAYS with a holiday list.
  • [ ] Flag SLA_Breached if BizDays_to_Ship > 3.
  • [ ] Count percent breached by month of order.
Tips
  • Use DATE(YEAR(d), MONTH(d), 1) to find month start.
  • Use TEXT(d, "yyyy-mm") for grouping labels.

Exercise 2 — Signup cohorts

  • [ ] Create Signup_Month = first day of signup month.
  • [ ] Months_Since_Signup = completed months between signup and today.
  • [ ] Cohort_Label = TEXT(Signup_Month, "yyyy-mm").
  • [ ] Pivot: count users by Cohort_Label.
Tips
  • Use DATEDIF(start, end, "M") for completed months.
  • Watch that your dates are real dates (format to Number to check).

Common mistakes and self-check

  • Text dates that look real: "2025-03-01" may be text. Self-check: change cell format to Number. If it doesn’t change, convert with DATEVALUE or split to parts and use DATE().
  • Adding 30 for a month: Use EDATE for month arithmetic; days vary by month.
  • Wrong weekday type: WEEKDAY defaults vary. Use type 2 for Monday=1 if you need ISO-style weeks.
  • Ignoring time portion: 2025-01-10 23:00 minus 2025-01-10 01:00 is 0.9167 days, not 0. Use INT() to drop time or use DATE() on the same cell to normalize to midnight.
  • Business day calculations without holidays: NETWORKDAYS without holidays overstates capacity. Always pass a holiday range.
  • Mixed date systems (Excel 1900/1904): If numbers are off by ~1462 days, align the workbook date system and recheck.
Self-check routine
  1. Select a sample of 5 date cells, change format to Number, then back to Date.
  2. Verify month arithmetic with EDATE by testing end-of-month dates (e.g., Jan 31 + 1 month).
  3. Confirm week start logic by testing a known Monday and Sunday.

Mini challenge

You receive a table of payments with columns: Payment_ID, Customer_ID, Invoice_Date, Payment_Date, Amount.

  • [ ] Compute Days_to_Pay and BizDays_to_Pay (with a holiday list).
  • [ ] Create Invoice_Month label (yyyy-mm).
  • [ ] Create Aging_Bucket at time of a snapshot date (e.g., TODAY()): 0–30, 31–60, 61–90, 90+ using FLOOR(Days/30) logic.
  • [ ] Build a pivot: sum Amount by Invoice_Month and Aging_Bucket.
Hints
  • For Aging relative to a snapshot: =TODAY() - Invoice_Date.
  • Buckets: =CHOOSE(1+MIN(3, FLOOR((Days)/30, 1)), "0-30", "31-60", "61-90", "90+")

Who this is for

  • Early-career Data Analysts who build recurring reports and dashboards.
  • Anyone cleaning or grouping time-based data in spreadsheets.

Prerequisites

  • Basic spreadsheet navigation and formulas.
  • Cell formatting (Number vs Date vs Text).

Practical projects

  • Sales funnel by week: compute Monday-start week labels and chart conversions.
  • Customer support SLA: response time in business days with weekend/holiday rules.
  • Churn cohorts: label signups by month and calculate retention after 1, 3, 6 months.

Learning path

  • After Date Functions: Text parsing (LEFT, MID, RIGHT, SPLIT), cleaning imports.
  • Then: LOOKUPs/joins (VLOOKUP/XLOOKUP/INDEX-MATCH) to enrich date-stamped tables.
  • Then: Pivot tables and charts with time grouping.

Next steps

  • [ ] Rebuild one of your reports using DATE, EDATE, EOMONTH, and NETWORKDAYS.
  • [ ] Create a reusable sheet with holiday list and week/month helper columns.
  • [ ] Take the quick test below. The test is available to everyone; only logged-in users get saved progress.

Practice Exercises

2 exercises to complete

Instructions

You have a table Orders(A: Order_ID, B: Order_Date, C: Ship_Date). Holidays are in H2:H20.

  1. Create D: Days_to_Ship = C2 - B2. Format as Number.
  2. Create E: BizDays_to_Ship using NETWORKDAYS with $H$2:$H$20 as holidays.
  3. Create F: SLA_Breached = TRUE if BizDays_to_Ship > 3 else FALSE.
  4. Create G: Order_Month = TEXT(DATE(YEAR(B2), MONTH(B2), 1), "yyyy-mm").
  5. Build a pivot: rows = Order_Month, values = % Breached (average of SLA_Breached).
Expected Output
Columns D, E, F, G filled. A pivot that shows breach rate by month (e.g., 12.5%, 8.0%, ...).

Date Functions — Quick Test

Test your knowledge with 10 questions. Pass with 70% or higher.

10 questions70% to pass
Text FunctionsArray Formulas Basics

Have questions about Date Functions?

AI Assistant

Ask questions about this tool