Lesson 4: Excel stores dates as serial numbers
Dates are numbers formatted to look like calendar dates; times are fractions of a day. Subtracting two dates gives elapsed days. Use DATE to build dates from numeric year, month, and day values instead of joining date text.
If A2 is 2025-01-15 and B2 is 2025-02-01, =B2-A2 returns 17 days. Format the result as Number.
Date and time formula reference
23 formulas shown
| Function | Purpose | Example |
|---|---|---|
| DATE | Builds a date from year, month, and day; normalizes overflow. | =DATE(2025,2,1) returns 1-Feb-2025. |
| TODAY | Returns the current date; updates on recalculation. | =TODAY(). |
| NOW | Returns current date and time. | =NOW(). |
| YEAR / MONTH / DAY | Extracts a component from a date. | =MONTH(DATE(2025,2,1)) returns 2. |
| HOUR / MINUTE / SECOND | Extracts a time component. | =HOUR(TIME(14,35,20)) returns 14. |
| DATEVALUE | Converts recognized date text to a serial number. | =DATEVALUE("1-Feb-2025"). |
| TIME | Builds a time from hour, minute, second. | =TIME(14,30,0) returns 2:30 PM. |
| TIMEVALUE | Converts recognized time text to a serial fraction. | =TIMEVALUE("2:30 PM"). |
| DAYS | Returns end date minus start date in days. | =DAYS(B2,A2). |
| DATEDIF | Returns completed years, months, or days between dates. | =DATEDIF(A2,B2,"m") returns complete months. Use a valid ordered date pair. |
| DAYS360 | Calculates days using a 360-day financial year. | =DAYS360(A2,B2). |
| YEARFRAC | Returns the fraction of a year between dates. | =YEARFRAC(A2,B2,1) uses actual days/actual year basis. |
| EDATE | Moves a date by whole months. | =EDATE(A2,3) is three months later. |
| EOMONTH | Returns the last day of a month offset. | =EOMONTH(A2,0) returns the current month's end. |
| WORKDAY | Moves by workdays, excluding weekends and optional holidays. | =WORKDAY(A2,10,H2:H10). |
| NETWORKDAYS | Counts workdays between dates. | =NETWORKDAYS(A2,B2,H2:H10). |
| WORKDAY.INTL | Schedules workdays with a custom weekend pattern. | =WORKDAY.INTL(A2,10,1,H2:H10). |
| NETWORKDAYS.INTL | Counts workdays with a custom weekend pattern. | =NETWORKDAYS.INTL(A2,B2,1,H2:H10). |
| WEEKDAY | Returns weekday number using a selected numbering system. | =WEEKDAY(A2,2) returns Monday=1 through Sunday=7. |
| WEEKNUM | Returns a week number using a selected week system. | =WEEKNUM(A2,2) starts weeks on Monday. |
| ISOWEEKNUM | Returns ISO-8601 week number. | =ISOWEEKNUM(A2). |
| HOUR / MINUTE | Extracts clock components for elapsed-time reports. | =MINUTE(TIME(14,35,0)) returns 35. |
| TIME | Adds a fractional-day time to a date. | =A2+TIME(8,30,0) adds 8:30 AM. |
Find a due date 10 workdays later: =WORKDAY(A2,10,$H$2:$H$10)
Dates that work across locales
Store true date values, not strings such as "01/02/25", which can mean different dates in different regions. Use number formats for display, and pass an explicit weekend code to the .INTL functions when Saturday/Sunday is not the weekend.
Progress stays in this browser.