Looking for correct calendar formula engl. vs. german

[excelianer]

Hi,

I am using two formulas for calendar in Excel 2012 German version which are working perfectly in German office. When I open excel sheet in office 2007 Engl. Version and when I change year whole formula shows error of #NUM!

GERMAN FORMULA
“C2485” has formula ‘=WENN(TEXT($A2484;"TTT")=C2484;$A2484;""), cell format is date 09/11/2012, cell A has format customer YYYY MMMM
“D2485” has formula =WENN(TEXT($A2484;"TTT")=D2484;$A2484;WENN(C2485="";"";C2485+1))
“A284” has formula ‘=DATUM($A$1;11;1) cell formats customer YYYY MMMM
“A1 has format is “[$-407]MMMM JJJJ;@”

In company where I am using company’s PC and Office 2007 English version.

ENGLISH OFFICE 2007 FORMULA
So excel translated formulas as;
“C2485” has formula '=IF(TEXT($A2484;"TTT")=C2484;$A2484;"") ;""), cell format is date 09/11/2012, cell A has format customer YYYY MMMM
“D2485” has formula ‘=IF(TEXT($A2484;"TTT")=D2484;$A2484;IF(C2485="";"";C2485+1))
“A284” has formula ‘=DATE($A$1;11;1) ) cell formats customer YYYY MMMM
“A1” has format “[$-407]MMMM JJJJ;@”


When I open excel sheet in company with office 2007 (Engl. Version) the formula for calendar is showing me correct year and date. As I change year up or down in “A1” I got result #NUM!

What is wrong in formula or formats? Do you have any ideas?

Thanks for coming help

Excelianer

[Fotis1991]

=IF(TEXT($A2484;"TTT")=C2484;$A2484;"") ;"")

As i know, there is not sutch a format.... What is "TTT"? Replace "TTT" with "YYY" if this is year format...

[excelianer]

Hi Fotis,

Tahank you for your respond.
Well "TTT" is shortnes of "Tag=>day". Than I will tray today to replace "TTT" with "DDD" or with "YYY".
After I am going to try I can let you know did I solve this formula issue.

Regards

Excelianer

[Norie]

Did you change the semi-colons to commas?

[excelianer]

Hi Norie and Fotis,

Did you change the semi-colons to commas?
I’ve used this page also to translate and didn’t help.

http://excel-translator.de/

No I didn’t English excel converted German formulas automatically to English version like in previous posting.

I changed “TTT"(day) to “DDD" and "YYY" and didn’t work.

Has anybody else any Ideas?


Thx

Ecelianer

[Fotis1991]

So you replaced "TTT" that in German is for days, to "ddd" and didn't work?

Can you upoload a small sample workbook?

[jake.masters]

I see that the format for A1 is:

“A1 has format is “[$-407]MMMM JJJJ;@”

Perhaps change that to MMMM YYYY?

I have not seen JJJJ before, but I may just not know much...




If I google Excel Format JJJJ, all of the sites are German, which leads me to believe Excel didn't translate it properly for you.

[daddylonglegs]

The formatting part of TEXT function doesn't translate, hence your problem, probably better to use WEEKDAY function if you want to check for a particular day, that won't have any translation issues. E.g. to check if a date in A1 is a Saturday and act on that...

=IF(WEEKDAY(A1)=7,"do this","do that")

1 = Sunday through to 7 = Saturday

[Norie]

When you changed the format strings to "DDD", "YYY" etc what was the problem?

[excelianer]

Hi,

I added my files, please have look and open it with english excel
and please let me know what would be the best solution.

Thank you very much

Excelianer

[Norie]

I think I can see 2 problems.

Using ddd returns Mon, Tue, Wed etc not Mo, Tu, We etc.

To ge Mo, Tu etc you can use this

Please Login or Register  to view this content.

Also on your worksheet you've still goto the German abbreviations for the days of the week in C6:I6.

So you'll need to change those as well.

[excelianer]

Hi Norie,

thanks a lot I will try it in company in two day. I hope it will work.

Thanks for your help

Excelianer