Ask Your Question
1

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

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

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

Comments

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 +0100 )edit

3 Answers

Sort by » oldest newest most voted
3

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

karolus gravatar image

Hallo

=TEXT(A1;"NNN")
edit flag offensive delete link more

Comments

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)

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

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

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

Comments

the question was about easier way… not about sophisticated one

karolus gravatar imagekarolus ( 2016-07-23 15:10:32 +0100 )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 +0100 )edit
-2

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

adam1969in gravatar image

=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.
edit flag offensive delete link more

Comments

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 +0100 )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 +0100 )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 +0100 )edit

Question Tools

1 follower

Stats

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

Seen: 12,096 times

Last updated: Oct 23 '16