Recommended Free Tools
Excel date and time calculations work because dates are stored as serial numbers and times as fractions of a day. That lets you add and subtract them, while cell formatting controls how the results look. This reference groups common functions by task and explains how to avoid familiar problems: a formula returns a strange number instead of a date, a calculation comes out a day off, or a column refuses to sort as expected.
How Excel stores dates and times
Excel represents a date as a serial number and a time as part of one day. A date-time value combines both: the whole-number portion represents the date and the fractional portion represents the time. Arithmetic works on those numeric values; formatting changes their display, not the underlying value.
For example, a Microsoft Q&A answer gives June 1, 2014 as serial number 41,791. The same answer notes that text such as 6-14 can be interpreted as a date rather than a time range, depending on how Excel parses the input. [Microsoft Q&A]
Recognition and display depend on regional settings. For data intended to be unambiguous, enter a four-digit year and be mindful of the workbook’s locale. If you need to calculate a shift or interval, use separate start and end time values rather than relying on an ambiguous text string.
Windows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallCrashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minuteChoose a function by the job
The function families below cover constructing values, extracting components, measuring intervals, moving dates by calendar rules, scheduling around workdays, and retrieving current or week-based values.
| Task | Functions | Typical result or use |
|---|---|---|
| Build or split a date | DATE, DAY, MONTH, YEAR, DATEVALUE |
Construct a date from year, month, and day; extract a date component; or convert a date represented as text to a date value. |
| Build or split a time | TIME, HOUR, MINUTE, SECOND, TIMEVALUE |
Construct a time from its components, extract a component, or convert time text to a time value. |
| Measure intervals | DAYS, DATEDIF, YEARFRAC |
Calculate a difference in days, a date interval, or a fraction of a year, respectively. |
| Move dates by calendar rules | EDATE, EOMONTH |
Return a date a number of months before or after a starting date, or the last day of a month offset from it. |
| Count or advance through workdays | NETWORKDAYS, NETWORKDAYS.INTL, WORKDAY, WORKDAY.INTL |
Count workdays between dates or return a date offset by a number of workdays; the .INTL variants allow alternative weekend rules. |
| Get current values or week information | TODAY, NOW, WEEKDAY, WEEKNUM, ISOWEEKNUM |
Return the current date or date and time, or derive weekday and week-number information. |
For workday calculations that need holidays, provide the holiday dates as part of the function’s inputs. For week numbers, choose the function and its options to match the calendar convention you need; a week-number result is not interchangeable with an ISO week number.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Format dates, clock times, and durations
To change how a numeric value appears, format the cell rather than converting the value to text. Date formats use m, d, and y; time formats use h, m, and s. Because m can mean month or minute, place minutes in a time context such as h:mm.
A clock display normally wraps after 24 hours. For elapsed totals that can exceed a day, use a bracketed hour format such as [h]:mm. Microsoft explains that square brackets around h tell Excel not to reset the hour count every 24 hours. [Microsoft: TEXT function]
Use TEXT only when the output must be text
Microsoft documents the syntax as TEXT(value, format_text). For example, =TEXT(TODAY(),"MM/DD/YY") produces a formatted date string, and =TEXT(NOW(),"H:MM AM/PM") produces a formatted time string. To join a date to text while controlling how the date appears, use a pattern such as =A2&" "&TEXT(B2,"mm/dd/yy"). [Microsoft: TEXT function]
TEXT returns text, not a numeric date or time. Microsoft cautions that this can make the result harder to reference in later calculations. Keep the original numeric value for arithmetic, and use TEXT for display inside a text string.
Quick Recap
Best Value
- Used Book in Good Condition
Fix common date and time problems
- A serial number appears instead of a date: the cell may be using General or a numeric format. Apply a date format to display the serial as a date.
- A value such as
6-14is read as a date: the entry is ambiguous and may be parsed according to regional settings. Store start and end times separately for calculations, and use explicit date or time values rather than shorthand text. - A calculated date looks a day off: check the source values and their formats, and confirm that the inputs are numeric dates rather than text that Excel interpreted differently than intended.
- A formatted result no longer behaves like a date: check whether the formula uses
TEXT. Its output is text; retain a numeric source value for date arithmetic. - An hours total resets after 24 hours: change the duration format to
[h]:mminstead of a clock-time format. - Dates sort unexpectedly: a column may contain a mixture of actual numeric dates and text strings. Normalize the inputs as date values before sorting.
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
