up vote 4 down vote favorite
share [g+] share [fb]

Given a date, how do I get the day of the week (e.g. "Monday") into a cell in Excel?

link|improve this question
Well, you could type M-O-N-D-A-Y into the cell with your keyboard... but somehow I think you mean something different. – Mike Cooper Sep 15 '09 at 23:07
Oh yeah :o) I could indeed type it. I'll update the question to make it more specific – Don Vince Sep 15 '09 at 23:13
feedback

3 Answers

up vote 10 down vote accepted

Simple example:

A1 cell: 1/8/2009
B1 cell: =TEXT(WEEKDAY(A1),"dddd")

This will, for the given date, print the corresponding day.

Is this what you wished ?

link|improve this answer
Many thanks! Small typo: there is a semicolon in the middle which should be a comma. – Don Vince Sep 15 '09 at 23:12
Thanks for the correction Molly – Don Vince Sep 15 '09 at 23:42
@Don Vince - Ups, sorry about that. – ldigas Sep 15 '09 at 23:53
The semicolon is required in localisations (Regional Settings in Windows) that use the comma for the decimal separator. Like in languages where the English 1,000.00 (one thousand) would be written as 1.000,00 and would thus be entered as 1000,00 in Excel. I actually thought a semicolon was always accepted, but I may be wrong about that. – Arjan Sep 16 '09 at 0:03
you're welcome :) and the correct answer deserves an upvote +1 – Molly7244 Sep 16 '09 at 0:07
show 2 more comments
feedback
up vote 4 down vote

Another possibility, depending on what you want to do with the date afterwards, is to set the cell's format to Custom: dddd

link|improve this answer
Superb! That does the trick, and is great for the situation where you don't want to use another column. Thanks – Don Vince Sep 15 '09 at 23:21
Even if you want to use another column, you could do this and simply enter a formula like =A1 in the example from the first answer. – Arjan Sep 16 '09 at 0:04
feedback
up vote 0 down vote

The answer given above is only working by fluke because 1/1/1900 was a Sunday and by default Excel is using Sunday as first day of week for the Weekday function.

What you are actually calculating in that method is the day of the week as a number, then formatting that as a day based on that number interpreted as a date. Eg if your date is 1/2/2003 and you use the WEEKDAY function, this results in 7 (=Saturday). When you then format this as "dddd" you are actually getting the day name of the 7th day in Excel, ie 7/1/1900, which happens to be a Saturday. This formula will break if someone opens it who has the option selected to use the 1904-based date system, as 1/1/1904 was not a Sunday, but a Friday. (yes I know hardly anyone uses that, but you don't want to build a solution which relies on that do you?)

You can make the formula shorter, faster and more robust simply by using

=TEXT(A1,"dddd")

You could of course just format the date cells themselves with a custom format as already suggested, depending on whether you really need this in a separate column or not. I often use date formats such as

ddd dd mmm yyyy

to give eg Sat 01 Feb 2003 so the date is explicit but shows the weekday name as well.

Using a second column and a TEXT function is essential if you want to use the weekday explicitly somewhere in a mail merge (for example), similarly for things like currencies and so on Excel > Word merging passes the actual underlying stored value rather than the on-screen formatted version, so regardless of the cell format, Word sees some horrible number. A true text field is passed 'as is' and displays properly in Word.

link|improve this answer
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.