Ask Your Question

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

asked 2016-07-21 15:49:17 +0200

EasyRider gravatar image

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

edit retag flag offensive close merge delete


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

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

3 Answers

Sort by » oldest newest most voted

answered 2016-07-21 16:02:24 +0200

karolus gravatar image


edit flag offensive delete link more



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)

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

answered 2016-07-21 16:05:18 +0200

mark_t gravatar image

Use the CHOOSE function

=CHOOSE(WEEKDAY(J19,2),"Monday", "Tuesday", "Wednesday","Thursday","Friday","Saturday","Sunday")
edit flag offensive delete link more


the question was about easier way… not about sophisticated one

karolus gravatar imagekarolus ( 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.

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

answered 2016-10-23 19:12:43 +0200

adam1969in gravatar image


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

=Text(A1,"DDDD") will be the best one.
edit flag offensive delete link 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

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

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

adam1969in gravatar imageadam1969in ( 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)

Mike Kaganski gravatar imageMike Kaganski ( 2019-08-20 09:26:22 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2016-07-21 15:49:17 +0200

Seen: 10,169 times

Last updated: Oct 23 '16