Converting between date types: "20101012"

Saldo · Oct 27th 2010

Hi,
I have a spreadsheet with dates in format YearMonthDay (e.g. 20101027).
Is there any straightforward way to convert them in Excel DATE format, via an Excel function or VBA?

Thanks!
Aldo

Rob Xaos · Oct 27th 2010

Re: Converting between date types: "20101012"

Test that they are actual DateTime values by widening the column, and remove any alignment on that column. If they are DateTime values then they should be right aligned (as a number).

If they are then you can simply use Format > Cells > Date and pick the format you require.

AAE · Oct 27th 2010

Re: Converting between date types: "20101012"

If the "number" is not a true date, then you have text numbers and need to convert the string into a valid date.

Assuming you always have yyyymmdd, then : =DATE(LEFT(A1,4)+0,MID(A1,5,2)+0,RIGHT(A1,2)+0)

Saldo · Oct 27th 2010

Re: Converting between date types: "20101012"

That's brilliant, thank you very much both!

Aldo

Converting between date types: "20101012"

Participate now!

Share