Formula error since 10.5.6 update ...

[rlesperance]

Hi,
I have been working with a spreadsheet for years without any problem
of this sort. Since my recent 10.5.6 upgrade a part of a formula
does
not return a correct result.

My problem seems to be related to the incorrect result of this part
of
my formula: TEXTE($N$5;"dd-mm-yy"). In this example, N5 is equal to
"31-10-09". This formula should return : 31-10-09 It returns
"dd-10-
yy". Funny, the "31" is replaced by "dd" and "09" by "yy", while the
month number is correct.

Any idea with this is all about ? Thanks in advance.


Robert Lespérance

 

[Bob Greenblatt]

Hi,
I have been working with a spreadsheet for years without any problem
of this sort. Since my recent 10.5.6 upgrade a part of a formula
does
not return a correct result.

My problem seems to be related to the incorrect result of this part
of
my formula: TEXTE($N$5;"dd-mm-yy"). In this example, N5 is equal to
"31-10-09". This formula should return : 31-10-09 It returns
"dd-10-
yy". Funny, the "31" is replaced by "dd" and "09" by "yy", while the
month number is correct.

Any idea with this is all about ? Thanks in advance.


Robert Lespérance

What language version are you using? If you are using a German version (Just
a guess because of the "TEXTE" try tt instead of dd and jj instead of yy. If
not German try the abbreviations for the language you are using.

 

[CyberTaz]

The only suggestion I can offer is that you might consider fully updating OS
X ‹ I assume that's what you mean by "10.5.6", but Leopard has been at
10.5.8 for some time now.

You've also given no indication of what version & update level of
Office/Excel you're working with, so it might be a good idea to check for
updates there as well.

HTH |:>)
Bob Jones
[MVP] Office:Mac

 

[rlesperance]

Hi Bob,

Excuse me for the confusion ... I am running OS 10.6.2 on an iMac
Intel and Excel 10.5.6 in French. So I should have written TEXT
function instead of TEXTE. All other parameter have been translated
to English in my post.

Regards.


Robert Lespérance

 

[rlesperance]

Problem update ... I found a work around. To better explain the
case, I must write the formula in French. Consider that N5 = 31-10-09
(dd-mm-yy)

My formula was: TEXTE($N$5;"jj-mm-aa") and always returned "31-10-
09". Since I updated to Excel 10.5.6, this formula does not work
properly and returns: "dd-10- yy"
My work around (I modified the formula): TEXTE($N$5;"dd-mm-yy") and
now it returns the correct answer : "31-10- 09".

Why do I now have to change the way I write the format for the TEXTE
Excel function ?

Regards.



Robert Lespérance

 

[rlesperance]

BTW what is the difference with this group and this one:
microsoft.public.excel.macintosh ?

 

[Bob Greenblatt]

Hi Bob,

Excuse me for the confusion ... I am running OS 10.6.2 on an iMac
Intel and Excel 10.5.6 in French. So I should have written TEXT
function instead of TEXTE. All other parameter have been translated
to English in my post.

Regards.


Robert Lespérance

Well, I can't reproduce your problem as I am running with the US versions. I
will forward your observations to Microsoft. It looks like there may be a
bug with the update. The other Excel forum is really old. Unfortunately,
there is no way to delete it from the usnet servers, so it keeps popping up.
This is the correct forum for Macintosh Excel issues.

 

[CyberTaz]

Well, that still leaves a question - unless there is something unique about
the French version of Mac Excel. The version you cited "10.5.6" indicates
the v.X (10.x.x) release for which there is no such update level. The last
update for v.X was 10.1.9 & there will be no more. That version of Office
has been deprecated for roughly 2 years.

Regards |:>)
Bob Jones
[MVP] Office:Mac

 

[rlesperance]

Hi,

Another confusion .. If I go to «About Excel» in the Excel menu bar
item I get : Microsoft Excel 2004 for Mac v 11.5.6 (090928). So
excuse for the error, I run the 11.5.6 version not the 10.5.6. I do
not know where Cyber Taz gets his information regarding Excel version
#. I got mine from the application running on my computer. It was
updated automatically by the «Microsoft AutoUpdate» application.

As for the bug ... just after posting the problem to this thread,
there was a power failure. So the computer had to restart. Upon
restart, the same file that I had modified to «TEXTE($N$5;"dd-mm-yy")»
did not work anymore. I had to change it back in French «TEXTE($N
$5;"jj-mm-aa")» and it worked. But later, it stopped retourning the
good result and I had to put it back to «TEXTE($N$5;"dd-mm-yy")». It
stills works now. I will have to see if a new computer restart will
corrupt the function again. I will keep the result posted.

Thanks for helping.



Robert


P.S. Is it possible to receive all replies to this thread to my
personnal email adress ?

 

[XinXin]

Hi Robert,

We spent a good amount of time trying to reproduce the formatting issue you reported. Unfortunately, we still can't find concrete steps to repro it. Could you please help share a little bit more info:

1. What's your OS language?
2. What's your locale (system perferences -> language & text -> Formats)?
3. Could you please share with us ([email protected]) a copy of your workbook?

Thanks,
XinXin Liu
Macintosh Business Unit, Microsoft

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[CyberTaz]

Hello Robert;

<snip>

I do
not know where Cyber Taz gets his information regarding Excel version
#.

<snip>

I got it from your previous posts;

11 January:

Post 1: Since my recent 10.5.6 upgrade
Post 2: I am running OS 10.6.2 on an iMac Intel and Excel 10.5.6 in French.
Post 3: Since I updated to Excel 10.5.6

Regards |:>)
Bob Jones
[MVP] Office:Mac

 

[rlesperance]

Hi XinXin Liu,

I regret the delay ... I was busy with my father's health. My
answers:

