TEXT function result differs between users

[mandersten]

Hi,

I have a relatively simple formula to consolidate a date with a text string. It works on my machine with MS Office 2007 and UK Regional settings.
Cell A1 contains a date formatted as: 2013-03-25
Cell A2 contains a text string: 1,5 weeks
Cell A3 contains a formula: ="End date is: "&TEXT(A1+(LEFT(A2;2)*7);"yyyy-mm-dd")&" (incl. "&A2&")"
Displayed result in A3: End date is: 2013-04-01 (incl. 1,5 weeks)

My colleague who has MS Office 2003 and Swedish Regional settings get the following result in A3:
End date is: yyyy-00-01 (incl. 1,5 weeks)

Any suggestions as to what could cause this?

Thanks!

[Special-K]

Post deleted - pls ignore.

[mandersten]

Does anyone have a solution to this problem, or at least an explanation as to why it doesn't work? / Thanks

[popipipo]

It is a language conflict
May be this file will help you

[daddylonglegs]

This version is longer but should work with most regional settings

="End date is: "&YEAR(A1+LEFT(A2;2)*7)&"-"&TEXT(MONTH(A1+LEFT(A2;2)*7);"00")&"-"&TEXT(DAY(A1+LEFT(A2;2)*7);"00")&" (incl. "&A2&")"

Note: by using LEFT(A2;2) you only take 1, from 1,5 thereby adding only one week not one and a half - not sure that's your intention

[JosephP]

if it is only one cell that causes the issue you could define a name called FormatA1 which refers to:
=GET.CELL(7,'sheet_name'!$A$1)
and change your formula to
="End date is: "&TEXT(A1+(LEFT(A2;2)*7);FormatA1)&" (incl. "&A2&")"

[mandersten]

Thank you! That did the trick (even though I must admit I don't fully understand why).

[mandersten]

To daddylonglegs: Thanks for making me aware of this mistake!