Date and Time in Excel

Date
Excel represents dates as sequential serial numbers, where January 1, 1900, is represented by the serial number 1. Each subsequent day increments the serial number by 1. This means that January 2, 1900, is represented by the serial number 2, and so on.
- Convert Date to Number (Serial Value)
- Convert Number to Date
Time
Excel represents times as decimal fractions of a day. For example, noon (12:00 PM) is represented by 0.5, because it’s halfway through the day. Similarly, 6:00 AM is represented by 0.25, and 6:00 PM is represented by 0.75. Excel can handle times with precision down to fractions of a second.
- Convert “Date and Time” to Number (Decimal Fraction)
- Convert Number (Decimal Fraction) to Time
Date and Time
When you work with both dates and times together, Excel combines the date serial number and the time fraction to represent a specific date and time. The whole number part represents the date, and the decimal fraction represents the time of day. For example, the 16/August/2023 3:00:00 PM has a numeric value of 45154.625.

Date

Excel (by default) uses the 1900 date system. This means the date 1 Jan 1900 has an underlying numeric value of 1, 2 Jan 1900 has a value of 2, and so forth. These values are called serial values in Excel, and they enable you to use dates in calculations. For any date after January 1, 1900, simply add the number of days to the serial value of January 1, 1900.

January 1, 1900, is serial number 1.
January 2, 1900, is serial number 2.
January 3, 1900, is serial number 3.
January 4, 1900, is serial number 4.
And so on.

Note: Excel’s date system is based on the Windows system, so this method assumes you are using the Windows date system. If you’re using a Mac, Excel might use a different starting date (January 1, 1904, instead of January 1, 1900), which could affect the serial value calculation.

Convert Date to Number (Serial Value)

The TEXT function can be used to format dates as serial values. For example, =TEXT(A1, "0") will display the date in cell A1 as an integer:

You can also use the DATEVALUE function to convert a text representation of a date into a serial number. For example:

=DATEVALUE("January 1, 1900") returns 1.
=DATEVALUE("January 2, 1900") returns 2.
=DATEVALUE("August 15, 2023") returns 45153.

Convert Number to Date

After entering the number, make sure the cell format is set to display the result as a date. Here’s how you can do that:

Select the cell with the number.
Right-click and choose Format Cells or press CTRL+1.
In the Number tab, select the Date category.
Choose the desired date format.

By adjusting the cell format, you can easily convert a serial number back to a human-readable date in Excel.

Time

Times are very similar, but Excel treats times as decimal fractions:

12:00 (noon) has a numeric value of 0.5 because noon is halfway through the day.
18:00 has a numeric value of 0.75 because it is three-quarters of 24 hours.
24:00 or 00:00 has a numeric value of 1.

For example, the date and time 16/August/2023 3:00:00 PM has a numeric value of 45154.625, with the number after the decimal (.625) representing the time, and the 45154 representing the serial value for 16/August/2023.

Convert “Date and Time” to Number (Decimal Fraction)

To see the numeric value of a date and a time, format the cell containing the value(date/time) as General:

Select the cell containing the date and time value.
Right-click and choose Format Cells or press CTRL+1.
Click the General tab, you’ll see the preview of the converted value under the Sample section.
Click OK to apply the formatting.

Convert Number (Decimal Fraction) to Time

You can use the Formatting Cells dialog box to format a decimal fraction as time. Excel stores time as a fraction of a day, where 1 represents a full day, and 0.5 represents half a day (12 hours).

If you have a decimal fraction representing a portion of a day (e.g., 0.25 for 6:00 AM, 0.625 for 3:00 PM, or 0.75 for 6:00 PM), you can format it as a time using custom formatting. Here’s how:

Select the cell with the number.
Right-click and choose Format Cells or press CTRL+1.
In the Number tab, select the Time category.
Choose the desired time format.
Click OK to apply formatting.

Now the decimal fraction will display as a time in the format you’ve selected. If the fraction represents a time that is not on the hour or half-hour mark, the minutes will be displayed accordingly.