TEXT "ddd" giving #N/A on some machines

[_Adrian]

Hi,

My first post here, hope someone can help.

I have created an Excel 2010 spreadsheet that is to be used in variou
European countries (I'm in the UK). One of the pilot testers in Austri
(but otherwise using a UK laptop with UK settings) is experiencing th
following issue whereas all other testers under similar condition
report no fault. I can't explain it...

I have a calculated date based on an input year
A1=DATE(Current_Year,1,1)
Adjacent to this I have B1=TEXT(A1,"ddd") in order to display Mon or Tu
or Wed etc

This works fine in the version I've sent and recalculates fine. However
change the Current_Year and the result of the TEXT function becomes dd
(and not Mon etc)!

Can anyone explain why, and better still, how to fix this!?

Thanks,

Adria

 

[Ron Rosenfeld]

Hi,

My first post here, hope someone can help.

I have created an Excel 2010 spreadsheet that is to be used in various
European countries (I'm in the UK). One of the pilot testers in Austria
(but otherwise using a UK laptop with UK settings) is experiencing the
following issue whereas all other testers under similar conditions
report no fault. I can't explain it...

I have a calculated date based on an input year:
A1=DATE(Current_Year,1,1)
Adjacent to this I have B1=TEXT(A1,"ddd") in order to display Mon or Tue
or Wed etc

This works fine in the version I've sent and recalculates fine. However,
change the Current_Year and the result of the TEXT function becomes ddd
(and not Mon etc)!

Can anyone explain why, and better still, how to fix this!?

Thanks,

Adrian

Let's figure out the cause first.

That kind of error is seen when "ddd" is not a valid notation for "day".
Possibly the user has changed the Control Panel / Windows Regional Settings (NOT the Excel settings), to Austrian; or to German(Austria).

 

[_Adrian]

;1612001']On Thu, 23 May 2013 10:48:48 +0100, _Adria

Hi,

My first post here, hope someone can help.

I have created an Excel 2010 spreadsheet that is to be used in various
European countries (I'm in the UK). One of the pilot testers i Austria
(but otherwise using a UK laptop with UK settings) is experiencing the
following issue whereas all other testers under similar conditions
report no fault. I can't explain it...

I have a calculated date based on an input year:
A1=DATE(Current_Year,1,1)
Adjacent to this I have B1=TEXT(A1,"ddd") in order to display Mon o Tue
or Wed etc

This works fine in the version I've sent and recalculates fine However,
change the Current_Year and the result of the TEXT function become ddd
(and not Mon etc)!

Can anyone explain why, and better still, how to fix this!?

Thanks,

Adrian-

Let's figure out the cause first.

That kind of error is seen when "ddd" is not a valid notation for "day"

Possibly the user has changed the Control Panel / Windows Regiona
Settings (NOT the Excel settings), to Austrian; or to German(Austria).

I wondered if the regional settings would make a difference withi
Excel, but his Excel Help for the TEXT function still shows ddd as bein
valid. I'll ask about his settings.
Thanks

 

[Ron Rosenfeld]

I wondered if the regional settings would make a difference within
Excel, but his Excel Help for the TEXT function still shows ddd as being
valid. I'll ask about his settings.
Thanks.

I would be surprised if Excel HELP changed unless he had a non-English version of Excel.

 

[Ron Rosenfeld]

I wondered if the regional settings would make a difference within
Excel, but his Excel Help for the TEXT function still shows ddd as being
valid. I'll ask about his settings.
Thanks.

If the problem is a regional settings issue, and the user does not wish to change is regional settings, you could use an xl4 macro to return the proper code.

You cannot use xl4 macro formulas directly in a worksheet, but you can use them as a Defined Name:

Formulas/Define Name
Name: DayCode
Refers to: =INDEX(GET.WORKSPACE(37),21)

Then, in the worksheet, use:

=TEXT(A1,REPT(DayCode,3))

This should work with any country setting.

 

[Ron Rosenfeld]

;1612001']On Thu, 23 May 2013 10:48:48 +0100, _Adrian

Hi,

My first post here, hope someone can help.

I have created an Excel 2010 spreadsheet that is to be used in various
European countries (I'm in the UK). One of the pilot testers in Austria
(but otherwise using a UK laptop with UK settings) is experiencing the
following issue whereas all other testers under similar conditions
report no fault. I can't explain it...

I have a calculated date based on an input year:
A1=DATE(Current_Year,1,1)
Adjacent to this I have B1=TEXT(A1,"ddd") in order to display Mon or Tue
or Wed etc

This works fine in the version I've sent and recalculates fine. However,
change the Current_Year and the result of the TEXT function becomes ddd
(and not Mon etc)!

Can anyone explain why, and better still, how to fix this!?

Thanks,

Adrian-

Let's figure out the cause first.

That kind of error is seen when "ddd" is not a valid notation for "day".

Possibly the user has changed the Control Panel / Windows Regional
Settings (NOT the Excel settings), to Austrian; or to German(Austria).

I wondered if the regional settings would make a difference within
Excel, but his Excel Help for the TEXT function still shows ddd as being
valid. I'll ask about his settings.
Thanks.

Another of fixing this particular problem, if you do not require an actual TEXT value in B1, but merely need to see the day of the week, would be

B1: =A1

Then custom format B1 (in Excel) to "ddd". When the regional settings change, the format will also change appropriately.