Weekday from date in TEXT formula independent from used language in Excel

user_4934150 0
Viewed 103
            A         |       B       |
----------------------|---------------|--------------------------                    
1      2022-02-14     |       Mo      |    B1 = TEXT(A1,"DDD")
2                     |               |
3                     |               |

I have a date written in Cell A1 and I want to extract the weekday from it in Cell B1.
All this works fine as long as I use the English version of Excel.

Now, some co-workers use the German version of Excel.
When they open the file the DDD part of the formula is not recognized anymore.
Therefore, instead of the weekday in Cell B2 just DDD as text is displayed.
I think this problem is quite similar to the issue in this question.

Therefore, I already implemented the following formula to the name manager:

Time_Global_Day =INDEX(GET.WORKSPACE(37),21)

Afterwards I switched the formula in Cell B1 to:

=TEXT(A1,REPT(@Time_Global_Day,2))

With this solution I am able to extract the day as number from the date in Cell A1.

How do I need to modify it to display the weekday no matter which language is used for Excel?

excel-formula excel
1 Answers

The solution from JvdV in the comments solves the problem:

full name of days   -->  B1 = TEXT(A1,REPT(Time_Global_Day,4))
short name of days  -->  B1 = TEXT(A1,REPT(Time_Global_Day,3))

If the names should be always in English you need to add "[$-409]" in front of REPT:

full name of days   -->  B1 = TEXT(A1,"[$-409]"&REPT(Time_Global_Day,4))
short name of days  -->  B1 = TEXT(A1,"[$-409]"&REPT(Time_Global_Day,3))

NOTE: Depending on which version of Excel you use it could be necessary to:
Enable Excel 4.0 macros when VBA macros are enabled in the Trust Center.

user_49341500
Related