10.1 Current Date, Time, & Day-of-Week Functions: NOW, TODAY, & WEEKDAY

Current Date, Time, & Day-of-Week Functions: NOW, TODAY, & WEEKDAY

Enterprise financial schedules, project management baselines, supply chain pipelines, and regulatory reporting calendars depend heavily on accurate temporal calculations. In Microsoft Excel, date and time arithmetic is governed by an internal serial numbering engine. Rather than treating calendar entries as arbitrary text strings, Excel converts every date and time into a precise numerical coordinate along a continuous timeline. Mastering that serial architecture — and the three functions MO-211 names for anchoring and classifying a single date, NOW, TODAY, and WEEKDAY — is the foundation for every business date formula that follows.

---

The 1900 Date Serial System & Fractional Time Architecture

By default, Excel for Windows and Excel for Microsoft 365 utilize the 1900 Date System. Under this system:

• Whole Days as Integers: Day 1 corresponds to January 1, 1900. Every subsequent calendar day increments this integer by exactly 1. For example, July 4, 2026 corresponds to the serial number 46207.
• Time as Fractional Days: Time is stored as a decimal portion of a 24-hour day. Because one full day equals 1.0, individual hours, minutes, and seconds are represented as fractions:
  • 1 Hour = $1 / 24 \approx 0.041667$
  • 6:00 AM = $6 / 24 = 0.25$
  • 12:00 PM (Noon) = $12 / 24 = 0.50$
  • 6:00 PM = $18 / 24 = 0.75$
  • 8 Hours (Standard Shift) = $8 / 24 = 1/3 \approx 0.333333$

A combined date-time timestamp such as July 4, 2026, 12:00 PM is stored internally as the floating-point number 46207.50. Adding 1 advances the timestamp by 24 hours, whereas adding 0.5 advances it by 12 hours.

[!NOTE] Historical Legacy Bug: Excel intentionally replicates a legacy Lotus 1-2-3 bug that treats the year 1900 as a leap year (recognizing February 29, 1900 as day 60), even though 1900 was not a leap year in the Gregorian calendar. This does not impact calculations for dates after March 1, 1900.

---

Volatile Current-Date Functions: NOW() & TODAY()

Every rolling report — aging schedules, days-past-due columns, tenure calculators, countdown dashboards — needs an anchor to "right now." Excel supplies two zero-argument functions for this, and MO-211 expects you to know precisely how they differ.

=TODAY()   // Current date only. Fractional (time) component is exactly 0.
=NOW()     // Current date AND time, stored as a fractional serial.

Volatility: What It Actually Means

Both functions are volatile. A volatile function recalculates on every worksheet recalculation event — opening the file, editing any cell, pressing F9, inserting a row, or sorting — not on a live ticking clock. NOW() therefore does not advance second by second while you watch it; it refreshes only when Excel next recalculates.

Two consequences the exam likes to probe:

• Phantom "unsaved changes": A workbook containing TODAY() or NOW() recalculates on open, marking the file dirty. Excel then prompts you to save on close even though you typed nothing.
• Performance drag: Thousands of volatile cells force full-chain recalculation in large models. Compute the anchor once in a single helper cell and reference that cell everywhere else.

Volatile Function vs. Static Timestamp

When a value must be frozen — a signed approval date, an audit stamp — a formula is the wrong tool:

Practical Patterns

=TODAY() - B2                  // Days an invoice has been outstanding
=NOW() - TODAY()               // Fractional time of day; format as [h]:mm
=EDATE(TODAY(), 3)             // Same calendar day, three months out
=IF(TODAY() > C2, "Overdue", "Current")

[!WARNING] NOW() and TODAY() return raw serials. In a cell formatted General, =NOW() displays 46207.5 rather than a timestamp. Apply a date or date-time number format before submitting an exam task.

---

Day-of-Week Classification: WEEKDAY()

WORKDAY moves a date across business days; WEEKDAY classifies a single date by its position within the week. It returns an integer, and the numbering scheme is controlled entirely by the optional second argument.

=WEEKDAY(serial_number, [return_type])

Return-Type Numbering Schemes

Thursday, October 15, 2026 evaluated three ways:

=WEEKDAY(DATE(2026,10,15))      // 5  (type 1: Sun=1, so Thursday is 5)
=WEEKDAY(DATE(2026,10,15), 2)   // 4  (type 2: Mon=1, so Thursday is 4)
=WEEKDAY(DATE(2026,10,15), 3)   // 3  (type 3: Mon=0, so Thursday is 3)

The Weekend Test — and Its Classic Trap

The standard weekend flag relies on return_type 2, which pushes Saturday to 6 and Sunday to 7 so a single >5 comparison captures both:

=IF(WEEKDAY(A2, 2) > 5, "Weekend", "Weekday")

Writing =WEEKDAY(A2) > 5 with the default type 1 is a silent failure: Sunday scores 1, so only Saturday (7) exceeds 5 and every Sunday is misclassified as a business day. The same rule powers a conditional formatting rule that shades weekend rows across A2:F200 (rule formula =WEEKDAY($A2, 2) > 5, applied with column $A locked and the row relative), which is exactly how the two domains intersect on exam tasks.

WEEKDAY vs. WORKDAY: Choosing Correctly