Convert to date

Question

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

Thanks!

Answer 1

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.

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.