Site icon ExcelNotes

How to Convert a Serial Number into a Date

Text FunctionData Formatting

Dates are stored as serial numbers in Excel and they can be switched from one format to another. If the dates in the file display as serial numbers, you can format them into any date formats; or you can use the Text Function to convert them to the date format.

In Excel, dates can display in many different formats, such as "5/8/2018", "May 8, 2018", "2018-5-8", or "20180508" etc.

1. To convert a date into "MMMM DD, YYYY" format, please use the formula and change A2 to the cells with dates in your file. For example, serial number "43228" returns May 8, 2018.

=Text(A2,"MMMM DD, YYYY")

2. To convert a date into "YYYYMMDD" format, please use the formula and change A2 to the cells with dates in your file. For example, serial number "43228" returns 20180508.

=Text(A2,"YYYYMMDD")

3. To convert a date into "YYYY-MM-DD" format, please use the formula and change A2 to the cells with dates in your file. For example, serial number "43228" returns 2018-05-08.

=Text(A2,"YYYY-MM-DD")

4. To convert a date into "MM/DD/YYYY" format, please use the formula and change A2 to the cells with dates in your file. For example, serial number "43228" returns 05/08/2018.

=Text(A2,"MM/DD/YYYY")

Alternatively, please follow the steps below to format the serial numbers into the dates.

Step 1: Select the date range and click the "Home" tab from the ribbon;

Step 2: Click the right bottom corner in the "Number" group;

Step 3: In the "Format Cell" window, click "Date" and select one of the formats;

Step 4: Click "OK".

Exit mobile version