MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Formatting Date Codes in the Form YYWW


Posted by Joe Barocio on February 06, 2002 4:03 PM

I need to convert past and future dates to date codes with the year and week, where 2/1/2002 would convert to 0205 and 2/8/2002 would convert to 0206 etc.

Using Datevalue, and Text functions I can get to 025 and 026 but I need that extra 0 on weeks below 10 so the codes sort correctly.

It seems there ougth to be a more elegant approach than a long datevalue, text, and if-then formula.


Posted by Mark W. on February 06, 2002 4:19 PM

=TEXT(ref,"YY")&TEXT(WEEKNUM(ref),"00") [NT]

Posted by Joe Barocio on February 06, 2002 4:41 PM

Re: =TEXT(ref,"YY")&TEXT(WEEKNUM(ref),"00") [NT]

Now, that's an elegant solution. Thanks a million.