Date formula?

[trekcowboy]

does anyone have a formula for converting numbers to dates.

ex. 03282002 = 03/28/2002

Thanks

 

[Russell Hauf]

However, 2832002 will not work.

In a nutshell, the day (mmddyyyy) or month (ddmmyyyy) must have 2 digits for these to work.

 

[Mark W.]

On 2002-03-28 13:26, Russell Hauf wrote:
However, 2832002 will not work.

In a nutshell, the day (mmddyyyy) or month (ddmmyyyy) must have 2 digits for these to work.

Russell, it's not unreasonable (in fact it's neccessary) to assume that days will always be entered as DD; otherwise, there would be considerable ambiguity for 1122002. Is this 11/2/2002 or 1/12/2002? Gotta have some ground rules! Right?
This message was edited by Mark W. on 2002-03-28 13:42

 

[Russell Hauf]

Exactly. If they aren't entered like that then the solution is not Excel-oriented. But I've seen some interesting stuff at work that would surprise you.

 

[Mark W.]

On 2002-03-28 13:55, Russell Hauf wrote:
Exactly. If they aren't entered like that then the solution is not Excel-oriented. But I've seen some interesting stuff at work that would surprise you.

Surprise me? Not a chance! "Interesting stuff" is quite pervasive. BTW, while sitting in cross-town traffic I realized that my formula...

=TEXT(REPT(0,8-LEN(A10))&A10,"00-00-0000")+0

...can be further simplified to...

=TEXT(A10,"0-00-0000")+0

Excel will coerce the text string into the correct date value with or without the leading zero.

 

[Yogi Anand]

Hi Mark:
That is SUPER! short and sweet ...