Convert to date

Question

Hi!

How i can convert number 20020415 to date 15.04.2002. I am working in Microsoft Excel 2003?

Thanks!

Answer 1

Let cell A1 contains the text 20020415.

=CONCATENATE(RIGHT(A1, 2), ".", MID(A1,5,2), ".", LEFT(A1, 4))

will produce 15.04.2002.

Afterward, if you want to store the value and not the formula you can copy and paste the value only.

Note: this method still keeps the date as text.

Answer 2

If A1 contains a number instead of text, you can use this:

=DATE(INT(A1/10000),INT((A1-10000*INT(A1/10000))/100),A1-(100*INT(A1/100)))

This can then be formatted using Excel formatting options.

Answer 3

If your numbers are always in the same format (i.e. yyyymmdd) then use Excel's Date function to convert your number to a date:

For example, assuming date 20020415 is in cell A1, then in cell B1 use:

=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

This will make sure your number is of a date format which will make it easier to use in the future if you want to treat it like a date.

Then, using Format > Cells, select 'Date' and select the appropriate formatting option (in your case 15.04.2002).

As an aside, you can access the 'Format Cells' dialog box using the shortcut keys CTRL+1. I find this a highly useful shortcut to know.