The unit of time in Excel is one day. Excel stores a date and time together as a number: the integer part identifies the date in the workbook's date system, and the fractional part represents the time within that day. A cell's number format decides how much of that value you see.
That explains why changing a date-time cell to General can reveal a number such as 40364.3003472222. You haven't lost the date or time. You're looking at the value Excel uses in calculations.
Combine a date and time in one cell
You can follow along in the date-and-time example workbook. It includes the Overview calculation, a Serial Display comparison, and the Elapsed Time example below.
Let's keep the original article's example: July 5, 2010, at 7:12:30 AM. On a sheet named Overview, enter these headings and formulas:
| Cell | Heading in row 1 | Formula in row 2 | Expected result |
|---|---|---|---|
| B2 | Date | =DATE(2010,7,5) | July 5, 2010 |
| C2 | Time | =TIME(7,12,30) | 7:12:30 AM |
| D2 | Date and time | =B2+C2 | July 5, 2010, 7:12:30 AM |
| E2 | Seconds since midnight | =C2*86400 | 25950 |
The DATE function takes the year, month, and day in that order. The TIME function takes hours, minutes, and seconds. Using these formulas avoids the ambiguity of typing a date such as 7/5/2010, whose interpretation depends on regional settings.
In D2, add the date to the time:
=B2+C2Apply a Date format to B2, a Time format that includes seconds to C2, and a date-time format to D2. For example, the custom format mmm d, yyyy h:mm:ss AM/PM shows both parts of D2. Keep E2 formatted as General or Number.
You can also build the same date-time directly:
=DATE(2010,7,5)+TIME(7,12,30)Change the display to see the serial number
To compare the displays without changing row 2, enter =B2 in B3, =C2 in C3, and =D2 in D3. Select B3:D3 and apply General from the number-format list.
With the workbook using the 1900 date system, the underlying values are:
| Cell | Meaning | Numeric value |
|---|---|---|
| B3 | Date | 40364 |
| C3 | Time | Approximately 0.3003472222 |
| D3 | Date and time | Approximately 40364.3003472222 |
Excel may show fewer decimal places depending on column width. D2 and D3 contain the same numeric value; their formats make them look different. Even if you format D2 to show only the date, its time fraction remains available to formulas.
For a current example, =NOW() returns the current date-time serial. It updates when Excel recalculates, rather than acting as a continuously ticking clock, so use the fixed example above when you want repeatable results.
Why 7:12:30 AM is a fraction of a day
A day has 24 hours, or 86,400 seconds. That makes noon 0.5 of a day and 6:00 AM 0.25. Midnight at the start of a day is 0; a complete day's duration is 1.
For 7:12:30 AM, count the seconds since midnight:
- 7 hours × 60 minutes × 60 seconds = 25,200 seconds.
- 12 minutes × 60 seconds = 720 seconds.
- Add the remaining 30 seconds: 25,200 + 720 + 30 = 25,950.
- Divide by 86,400: 25,950 / 86,400 = 0.300347222….
That's the value in C2. The formula in E2 reverses the calculation:
=C2*86400The result is 25,950 seconds. Formatting C2 as Time makes the fraction readable as a clock time; it doesn't change the fraction used in the calculation. Microsoft's TIME function documentation describes this numeric representation.
Subtract two date-times to find a duration
Now use a separate sheet named Elapsed Time, so the first example stays intact. Suppose an activity starts at 6:00 AM on January 20, 2022, and ends at 2:00 PM on January 21.
| Cell | Heading in row 1 | Formula in row 2 |
|---|---|---|
| A2 | Start | =DATE(2022,1,20)+TIME(6,0,0) |
| B2 | End | =DATE(2022,1,21)+TIME(14,0,0) |
| C2 | Elapsed days | =B2-A2 |
| D2 | Decimal hours | =(B2-A2)*24 |
| E2 | Duration | =B2-A2 |
Format A2 and B2 to show both date and time. With C2 formatted as General or Number, subtracting the start from the end returns approximately 1.333333333 days:
=B2-A2Multiply that difference by 24 to get 32 decimal hours in D2:
=(B2-A2)*24E2 uses the same subtraction as C2. Give E2 the custom number format [h]:mm and it displays 32:00. The brackets keep the hours counting past 24. Apply that format to the difference in days, not to D2's decimal-hours result. Microsoft uses this elapsed-hours approach in its guide to adding and subtracting time.
For shifts, overnight entries, and other duration problems, continue with calculating hours between two dates and times.
When a date or time looks wrong
A date appears as a decimal. Check the number format first. In the Overview example, 40364.300347222… and July 5, 2010, at 7:12:30 AM are two displays of the same value in the 1900 system.
A time-only subtraction is negative. A start time of 10:00 PM and an end time of 6:00 AM contain no dates to tell Excel that the end belongs to tomorrow. Use complete date-times when the dates are known, as in the Elapsed Time example.
Changing the format doesn't fix an imported date. Text that looks like a date isn't necessarily a numeric Excel date. Applying a date format doesn't convert text into a number. Check the source value before relying on date arithmetic.
Which date system is the workbook using?
The serial 40364 represents July 5, 2010, in Excel's 1900 date system. A workbook can also use the 1904 date system; this isn't a simple Windows-versus-Mac distinction. Microsoft's date-system explanation describes both systems.
Treat the serial as Excel's date-system value, rather than trying to reconstruct it by counting ordinary calendar days from January 1, 1900. The 1900 system includes a historical leap-year compatibility quirk.
If dates differ between workbooks, see Excel's 1900 and 1904 date systems for the 1,462-day difference and guidance on settings and conversion. Don't change the system just to make a displayed serial match this example.