How to display the week number and year code in Excel

Sometimes we need to aggregate data by week and the Excel function - WEEKNUM works like a charm. But there are situations where we have data referring to diferent years and in these situations we have to associate the week number to the respective year.

For these situations we can combine WEEKUM and TEXT Excel functions. The formula would be as follows:

=TEXT(A1,"yy")&TEXT(WEEKNUM(A1),"00")

How to display the week number and year code, step by step:

1. Enter 01-01-2014 on cell A1 (date from which will be retrieved the week number and year code)
2. Enter the above formula in cell B2
3. Press Enter
4. B2 should now contain the value  - 1401 - the first two digits "14" refers to year 2014, and  the last two "01" refer to week number of date 01-01-2014

Examples:

Now you can play arround with the formula to retrieve year code and week number in the sequence you need.

Retrieve the week number followed by the year code:

=TEXT(WEEKNUM(B3),"00")&TEXT(B3,"yy")

Retrieve the week number and year code with a "-" in the between:

=TEXT(WEEKNUM(B3),"00")&"-"&TEXT(B3,"yy")