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

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

edit retag reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp close date 2020-09-14 10:37:27.744247

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

( 2020-05-25 15:42:38 +0200 )edit

Sort by » oldest newest most voted

Hallo

=TEXT(A1;"NNN")

more

1

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)

( 2016-07-28 14:00:27 +0200 )edit

Use the CHOOSE function

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

more

( 2016-07-23 15:10:32 +0200 )edit

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.

( 2016-07-23 20:19:35 +0200 )edit

=WEEKDAY(A1)

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

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

more

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

( 2016-10-23 19:31:51 +0200 )edit

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

( 2016-10-25 04:51:15 +0200 )edit

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 ...(more)

( 2019-08-20 09:26:22 +0200 )edit