WEEKDAY value and name don't match

English
, , , ,
#1

I’m making a table with dates in one column and weekdays in another in LibreOffice Calc. The column $A has dates formatted as YYYY-MM-DD, the column $B has weekdays derived from these dates using WEEKDAY formula and is formatted as NN (shortened weekday name).

Now, I have locale set to Russian and our week starts on Monday. The issue is that while the displayed weekday name matches the actual weekday of the corresponding date, the weekday cell VALUE is off by 1 for my locale (i.e. Friday has value of 6 instead of 5).

According to documentation for WEEKDAY function (WEEKDAY), it has the following syntax: WEEKDAY(Number; Type). The default type is 1 and the default returned values start from 1 for Sunday, 2 for Monday etc. But I can override this by setting type parameter to 2, so Monday becomes the first day of the week. And that’s where strange things start to happen.

For the cell $A2 set to 2019-11-01 (which is Friday), the cell $B2 results in the following:

If the formula is =WEEKDAY($A2), the displayed weekday is Friday (correct), but the cell VALUE is 6 (incorrect).
If the formula is =WEEKDAY($A2;2), the cell VALUE becomes 5 (correct), but the displayed weekday becomes Thursday` which is wrong!

What am I doing wrong? Or what settings should I change to get weekday number and weekday name in sync according to my locale? I was under impression that this should be happening automatically, but, apparently, it doesn’t.

#2

Help states:
Returns the day of the week for the given date value. The day is returned as an integer between 1 (Sunday) and 7 (Saturday) if no type or type=1 is specified. For other types, see the table below.

#3

To show a name of a week day for a date, you should not use WEEKDAY function and then format the resulting number as date. Doing that, you tell LibreOffice to tell you which day of week was Nth day after 1899-12-30, where N is the number returned by WEEKDAY. Instead, you should format the date itself using NN format string.

#4

I’m not quite sure what you mean by “which day of week was Nth day after 1899-12-30”.

The description for the WEEKDAY function clearly says that it “Returns the day of the week for the given date value”. And it does exactly that: returns the weekday value of a date in a specified cell on a range from 1 to 7. Which is exactly what I need it to do. But these values are not correct for selected locale (Monday should be day 1), which should be solved by specifying type parameter for WEEKDAY function, as described in function manual. Only doing this results in an improper display of weekday names when the cell date formatting is set to NN.

Either there is a mismatch between weekday names mapping and locale settings, or something else. Hence the question.

#5

Date values are integers, counting days from new year’s eve 1899. Those are the numbers you can sensibly apply date formatting to.

When you apply date formatting to weekday number (from 1 to 7, derived from a date value but itself not a proper date value), you are doing it wrong.

#6

What is a date? A date in Calc is by definition of the ODF specification an integer value counting the number of days since epoch, which by default is 1899-12-30 (Have you ever asked what’s behind Tools -> Options -> LibreOffice Calc -> Calculate -> Radio Buttons: Date? - here you are - this is date with integer 0). Thus if you add a 5 (or somehow calculate a 5) to a cell and advice Calc Show me this as a date, calc shows you 1900-01-04, which is the 5th day after 0 and which was a Thursday. So everything works according to OASIS ODF specification.

#7

WEEKDAY returns the number of the week day, and the description and your understanding are right. What is wrong is your understanding of what formatting does. You apply NN format to an arbitrary number, and think that it should convert number of a week day into its name. But that is incorrect. NN formatting takes not a number of a day of a week; it takes a date, and shows you which weekday it is. So when you have 1 and format it with NN, you tell LibreOffice: “take date 1, and tell me which day of the week it was”. LibreOffice stores all dates as simple numbers, and counts them starting from date “0”, which is defined to be 1899-12-30. So when you tell LibreOffice to show weekday of date 1, it shows you the weekday of 1899-12-31.

#8

I can set cell $B2 to =VALUE(A2), so it takes the date value from A2, and then format it as NN to get weekday names.

But:

  1. how can I then apply conditional formatting to weekend days only?

  2. how integer value of the date in A2 is different from the value used by WEEKDAY in B2 to get weekday?

#9

Either there is a mismatch between weekday names mapping and locale settings, or something else. Hence the question.

Here is your misconception. You expect some names mapping coming along with WEEKDAY, which doesn’t exist. It just provides an integer.

conditional formatting to weekend days only OR(WEEKDAY(A2;2)=6;WEEKDAY(A2;2)=7) assuming A2 contains your date.

#10

how can I then apply conditional formatting to weekend days only?

Ask a specific question about that, describing your data and your end goal.

how integer value of the date in A2 is different from the value used by WEEKDAY in B2 to get weekday?

In your A2 (with =TODAY()), you have 43810 for today. In a cell with =WEEKDAY(A2), you have 4 for today. Do you see the difference? :wink:

EDIT: there’s no difference between integer part of A2 and B2 which is equal to A2. You may also format your A2 and have the same result.

#11

Mike, Opaque, are you saying that the integer values from 1 to 7 I get in $B using WEEKDAY are no different from manually inputting 1 to 7 and then applying NN formatting to those cells? If so, then how come then weekday names I get are properly corresponding to the dates in column$A? Is this just a coincidence?

#12

Sure - an integer is an integer. There is no such thing as memory of descent (meaning: Please take this integer as a result of WEEKDAY). (BTW: The whole question again proves the name WEEKDAY() for this function completely misleading; but it is defined in the standard and compliant implemented in LibreOffice)

#13

how come then weekday names I get are properly corresponding to the dates

yes, that’s just a coincidence coming from this: day zero (epoch) defaults to 1899-12-30 (Saturday); WEEKDAY returns 1 for Sunday and 7 for Saturday by default. Now look at this:

WEEKDAY(<date-that-is-Sun>) => 1; FORMAT(1; "NN") => epoch (Sat) + 1 => Sun
WEEKDAY(<date-that-is-Mon>) => 2; FORMAT(2; "NN") => epoch (Sat) + 2 => Mon
WEEKDAY(<date-that-is-Tue>) => 3; FORMAT(3; "NN") => epoch (Sat) + 3 => Tue
WEEKDAY(<date-that-is-Wed>) => 4; FORMAT(4; "NN") => epoch (Sat) + 4 => Wed
WEEKDAY(<date-that-is-Thu>) => 5; FORMAT(5; "NN") => epoch (Sat) + 5 => Thu
WEEKDAY(<date-that-is-Fri>) => 6; FORMAT(6; "NN") => epoch (Sat) + 6 => Fri
WEEKDAY(<date-that-is-Sat>) => 7; FORMAT(7; "NN") => epoch (Sat) + 7 => Sat

And just possibly the choice of epoch by MS (US-based; counting weeks from Sun) was somehow related…

#14

Ok, I get how it works now. Thank you both so much for the explanation! I’m marking the question as solved.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.