I have a column of cells with a formula being =TEXT(B10; "DDDD"), which lets me pick the date from the adjacent column and turn it into a week day. Now, I need weekends to have a specific style applied, which I can do with conditional formatting. Issue is, I have no way of selecting just weekends in an internationalized way (so that locales don’t break things) to apply my weekends style. How do I convert that text field in the cells to a number between 0 and 7, so that I can pick just weekends and apply the style I want?
WEEKDAY(B10;2) returns 1 to 7 for Monday to Sunday.
Conditional format formula: WEEKDAY(B10;2)>5 evaluates to TRUE on Saturdays and Sundays.
1 Like
This is eventually how I solved it, but still, how would I go about converting days from text to a number between 1 and 7?
=MATCH(A1;$X$1:$X$12;0) where X1:X12 has the month names.
Nitpick: even with numbers between 0 and 6 (or 1 and 7) it wouldn’t be internationalized because some cultures have weekends on other days than Saturday/Sunday.
As some complain, “we” don’t answer their questions:
After your =TEXT(B10; "DDDD") you have strings from "1" to "31" wich you can convert back to integer with VALUE().
.
Transform this numbers to a range 0..7 you may use MOD 7, so try
=VALUE(TEXT(B10; "DDDD")) MOD 7
.
But for the task you described above I guess you will need to shift the values by some WEEKDAY() of the first day of the month. So I would use the suggestion of @Villeroy - but this is up to you…
.
Some links to help for MOD and VALUE:
https://help.libreoffice.org/latest/en-US/text/sbasic/shared/03070600.html?DbPAR=BASIC
https://help.libreoffice.org/latest/en-US/text/scalc/01/func_value.html
1 Like
Um… no, the result is day names from Monday to Sunday.