Excel treats date and time as numbers which are then formatted into the Date and Time formats. This gives you the opportunity to convert numbers to dates. That means that you can also perform some mathematical operations on them.
Format cells window
The first way to create a date from a number is to use the Format Cells feature. It will convert a number to date without using formulas.
Look at the following example.
It consists of several numbers. Let’s copy them to column B.
Select cells from B2 to B5 and use the Ctrl + 1 keyboard shortcut.
From Date, you can choose one of the different data formats. This will give us the following result.
Convert number to date with formula
The TEXT function converts a value to text in a specified format. For example, the formula will return the following text string.
|
1 |
=TEXT(A2, “dd-mm-yy”) |
16-02-64
We can create a date from text using the DATE function.
|
1 |
=DATE(year, month, year) |
You can join both functions to create the following formula.
|
1 |
=DATE(TEXT(A2,"yy"),TEXT(A2,"mm"),TEXT(A2,"dd")) |
This formula gives us the same result as the cell formatting.