Quick Links

• Dates and Times Are Stored as Serial Numbers in Excel

• How Date and Time Serial Numbers Are Generated in Excel

• How to Use Date and Time Serial Numbers in Excel Calculations

Summary - Dates and times are stored as serial numbers in Microsoft Excel, enabling swift calculations and comparisons.

• Serial numbers for dates begin on January 1, 1900, while times are decimal representations of a 24-hour period.
• Grasping Excel's date and time serial numbers aids in computing differences between dates, adding days, modifying times, and more.

In Microsoft Excel, selecting cells with dates or times and applying the General number format creates what seem like arbitrary numbers. In reality, these numbers are systematic serial numbers with a specific function.

Dates and Times Are Stored as Serial Numbers in Excel

In Microsoft Excel, you can configure a cell as a date or a time. In this instance, cell F1 is set as a date.

Excel acknowledges dates entered in diverse ways. For instance, you can input dates with hyphens (like 2024-12-16 or 16-Dec-2024), forward slashes (like 2024/12/16 or 24/12/16), or spaces and commas (like Dec 16, 2024). However, if you use a period to separate the year, month, and day, Excel interprets this as text.

Conversely, the value in cell F2 is formatted as a time.

You can enter times in Excel as hours and minutes divided by a colon (like 10:00), and you can also include the seconds (like 10:00:00) if desired. To include AM or PM, simply add a space and type AM or PM after typing the time. For hour-only times, you can enter the hour followed by AM or PM (like 10 AM).

Excel's 12 Number Format Options and How They Affect Your Data Adjust your cells' number formats to align with their data type.

PostsNevertheless, beneath the surface, cells with dates and times hold unseen sequential serial numbers. You can view these by choosing the cells formatted as dates and times, and clicking "General" in the Number group of the Home tab on the ribbon.

Observe how the serial numbers for dates are whole numbers, and the serial numbers for times are decimals. This is because time is viewed as a fraction of a day, and you can have a date and a time together in the same cell. For instance, if you type 2024-12-16 10:00:00 into 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 current versions of Microsoft Excel begin at 1 for January 1, 1900, increasing with each subsequent day.

For example, the serial number for January 31, 1900, is 31, as this is 31 days after the starting point. However, this system includes a serial number for February 29, 1900, even though 1900 wasn't a leap year. Thus, each serial number after this date is one off from what you would anticipate, but this is only relevant if you're handling dates in the first quarter of the year 1900.

Additionally, Excel does not recognize dates prior to January 1, 1900. Consequently, earlier dates lack serial numbers, so they cannot be used in calculations.

How to Use Date and Time Serial Numbers in Excel Calculations Having learned how serial numbers operate for dates and times in Excel, you're prepared to maximize this knowledge in calculations.

Example 1: Determining the Gap Between Two Dates

For this example, let's assume you wish to calculate the number of days between January 1 and March 31 in 2025.

Given that date serial numbers increase by one daily, in cell D2, you can merely subtract the end date from the start date, ensuring the cell holding the outcome is formatted as a number, not a date:

<code>=C2-B2</code>

Example 2: Adding Days to a Date Here, your goal is to add 60 days to January 1, 2025.

To achieve this, in cell C2, add the cell containing the difference in days to the start date, making sure the cell is formatted as a date.

<code>=B2 D2</code>

Example 3: Subtracting Days from a Date

Similar to adding days, you can also use a comparable method to deduct days from a date. For instance, you may want to determine the date 35 days prior to July 1, 2025.

In cell B2, type:

<code>=C2-D2</code>

and format the cell as a date.

Example 4: Finding the Difference Between Two Times on the Same Day

The initial consideration when determining the difference between two times is that the times must fall on the same day. This is because time serial numbers progress from the start of the day to the end of the day, then restart the next day.

For instance, if you attempt to calculate the difference between 7 PM on one day and 7 AM on the next day, the serial number for 7 PM is greater than the serial number for 7 AM, and Excel cannot show negative times. In essence, the first argument in the sum must be a later time than the second argument.

To resolve this, you'll need to convert all the data to time-date formats. Refer to example 7 for further details.

Instead, let's say you want to compute the time difference between 7:12:45 AM and 10:14:57 AM on the same day.

To do this, in cell D2, type:

<code>=C2-B2</code>

Since cell D2 is formatted as a time, it has a serial number. In the above example, this means it has taken the "AM" from cells B2 and C2.

Thus, to eliminate these letters, select cell D2, and press Ctrl 1 to open the Format Cells dialog box. Then, in the Number tab, click "Time," and pick a time format that shows only the hours, minutes, and seconds. Afterward, click "OK."

Now, you have a clearer depiction of the time difference between cells B2 and C2????, and the corresponding serial number verifies that the calculation is accurate.

Example 5: Adding Hours and Minutes to a Time

In this scenario, you aim to find out the time after adding 12.5 hours to 7 PM.

![](/uploads/20250531/

The above is the detailed content of What Are Date and Time Serial Numbers in Microsoft Excel, and Why Do They Exist?. For more information, please follow other related articles on the PHP Chinese website!