Ask Your Question

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

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 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

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

Question Tools

1 follower


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

Seen: 13,972 times

Last updated: Oct 23 '16