What are history and time serial numbers in Microsoft Excel and why are they?

If you select cells with a date or time of time in Microsoft Excel and apply the total number format, it creates these random numbers appear. In fact, these numbers are something random – they are consistent serial numbers that serve a certain purpose.

Dates and times are stored as serial numbers in Excel

You can format a cell like a date or a time in Microsoft Excel. In this example, F1 cell is formatted as a date.

Excel recognizes dates written in different ways. For example, the tirates (2024-12-16 or 16 December-2024), forth slashhes (eg, 2024/12/16 or 24/12/16) or commas (for example, 16, 2024), you can enter dates. However, if you use a period to separate the year, months and days, Excel recognizes it as text.

On the other hand, the value in the F2 cell reaps for a while.

You can enter the seconds and minutes allotted by a colon (for example, 10:00) in Excel and enter seconds (eg 10.00:00). Simply add a place and type to enter AM or PM God or Evening after writing time. You can enter the clock entering until hours, AM or PM (eg 10 in the morning).

Costly

Excel’s 12 format options and how it affects your data

Adjust the cell number formats to match the data type.

But under the surface, the cells with dates and times contain consistent consecutive numbers. You can see them formatted in the form of dates and times and press the “General” button in the home sign number group.

Let’s notify how serial numbers for dates and serial numbers for times are decimal. To do this, the time is considered a part of a day and you can be a date and at one time in the same cell. For example, if you write 2024-12-16 10:00:00 In one cell, the serial number represents both full and decimal.

How much history and time serial numbers are created in Excel

Serial numbers for dates in all modern versions of Microsoft Excel are starting on January 1, 1900, each subsequent day.

For example, the serial number for January 31, 1900 is 31, because this is 31 days after the starting point. However, this system includes a serial number for February 29, 1900, although there were 1900 leap years. Thus, after this date, each serial number is one of your expectations, but it is important to note only if you are engaged in dates in the first quarter of 1900.

It also does not recognize dates until January 1, 1900. As a result, there are no serial numbers in the past, so you cannot use it in calculations.

In times, there are a serial number in 12, and this is 1440 minutes a day when it increases 1/1440 per minute. Beləliklə, məsələn, 1 AM üçün seriya nömrəsi 00/1440-a bərabər olan 0.041666666666666666666666667-dir.

How to use history and time serial numbers in Excel calculations

Now you know how serial numbers are working for dates and time on Excel, you are ready to prepare most of these knowledge.

Example 1: To find the difference between two histories

In this example, let us say you want to run the number of days between January 1 and March 31, 2025.

Once serial numbers, every day, you can withdraw each other’s deadline from the beginning date of the beginning, but you can ensure that the cell contains a number, not a number of:

=C2-B2

Example 2: Adding a day to a date

Here your goal is to add until January 1, 2025, 60 days.

To do this, add a cell that is the difference in the days to the start date to make sure the cell is formatted as a date of the cell.

=B2+D2

Example 3: To rest one day from a date

In addition to adding days to a date, you can also use a similar principle to remove days from the day. For example, you may want to see what happened before July 1, 2025, 35 days before date.

B2 Cell Type:

=C2-D2

and format the cell as a date.

Example 4: To find the difference between twice the same day

The first thing to celebrate when finding a difference twice is the lack of time in the same day. The reason for this is before the end of the day before the end of the day, before the next day begins.

For example, if you try to find 7 to 7 in the evening at 7 pm, the serial number is larger than the serial number for the 7th year and Excel may not specify negative times. In other words, the first argument in the total argument must be a later time.

To fix this you need to turn all the information into time in time. See 7 examples for more information.

Instead, we think the difference between the difference between 7:12: 4:45 is the same day.

To do this, D2 cell, type:

=C2-B2

Because the cell D2 is formatted in the form of a while, there is a serial number. In the above example, this means that B2 and C2 have accepted the “AM”.

So select the cell D2 to delete these letters and press Ctrl + 1 to start the format cell dialog box. Then, click the “Time” button in the number tab and select a time format that shows the hours, minutes and seconds. After graduation, press “OK”.

Now you have a clearer representation of the time difference between B2 and C2 cells and the relevant series number confirms the calculation correct.

Example 5: To add hours and minutes at once

In this example, you want to find the time after adding 12.5 hours to 7 pm.

You will see the time difference in the D2 cell is formatted for a while. But you know that this will work as expected, because the serial number is more than half a day, this is what you want to add to the start time.

He said that if you want to be so neat, the format cells can change D2 D2 in the dialog box, so only hours, minutes and seconds are on the screen. See 4 examples for more information about how to do it.

Now in the C2 cell, type:

=B2+D2

For the lately, the serial number is more than 1, because adding from 12.5 hours to 7 is the next day. However, Chel C2 only because it is formatted to show the time, only after returning the part is only after the part.

Example 6: hours and minutes for a while

For example, as in 4, it will work in the same day to remove hours and minutes from one time in the same day. Otherwise, you will see a number of hash characters, because Excel cannot show a negative time. In this case, such as 7, you must enter the values ​​worth the date in the date of time values.

Let’s imagine you will like to take five hours, 30 minutes and 9 to 9 to 9 to 9 seconds.

To do this, B2 cell, type:

=C2-D2

Example 7: Conducting calculations with historical values

In the last example, your goal is to use the night difference at 7:00 pm on 71 pm on December 12, 2023 and 9.

Apply to the D2 first in the first or numbered format. Then, in the same cell, type:

=C2-B2

Excel can manage this calculation, because the serial number for value in C2 cells is larger than a serial number for value in B2 cell.

Now it can be divided into days and hours to make this information clearer. First, E2 cell, type:

=INT(D2)

round the decumbating number up to the nearest number. This gives the number of all days.

Finally, the type in the F2 cell:

=SUM(MOD(D2,1)*24)

to take out the decimal and convert a single digit.

Costly

How to use Excel’s Mod function to solve real-world problems

Mod is more versatile than you can think of.

Along with the use of history and time serial numbers to perform this type of calculations, Microsoft Excel offers various time and date functions, including DATEdif and network days that can help you get history and time.