excel date field to general numbers

[djones]

Does anyone know of a way to get a column of dates converted automatically, using a formula, into standard numbers but preserving the date numbers - sorry, sounds a tad confusing!

 

e.g. date shown: 21 May 2009 or 21/05/09 etc CONVERTED TO: 210509

 

Thanks,

 

Dave.

[leco]

You could use CONCATENATE

Date is in cell A1

Formula: =CONCATENATE(DAY(A1),MONTH(A1),YEAR(A1))

 

That works but strips out leading zeros - 03/08/09 would become 382009. Might not be what you want.

[maniac]

Does it have to be a formula, as the easiest way is to change the formatting of the cell to custom and type ddmmyy into the type box.

 

I've just used this to generate my new users passwords, as we're using DOB this year for the new Y7's as their initial password in that format.

 

Mike.

[srochford]

Just taking @leco's a bit further, if you need the leading zeros then:

=TEXT(DAY(A1),"00") & TEXT(MONTH(A1),"00") & TEXT(YEAR(A1),"0000")

should do what you need. "&" is quicker to type than "concatenate" but you can also do:

=CONCATENATE(TEXT(DAY(A1),"00"),TEXT(MONTH(A1),"00"),TEXT(YEAR(A1),"0000"))