Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
All articles Convert serial number to date Convert serial number to date – Excelchat

Convert serial number to date – Excelchat

Excel has a special way of storing dates by assigning serial numbers.  The serial number “1” refers to January 1, 1900, and serial dates increment by 1 for every day thereafter.  

Calculations involving dates are possible because Excel treats dates as numbers.  When the result we obtain is in number form, we can convert the serial number into date by following these methods:  

  • Change format in Format Cells
  • Use TEXT function

Figure 1.  Final result: Convert serial number to date

Using Format Cells to convert serial number to date

As an example, the serial number for January 1, 2019 is 43466.  To convert the number 43466 into date, we follow these steps:

  • Select the cell containing the serial number
  • Right click and select Format Cells

Figure 2.  Format Cells option in menu

  • In the Format Cells dialog box, click the Number tab and select the Date category
  • Choose the preferred date format in Type.  The Sample box above it will show a preview of the date using the selected type.  

Figure 3.  Format Cells dialog box

As a result, the serial number will be converted to a date with the specific format “dd/mm/yyyy”.  

Figure 4.  Output: Using Format Cells to convert serial number to date

Using formula to convert serial number to date

We can also the TEXT function in a formula to convert a serial number to date.  

Syntax of TEXT

=TEXT(value,format_text)

  • format_text is the format code enclosed in quotation marks.  

Example 1:

In cell C3, enter the formula =TEXT(B3,"mm/dd/yyyy") and press Enter.

Figure 5.  Output: Entering the formula using TEXT

The serial number 43466 is instantly converted into date.  Note, the resulting date is now in text format. We can also specify a different date format such as in below example.  

Example 2:

In cell D4, enter the formula  =TEXT(B3,"mmmm dd, yyyy") and press Enter.

Figure 6.  Output: Using formula to convert serial number to date

Instant Connection to an Excel Expert

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc