How to Turn a Number into a Date on Excel

Excel stores dates as serial numbers that represent the number of days since January 1, 1900. For example, the date January 1, 2021 is stored as the number 44197, because it is 44,197 days after January 1, 1900. Similarly, the date December 31, 2020 is stored as the number 44196, because it is one day less than January 1, 2021.

To turn a number into a date on Excel, we need to apply a date format to the cell that contains the number. This will tell Excel to display the number as a date, according to the chosen format. For example, if we apply the format mm/dd/yyyy to the cell that contains the number 44197, Excel will display it as 01/01/2021.

Procedures

To turn a number into a date on Excel, follow these steps:

1. Select the cell or range of cells that contain the numbers you want to convert to dates.
2. Right-click on the selected cells and choose Format Cells from the context menu. Alternatively, you can press Ctrl + 1 on your keyboard to open the Format Cells dialog box.
3. In the Format Cells dialog box, click on the Number tab and select Date from the Category list.
4. Choose the date format you want from the Type list. You can also customize the date format by using the symbols in the table below.

5. Click OK to apply the date format to the selected cells.

Example

Let’s say we have a list of numbers in column A that represent the dates of some events. We want to turn these numbers into dates using the format mm/dd/yyyy. Here is how the worksheet looks like before and after applying the date format:

Other Approaches

Besides using the Format Cells dialog box, there are other ways to turn a number into a date on Excel. Here are some of them:

• Use the DATE function. The DATE function takes three arguments: year, month, and day, and returns a serial number that represents the date. For example, =DATE(2021,1,1) returns 44197, which is the serial number for January 1, 2021. To display the serial number as a date, you need to apply a date format to the cell that contains the formula.
• Use the TEXT function. The TEXT function takes two arguments: a value and a format code, and returns a text string that represents the value in the specified format. For example, =TEXT(44197,"mm/dd/yyyy") returns 01/01/2021, which is a text string that represents the number 44197 in the date format mm/dd/yyyy. Note that the result of the TEXT function is not a date, but a text. This means that you cannot perform date calculations or apply date filters on the result.
• Use the DATEVALUE function. The DATEVALUE function takes one argument: a text string that represents a date, and returns a serial number that represents the date. For example, =DATEVALUE("01/01/2021") returns 44197, which is the serial number for January 1, 2021. To display the serial number as a date, you need to apply a date format to the cell that contains the formula.