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. The off-by-one surfaces in day counts that cross the end of February 1900: the phantom February 29 leaves every serial number from March 1, 1900 onward one too high, while a span that stays inside January 1 to February 28 comes out correct. Virtually nobody encounters either case in real spreadsheets.
| Displayed value | Serial number | Breakdown |
|---|---|---|
| 2026-05-21 0:00 | 46163 | Integer part only |
| 2026-05-21 12:00 | 46163.5 | 46163 + 0.5 |
| 2026-05-21 18:00 | 46163.75 | 46163 + 0.75 |
| 18:30 (time only) | 0.770833... | Return value of TIME(18, 30, 0) |
The fraction is the share of one 24-hour day: 18:30 works out to 18.5 / 24 = 0.770833..., which is why TIME always returns a value of at least 0 and less than 1. Because the date and the time live in the integer and fractional halves of a single number, INT strips the time off a timestamp and MOD(value, 1) keeps only the time.
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. A month below 1 counts backward from January, which makes DATE(2026, 0, 31) December 31, 2025. 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: 1 through 7 cover two-day weekends (1 is Saturday and Sunday), while 11 through 17 cover single-day weekends, running from 11 for Sunday only up to 17 for Saturday only. A custom 7-character string like "0000110" marks weekend days explicitly. This handles countries that treat Friday and Saturday as the weekend, 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, 2027 (Friday) is week 1 of 2027 by WEEKNUM, but ISOWEEKNUM returns 53: because ISO week 1 is the week containing January 4, that date still belongs to week 53 of ISO year 2026. 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?