0
votes

Ive got a list of dates in column B in an excel spreadsheet, id like to populate column A with the day (mon - sun) that relates to that date eg. if we where going the day of this post it would be :

Column A: Wednesday, Column B: 14/09/16

Ive started writing a formula to work out the day number of the year eg. 1st jan would be 1 and then by manually knowing the first day of the year for one year work out the others, then +/- a day for each year past and forward, but this seems overly complex is their a simpler way to do it ?

2
Date is a count of days, just mod by 7 or use a format.chux - Reinstate Monica

2 Answers

3
votes

Yes, there's a way:

=TEXT(B1,"dddd")

Assuming B1 is your cell with date. Alternate way (if you have some non-English locale or if you need to specify weekdays in some custom format):

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

Note that the list of days starts from Sunday.

1
votes

You can kill yourself trying to implement it by hand or you can use function WEEKDAY. https://support.office.com/en-us/article/WEEKDAY-function-60e44483-2ed1-439f-8bd0-e404c190949a

Note the return_type parameter in WEEKDAY(serial_number,[return_type]). If you are in English speaking domain and your week starts on Sunday, you probably will pass 1. If you are in the rest of the world and your week start with Monday, then you will use 2. I do not know, maybe there are some other parts of the world where you would use different number...

UPDATE: I did not notice you want to see the weekday as a text. Then the answer by Taosique is the one to use.