Can’t perform any date formatting on a cell because Excel doesn’t recognise its contents as an ‘actual’ date? Using the DATE() formula can help you convert a text field to a date field!

The DATE() Function

If you type in DATE() into Excel you will notice that it accepts three different arguments:

  • DATE(year, : The Year value
  • DATE(year,month : The Month value
  • DATE(year,month,day) : The Day value

Example

Lets say you have a column filled with timestamps but Excel recognises their format as Text or as General.

Step 1 - Text as Date

All my Time’s in this column are set to 00:00:00 so they are not important when converting to a formatted Date.

I will now add three new columns next to the Date column labelled, Year, Month and Day.

Step 2 - 3 New Columns

In these columns I am going to split the date part of the Date column.

Here is how you can split this column in the three different parts of a date:

Year

As the Year is on the left hand side and is 4 characters long I can use the LEFT() function:

= LEFT(text, [num_of_chars])
= LEFT(A2,4)

Enter this formula in cell B2.

After hitting enter you can see that the year part of the Timestamp is in the Year column.

Month

As the month is in the middle of the date part of the Timestamp we will need use a different formula, MID(). The month part starts at character 6 (include the first character of the month as the starting point) and is only 2 characters long.

= MID(text, start_num, num_chars)
= MID(A2,6,2)

Enter this in cell C2.

Step 6

After hitting enter you can now see that the month part of the Timestamp is in the Month column.

Step 7

Day

The last part that we need to convert the Timestamp to a date is the Day part, this is also in the middle of the Timestamp so we will use the MID() formula again. The date starts at the 9th character and is 2 characters long.

= MID(text, start_num, num_chars)
= MID(A2,9,2)

Enter this in cell D2.

Step 8

After hitting enter you can now see that the day part of the Timestamp is in the Day column.

Step 9

Now it’s time to use the DATE() formula!
Label column E, Date and then in the cell below the heading (E2) enter in the date formula in this format:

=DATE(year, month, date)
=DATE(B2,C2,D2)

Step 9

Hit enter, you should now see the date part of the Timestamp cell in it’s own cell! You can also check if Excel has recognised it as a Date.

Step 10

You can also combine all these formulas into one large formula:

= DATE( LEFT(A2,4), MID(A2,6,2), MID(A2,9,2) )

The MID() Function

The MID() can be a bit confusing so here is a clear explanation of how it works:

mid

MID(text, start_num, num_chars):

  • Firstly, it looks at the cell value or reference.
  • Secondly, it looks at the starting position of the string you want to extract, in the example above we are trying to extract 09, so the starting position of 09 is 6.
  • Lastly, you will need to specify the length of the string you want to extract, in the example above it is 2 characters long (09).

You can also use the MID() function to get the start or the end of a cell value.

Also remember that Excel recognises spaces as characters.

If you have any different examples you would like help with please email me (about page) or leave your suggestions or problems via a comment!

You can download this example as an Excel Workbook here

Enjoy!