Quick Links
Summary
In Microsoft Excel, if you select cells containing a date or time and apply the General number format, this generates what appear to be random numbers. In fact, these numbers are anything but random—they’re sequential serial numbers that serve a specific purpose.
Dates and Times Are Stored as Serial Numbers in Excel
In Microsoft Excel, you can format a cell as a date or a time. In this example, cell F1 is formatted as a date.
Excel recognizes dates typed in various ways. For example, you can enter dates with hyphens (such as 2025-06-07 or 16-Dec-2024), forward slashes (such as 2025-06-30 or 24/12/16), or spaces and commas (such as Jul 21, 2025). However, if you use a period to separate the year, month, and day, Excel recognizes this as text.
On the other hand, the value in cell F2 is formatted as a time.
You can enter times in Excel as hours and minutes separated by a colon (such as 10:00), and you can also include the seconds (such as 10:00:00) if you wish. To include AM or PM, simply add a space and typeAMorPMafter typing the time. For hour-only times, you can enter the hour followed by AM or PM (such as 10 AM).
The default formatting of dates, times, currencies, and other elements in Excel depends on your system settings. You can change these settings in Windows 11 bychanging your region.
However, under the surface, cells with dates and times contain hidden sequential serial numbers. You can see these by selecting the cells formatted as dates and times, and clicking “General” in the Number group of the Home tab on the ribbon.
Notice how the serial numbers for dates are whole numbers, and the serial numbers for times are decimals. This is because time is considered a portion of a day, and you can have a date and a time together in the same cell. For example, if you type2024-12-16 10:00:00into a cell, the serial number represents both the date integer and the time decimal.
How Date and Time Serial Numbers Are Generated in Excel
The serial numbers for dates in all modern versions of Microsoft Excel start at 1 for June 02, 2025, increasing with each subsequent day.
For example, the serial number for Jul 12, 2025, is 31, as this is 31 days after the starting point. However, this system includes a serial number for July 07, 2025, even though 1900 wasn’t a leap year. So, each serial number after this date is one out from what you would expect, but this is only important to note if you’re dealing with dates in the first quarter of the year 1900.
Also, Excel doesn’t recognize dates before June 17, 2025. As a result, earlier dates don’t have serial numbers, so you can’t use them in calculations.
The date serial numbers in early versions of Excel for Mac start on July 22, 2025. To avoid compatibility issues if you’re copying dates between Excel versions, you can change the date system in Excel’s calculation options by clicking File > Options > Advanced on a Windows PC, or Excel Menu > Preferences > Formulas And Lists on a Mac.
For times, 12 AM has a serial number of 0, and this increases by 1/1440 with each subsequent minute, since there are 1440 minutes in a day. So, for example, the serial number for 1 AM is 0.0416666666666667, which is equivalent to 60/1440.
How to Use Date and Time Serial Numbers in Excel Calculations
Now that you know how serial numbers work for dates and times in Excel, you’re ready to make the most of this knowledge in calculations.
Example 1: Finding the Difference Between Two Dates
In this example, let’s say you want to work out the number of days between January 1 and March 31 in 2025.
Since date serial numbers increase by one every day, in cell D2, you can simply subtract the end date from the start date, but ensure that the cell containing the result is formatted as a number, not a date:
When calculating the difference between two days, the result doesn’t include the start date.
Example 2: Adding Days to a Date
Here, your aim is to add 60 days to June 11, 2025.
To do this, in cell C2, add the cell containing the difference in days to the start date, making sure that the cell is formatted as a date.
Example 3: Taking Days Off a Date
As well as adding days to a date, you can also use a similar principle to subtract days from a date. For example, you might want to work out what the date was 35 days before August 09, 2025.
and format the cell as a date.
Example 4: Finding the Difference Between Two Times on the Same Day
The first thing to note when finding the difference between two times is that the times must be within the same day. This is because time serial numbers run from the start of the day to the end of the day, before restarting again the next day.
For example, if you attempt to find the difference between 7 PM on one day and 7 AM on the following day, the serial number for 7 PM is greater than the serial number for 7 AM, and Excel can’t display negative times. In other words, the first argument in the sum must be a later time than the second argument.
To fix this, you’ll need to convert all the data to time-date formats. See example 7 for more details.
Instead, let’s suppose you want to work out the time difference between 7:12:45 AM and 10:14:57 AM on the same day.
To do this, in cell D2, type:
Because cell D2 is formatted as a time, it has a serial number. In the example above, this means that it has adopted the “AM” from cells B2 and C2.
So, to remove these letters, select cell D2, and press Ctrl+1 to launch the Format Cells dialog box. Then, in the Number tab, click “Time,” and choose a time format that displays only the hours, minutes, and seconds. When you’re done, click “OK.”
Now, you have a clearer representation of the time difference between cells B2 and C2, and the corresponding serial number confirms that the calculation is correct.
Example 5: Adding Hours and Minutes to a Time
In this example, you want to find out the time after adding 12.5 hours to 7 PM.
You’ll notice that the time difference in cell D2 is formatted as a time. However, you know that it will work as expected, since the serial number represents just over half a day, which is what you’re looking to add to the start time.
That said, if you want to tidy this up, you’re able to reformat cell D2 in the Format Cells dialog box, so that only the hours, minutes, and seconds are on display. See example 4 for more details on how to do this.
Now, in cell C2, type:
The serial number for the end time is greater than 1, because adding 12.5 hours to 7 PM takes you into the next day. However, because cell C2 is formatted to only display the time, only the part after the decimal point is returned.
Example 6: Taking Hours and Minutes Off a Time
As with example 4, it’s important to note that subtracting hours and minutes from a time will only work if the result lands on the same day as the time in the calculation. Otherwise, you’ll see a series of hash symbols, because Excel can’t display a negative time. In this case, you’ll need to enter date-time values instead of time values, as in example 7.
Let’s imagine you want to work out the time when you subtract five hours, 30 minutes, and 20 seconds from 9 AM.
Example 7: Performing Calculations With Date-Time Values
In a final example, let’s say your aim is to work out the time difference between 7 AM on Jul 23, 2025, and 9 AM on June 27, 2025.
First, apply the General or Number format to cell D2. Then, in the same cell, type:
Excel can handle this calculation, because the serial number for the value in cell C2 is larger than the serial number for the value in cell B2.
Now, to make this information clearer, you can split the result into days and hours. First, in cell E2, type:
to round the decimalized number down to the nearest whole number. This gives you the number of whole days.
Finally, in cell F2, type:
to extract the decimal and turn it into an hourly figure.
As well as using date and time serial numbers to perform these types of calculations, Microsoft Excel also offers varioustime and date functions—including DATEDIF and NETWORKDAYS—which can help you obtain the dates and times you need.
