Excel stores dates as serial numbers counting days from January 1, 1900. May 21, 2026 is the integer 46163. Times are stored as fractions of a day: 12:00 noon is 0.5, and 6:00 PM is 0.75. A combined date and time like "2026-05-21 18:00" becomes 46163.75. Once you understand this, every date function makes sense as arithmetic on numbers.
Because dates are numbers, the difference between two dates is simple subtraction, and adding days is plain addition. Excel does carry one historical quirk: it incorrectly treats 1900 as a leap year, an artifact preserved for compatibility with Lotus 1-2-3. This affects only dates between 1900-01-01 and 1900-02-28, which virtually nobody encounters in real spreadsheets.
DATE(year, month, day) constructs a date from three integers. DATE(2026, 5, 21) returns the serial number for May 21, 2026. Excel automatically normalizes out-of-range values, so DATE(2026, 13, 1) becomes January 1, 2027 and DATE(2026, 0, 31) becomes January 31, 2026. This is invaluable for month boundary calculations and rolling-window reports.
TIME(hour, minute, second) returns a value between 0 and 1 representing a time of day. Combine it with DATE for a full timestamp. For string inputs, DATEVALUE and TIMEVALUE parse text such as "2026/05/21" or "18:30:00" into serial numbers, useful when importing data from external systems without going through Power Query.
TODAY() returns the current date and NOW() returns the current date and time. Both are volatile functions that recalculate whenever the workbook recalculates, so opening the file or editing any cell can update them. They are useful for live dashboards but unsuitable when you need to capture a fixed moment in time.
To record the moment a row was created, use the keyboard shortcut Ctrl + ; (insert today's date) or Ctrl + Shift + ; (insert current time). Both insert literal values that do not change later. With VBA or Office Scripts, you can also use Worksheet_Change events to write Now into a sibling cell when a particular column is edited, providing automatic audit trails. Volatile functions everywhere also slow down recalculation, so reserve them for cells that genuinely need real-time freshness.
NETWORKDAYS(start, end, [holidays]) returns the count of weekdays between two dates, excluding Saturdays and Sundays. NETWORKDAYS("2026-05-04", "2026-05-08") returns 5. The optional third argument accepts a range of cells containing holiday dates that are also excluded. Maintaining a holiday list elsewhere in the workbook lets the formula handle company-specific or country-specific calendars cleanly.
For non-standard work weeks, NETWORKDAYS.INTL(start, end, weekend, [holidays]) accepts a weekend code from 1 (Saturday and Sunday) to 17 (Tuesday only) or a custom 7-character string like "0000110" to mark weekend days explicitly. This handles Middle Eastern weeks (Friday and Saturday off), single-day weekend countries, and rotating shift schedules. Combined with SUMPRODUCT, you can compute monthly working hours across complex shift patterns.
EOMONTH(date, months) returns the last day of the month offset by the specified count. EOMONTH(TODAY(), 0) gives the end of this month, EOMONTH(TODAY(), -1) the end of last month, and EOMONTH(TODAY(), 1) the end of next month. This is essential for closing periods, monthly reports, and recurring billing dates that always fall on month-end.
EDATE(date, months) shifts the same day of the month forward or backward. EDATE on January 31, 2026 with offset 1 returns February 28, 2026 because February has no day 31. This safe rollover is exactly what mortgage payment schedules, insurance renewals, and subscription billing need. For year offsets, just multiply: EDATE(date, 12) goes one year forward.
WEEKNUM(date, [type]) returns the week number within the year. The second argument selects the start-of-week convention: 1 for Sunday, 2 for Monday. The catch is that WEEKNUM defines week 1 as the week containing January 1, while the ISO 8601 standard defines week 1 as the week containing January 4 (so it must contain at least four days of the new year).
The discrepancy is most visible at year boundaries. January 1, 2026 (Thursday) is week 1 by WEEKNUM but ISO week 53 of 2025. International project reporting often uses ISO weeks, so cross-team weekly summaries can show different totals depending on whether each side uses WEEKNUM or ISOWEEKNUM. Pick a single convention for your sheet, document it explicitly at the top, and you avoid hours of confusion later.
Was this article helpful?