none
WEEKDAY function does not display day name RRS feed

  • Question

  • Hi, I have a formula, where I need to display the name of the day as follows

    =TEXT(WEEKDAY(DATE(CalendarYear;12;1);1);"aaa")

    I get in all cells 1900 and not the day name, what is wrong in this formula?

    Thanks for your help

    Saturday, January 16, 2016 12:56 PM

Answers

  • The TEXT function will always use your system settings, so if it is not feasible for you to set your Windows locale to English, you'll have to specify the day names in the formula. For example:

    =CHOOSE(WEEKDAY(DATE(CalendarYear;12;1));"Sun";"Mon";"Tue";"Wed";"Thu";"Fri";"Sat")

    or

    =MID("SunMonTueWedThuFriSat";3*WEEKDAY(DATE(CalendarYear;12;1))-2;3)


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by eliassal Sunday, January 17, 2016 12:37 PM
    Saturday, January 16, 2016 8:35 PM

All replies

  • Assuming that your system settings are French, the letter "a" stands for année = year. For day, use the letter "j" from jour.

    Moreover, there is no need to use WEEKDAY, since TEXT will format the date itself. So use

    =TEXT(DATE(CalendarYear;12;1);"jjj")

    if you want the abbreviated day name (Lun, Mar, ..., Dim), or

    =TEXT(DATE(CalendarYear;12;1);"jjjj")

    for the full day name.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, January 16, 2016 2:27 PM
  • So many thanks, yes it is working but my problem is that I need th day name in english not in French (my windows is english but locale and other date related config is French, how can this be acheived?Regards

    Saturday, January 16, 2016 6:00 PM
  • The TEXT function will always use your system settings, so if it is not feasible for you to set your Windows locale to English, you'll have to specify the day names in the formula. For example:

    =CHOOSE(WEEKDAY(DATE(CalendarYear;12;1));"Sun";"Mon";"Tue";"Wed";"Thu";"Fri";"Sat")

    or

    =MID("SunMonTueWedThuFriSat";3*WEEKDAY(DATE(CalendarYear;12;1))-2;3)


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by eliassal Sunday, January 17, 2016 12:37 PM
    Saturday, January 16, 2016 8:35 PM
  • So many thanks again, I really appreciate your prompt response and the different options you provided, it works like a charm
    Sunday, January 17, 2016 12:36 PM