Excel 2013 issue with text formatting

Hi, I'm running into an issue with Excel 2013.

I'm using the follwing formula.
=TEXT(CONCATENATE(INT((A$3-DATE(YEAR(A$3-WEEKDAY(A$3-1)+4),1,3)+WEEKDAY(DATE(YEAR(A$3-WEEKDAY(A$3-1)+4),1,3))+5)/7),"-",A1),"mm-jjjj")

In Excel 2010 this works perfectly. However when I open the file in 2013 it will display as 41-JJJJ. JJJJ should print the year. Changing this to YYYY doesn't work. If I open the file in 2010 after opening it in 2013 it will give the same problem in 2010. This can be fixed by selecting the formula and hitting enter.
In 2013 I can't get it to work at all. It seems 2013 uses the regional setting differently. I'm using 2013 in a citrix enverinment, i'm not sure what it uses as regional setting.

Does anyone have a workaround that works in all versions of excel?

As you are concatenating some values, that will result in a text value - you need to convert that into a numeric value in order for the TEXT function to work. Can you explain what is in the various cells used in the formula (A1, A3) and what the CONCATENATE part actually produces?

Hope this helps.

Pete

Row A1 contains the year (2012). A3 contains the date (8-okt-2012). It should produce a weeknumber 41-2012.

It functions fine in excel 2010. It's only in Excel 2013 where it doesn't work.

Repeating the excercise to change jjjj to yyyy, seems to work this time.

I don't think you need that TEXT function, then - try it without it, like this:

=CONCATENATE(INT((A$3-DATE(YEAR(A$3-WEEKDAY(A$3-1)+4),1,3)+WEEKDAY(DATE(YEAR(A$3-WEEKDAY(A$3-1)+4),1,3))+5)/7),"-",A1)

Hope this helps.

Pete

Thanks!

That doesn't work for me though because I need it to print 01-2013 instead of 1-2013. That's why i added the text() function.

In that case you can do this:

=TEXT(INT((A$3-DATE(YEAR(A$3-WEEKDAY(A$3-1)+4),1,3)+WEEKDAY(DATE(YEAR(A$3-WEEKDAY(A$3-1)+4),1,3))+5)/7),"00")&"-"&A1

Hope this helps.

Pete

Thanks! That works even better