Quick wins for a faster PC:
Scan for outdated or missing drivers - takes under a minuteDriver Scan →Clear out junk files and repair common Windows errorsFree Scan →Excel dates and times are numeric values: dates use serial numbers, while times are fractions of a day. That lets you add and subtract them, and cell formatting controls how they appear. Use the function map and troubleshooting guidance below when 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 performs date and time arithmetic on underlying numbers rather than on the characters shown in a cell. A date is represented by a serial number, and a time is represented by a fraction of a 24-hour day. Formatting changes the display without changing that numeric value.
In a Microsoft Q&A example, the text 6-14 is interpreted as June 1, 2014, with serial value 41,791. That illustrates why ambiguous entries can be misread: Excel interprets input according to recognized date conventions and regional settings. For dates you enter or share, use an unambiguous four-digit year and ensure the workbook’s locale matches the data.
Microsoft Q&A: Excel date format confusion
Choose a function by the job
This map groups common Excel date and time functions by what you need to do. Whether a result remains numeric or becomes text depends on the function; for calculations, retain numeric date and time values and use formatting for presentation.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
| Task | Functions | What they do |
|---|---|---|
| Build or break apart dates | DATE, DAY, MONTH, YEAR, DATEVALUE |
Construct a date, extract its components, or convert a date represented as text into a date value. |
| Build or break apart times | TIME, HOUR, MINUTE, SECOND, TIMEVALUE |
Construct a time, extract its components, or convert a time represented as text into a time value. |
| Measure intervals | DAYS, DATEDIF, YEARFRAC |
Calculate a span between dates, with the chosen function determining the form of the interval. |
| Shift dates by calendar rules | EDATE, EOMONTH |
Move a date by months or return a month-end date. |
| Count or advance through workdays | NETWORKDAYS, NETWORKDAYS.INTL, WORKDAY, WORKDAY.INTL |
Count workdays or calculate a date a number of workdays away, with the .INTL variants supporting alternative weekend patterns. Holiday dates can be supplied where supported by the function. |
| 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. |
Microsoft’s reference lists Excel date and time functions and their syntax: Date and time functions reference.
Display dates, times, and elapsed durations
For a numeric value, change the cell’s number format when you want a different display but still need to calculate with it. Date format codes use M, D, and Y; time codes use H, M, and S. Because m can represent either month or minute, use it in a time context such as h:mm when you mean minutes.
Rank #2
- Used Book in Good Condition
Use TEXT only when you need text
TEXT(value, format_text) returns a text string formatted as requested, rather than a numeric date or time for subsequent arithmetic. Microsoft’s examples include =TEXT(TODAY(),"MM/DD/YY") and =TEXT(NOW(),"H:MM AM/PM"). To include a formatted date in a text string, an example is =A2&" "&TEXT(B2,"mm/dd/yy"). Keep the original numeric value in its own cell if later formulas need to calculate with it.
Microsoft explains the function and its format codes in TEXT function and format a date.
Show elapsed hours beyond one day
Clock-time formats normally display time within a 24-hour cycle. For a duration total that can exceed 24 hours, use an elapsed-time format such as [h]:mm. The brackets tell Excel to keep counting hours rather than reset the display at 24.
See Microsoft’s guidance on adding or subtracting time.
Quick Recap
Best Value
- The Microsoft Office 365 Bible: The Most Updated and Complete Guide to Excel, Word, PowerPoint, Outlook, OneNote, OneDrive, Teams, Access, and Publisher from Beginners to Advanced
- ABIS BOOK
Troubleshoot common date and time problems
- A serial number appears instead of a date: The value may be formatted as General or as a number. Apply an appropriate date format; the underlying value can remain numeric.
- A value that looks like a date is read incorrectly: Input such as
6-14is ambiguous and may be parsed as a date. Check the cell’s actual value and the workbook’s regional conventions. For time ranges, keep start and end times in separate cells rather than entering a compact string that Excel might interpret differently. - A formatted result no longer works in arithmetic: If you used
TEXT, the result is text. Refer to the original numeric date or time for calculations. - A total wraps at 24 hours: The cell is displaying a clock time, not an elapsed duration. Use a bracketed format such as
[h]:mmfor accumulated hours. - A date calculation or sort behaves unexpectedly: Check that the cells contain date values rather than text that merely resembles dates, and verify that entries were interpreted using the intended locale.
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.