1. What's your OS language? = french canadian
2. What's your locale (system perferences -> language & text ->
Formats)? = In this order: French canadian - French - Canadian English
- US English
3. Could you please share with us ([email protected]) a copy of
your workbook? Yes ... no problem . Just did.


New observations ... Let's start that all over. I now have a better
picture of the situation.

1. I have 2 computers, one iMacG5 running Tiger (10.4.11) and one
iMac Intel running Snow Leopard (10.6.2). MS Excel 11.5.6 is
installed on both. Both also have the same «language & text» formats
installed in the same order.

2. I experience a «date format problem» when accessing a MS Excel
file located on my Tiger machine from my Snow Leopard machine.

3. I don't experience any «date format problem» with this file on the
Tiger machine. On the Tiger machine, the system's date format (dd-mm-
yyyy) is the same as the one I see in MS Excel's cell edit field,
except for the fact that my system use «-», not «/».

5. On the Snow Leopard machine, MS Excel's cell edit field displays
the date in the «yyyy/mm/dd» format while the system format is still
«dd-mm-yyyy», like on my Tiger machine. The date format everywhere
else on my Intel machine is identical with the system's date format
(dd-mm-yyyy).


So when I open the Tiger file on my Snow Leopard machine, a formula
that contains date format information is not calculated, while the
same spreadsheet is calculated correctly when opened and edited on my
Tiger machine.

Another observation, how can I be able to open the file on my SL
machine if the file is already opened on my Tiger machine ? How can
that be possible ? It could be a source of major mistake and error.

I sent you the file to your email address as requested.

Regards.




Robert Lespérance

 

[rlesperance]

Hi XinXin Liu,

I regret the delay ... I was busy with my father's health. My
answers:

1. What's your OS language? = french canadian
2. What's your locale (system perferences -> language & text ->
Formats)? = In this order: French canadian - French - Canadian English
- US English
3. Could you please share with us ([email protected]) a copy of
your workbook? Yes ... no problem . Just did.


New observations ... Let's start that all over. I now have a better
picture of the situation.

1. I have 2 computers, one iMacG5 running Tiger (10.4.11) and one
iMac Intel running Snow Leopard (10.6.2). MS Excel 11.5.6 is
installed on both. Both also have the same «language & text» formats
installed in the same order.

2. I experience a «date format problem» when accessing a MS Excel
file located on my Tiger machine from my Intel machine.

3. I don't experience any «date format problem» with this file on the
Tiger machine. On the Tiger machine, the system's date format (dd-mm-
yyyy) is the same as the one I see in MS Excel's cell edit field,
except for the fact that my system use «-», not «/».

5. On the Snow Leopard machine, MS Excel's cell edit field displays
the date in the «yyyy/mm/dd» format while the system format is still
«dd-mm-yyyy», like on my Tiger machine. This format is annoying
because to be recognized by Excel I hat to input it in the «yyyy/mm/
dd» format while everywhere else on my computer the date conforms with
the system's date format (dd-mm-yyyy).


So when I open the Tiger file on my SL machine, any cell that refers
to date format information is not calculated, while the same
spreadsheet is calculated correctly when opened and edited on my Tiger
machine. Another observation, how can I be able to open the file on
my SL machine if the file is already opened on my Tiger machine ? How
can that be possible ? It could be a source of major mistake and
error.

I sent you the file as requested.


Regards.




Robert Lespérance

 

[rlesperance]

Hi XinXin,

Another update ... I hope I didn't forget anything this time.

Forget item 5. in my last post. There was an error in my Snow
Leopard's OS date format. Some date formats were customized to fit
the «dd-mm-yyyy» and some other were still with the «yyyy-mm-dd»
format. That explains item 5. of my last post. After modifying all
SL's OS date format to «dd-mm-yyy» formats, Excel's cell edit field
displays the date accordingly.

Because I explained the problem in many posts, I realize that my last
one would have benefit from restating the problem described in item
2. Here is the problem: when I open the file on my SL machine a cell
does not compute as it does on my Tiger machine. I found that the
problem is related to this part of the formula:

TEXTE($D$5;"jj-mm-aa") ----> the "jj-mm-aa" date format is the French
equivalent of "dd-mm-yy" and the TEXTE function is the French
equivalent of the TEXT function

In order for the formula on the SL machine to compute without error
like on my Tiger machine, I have to change that part of the formula
to:

TEXTE($D$5;"dd-mm-yy") ----> changing "jj-mm-aa" to "dd-mm-yy" fixes
the problem, but creates another one on the Tiger machine if I edit
and save the file from my SL machine.

I hope you can decipher all my posts and understand my problem. My
English can also be misleading because I am French Canadian. If you
do not understand all my explanations, I would be glad to help you by
restating my case once more.

Regards.



Robert Lespérance

 

[Phillip Jones, C.E.T.]

You do know the newest version of OSX is 5.8 you have 5.6. Try updating
to 5.8 and see if that helps first.

 

[rlesperance]

You do know the newest version of OSX is 5.8 you have 5.6. Try updating
to 5.8 and see if that  helps first.





rlesperancewrote:

Please read again ... 10.5.6 refers to MS Excel's version not OS X's.

 

[Phillip Jones, C.E.T.]

Please read again ... 10.5.6 refers to MS Excel's version not OS X's.

Sorry. Not familiar with the 10 series. Office2004 is 11 and latest is
11.5.6

 

[CyberTaz]

Once again, a 10.5.6 version of Excel for Macintosh does not exist.

There is a 10.5.6 version update of Mac OS X Leopard (10.5), however.

Please read again ... 10.5.6 refers to MS Excel's version not OS X's.

Regards |:>)
Bob Jones
[MVP] Office:Mac