How do I derive a text day (Monday, Tuesday,...) from a date field (7/21/2016)

So far, I have a long, complicated “IF” statement, using WEEKDAY()…
There must be an easier way!

Create a table with two columns. First column has numbers 1 to 7 and second column text Sun… Sat. Label this table DayOfWeek.

Then use the formula =vlookup(weekday(A1, 1),DayOfWeek,2)

Eg A1 = Date(2000,3,23) result should be Thu


1 Like

Better use TEXT(A1;"DDDD") if you eventually want to save in Excel formats, as the “NNN” and “NNNN” format codes are only available in LibreOffice and OpenOffice(.org)

Use the CHOOSE function

=CHOOSE(WEEKDAY(J19,2),"Monday", "Tuesday", "Wednesday","Thursday","Friday","Saturday","Sunday")

the question was about easier way… not about sophisticated one

Compared to string of IF functions CHOOSE is easier, but of course TEXT is the best and EasyRider should choose that as the best answer. I looked for FORMAT and didn’t realize that function was called TEXT.


and formatting the cell as Date - user defined - “NNNN”
The other answers are right too.

=Text(A1,"DDDD") will be the best one.

And your answer is wrong, also if it produce accidentally the “right” results.
do never patch some Date-formatting on Values which are no real Dates

The main formula I recommended is =Text(A,“DDDD”), though it wrongly works with Weekday function.

Just in case someone would still think it’s a “great idea”: the proposed thing is totally wrong. =WEEKDAY(A1) would call functions to analyze the date in A1 and put numbers from 1 to 7 for Sun through Sat. Then applying a user-defined format NNNN to the cell with that number will tell Calc to treat that number as date (it means, Calc will see 1899-12-31 for number 1, 1900-01-01 for number 2, etc. by default), call functions to analyze this date again, and find out which day of the week was that day - and luckily, 1899-12-31 was Sunday… so you will eventually see Sunday in your formula cell, for the cost of double calculations where only one was needed (just put =A1 in the cell, and format NNNN or DDDD).

But what’s worse, this would not only cost more CPU cycles, but will give wrong results, if one used non-default start date (epoch; see OptionsLibreOffice CalcCalculateDate).