convert date to number

[mzalikhan]

Hi all

I have dates in excel 2007 as 4/14/2007 (formatted as date) i want to change it to number as 20070414 (in number format).

In other words i have date as 4/14/2007. If i only change the format of the cell to "number" it would give me "39186". That is what I am NOT looking for. What i need is 20070414.

Please let me know how to do that?

Regards

 

[VoG]

Perhaps

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

 

[Kyle123]

Maybe
=VALUE(TEXT(A1,"YYYYMMDD"))

 

[mzalikhan]

Thank you Kyle123 and VoG. Both formulas are working fine

 

[VoG]

You are welcome. Kyle's is better than mine.

 

[Kyle123]

WoooHooo that's a first Thanks Peter , I'm normally rubbish at formuas

It's only because that's how I'd do it in code

val(format(Now(),"YYYYMMDD"))