33
3
Given a date, how do I get the day of the week (e.g. "Monday") into a cell in Excel?
Don Vince
Posted 2009-09-15T23:04:12.367
Reputation: 435
How do I get the day name into a cell in Excel?
Answers
54
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 ?
Rook
Posted 2009-09-15T23:04:12.367
Reputation: 21 622
@Quandary Don't even use WOCHENTAG() at all... just use the Text function directly against the value. – ErikE – 2016-01-29T18:49:58.933
Many thanks! Small typo: there is a semicolon in the middle which should be a comma. – Don Vince – 2009-09-15T23:12:24.143
@Don Vince - Ups, sorry about that. – Rook – 2009-09-15T23:53:55.353
2The 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 – 2009-09-16T00:03:02.840
you're welcome :) and the correct answer deserves an upvote +1 – None – 2009-09-16T00:07:05.593
@Arjan van Bentem: At least id doesn't work the other way around. I am using German settings and I always have to use semicolons. – Lucas – 2009-09-17T09:57:15.257
4This is only working because of an oddity of the 1/1/1900 being a Sunday. Lose the weekday function completely, just use =TEXT(A1,"dddd") (see my fuller answer below) – AdamV – 2009-11-02T11:05:21.080
Moreover, only working with the English Version of Excel. German Version is Text(WOCHENTAG(A1); "tttt"). While Text is the same, and weekday gets translated automagically, note the "tttt" instead of "dddd", which does NOT get translated automagically. – Quandary – 2013-12-19T12:54:27.433
19
The answer given above is only working by fluke because Excel thinks that 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 since its "epoch", 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.
*in fact it is a Monday but Excel was written to match the incorrect dates in Lotus 1-2-3 which treated 1900 as a leap year when it is not.
AdamV
Posted 2009-09-15T23:04:12.367
Reputation: 5 011
I already upvoted it before your edit. Now it's even better. – Jeroen Wiert Pluimers – 2012-10-02T17:26:40.690
7
Another possibility, depending on what you want to do with the date afterwards, is to set the cell's format to Custom: dddd
Margaret
Posted 2009-09-15T23:04:12.367
Reputation: 1 536
Superb! That does the trick, and is great for the situation where you don't want to use another column. Thanks – Don Vince – 2009-09-15T23:21:36.870
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 – 2009-09-16T00:04:55.333
5
I found that nesting IF statements can be cumbersome, but it does work. If, however, you'd like to save a little typing, you may try this:
=CHOOSE(WEEKDAY(A2), "Sun","Mon","Tue","Wed","Thur","Fri","Sat")
Or, if you need full names:
=CHOOSE(WEEKDAY(A2), "Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday")
In this example, "A2" can be whatever cell (or formula) contains the date in question. For instance:
=CHOOSE(WEEKDAY(TODAY()), "Sun","Mon","Tue","Wed","Thur","Fri","Sat")
would print the three letter abbreviation for whatever today is.
Brad
Posted 2009-09-15T23:04:12.367
Reputation: 51
1
you can also localize the answer by using [$nnn] before the format (so the custom code is: [$nnn]dddd;@). change nnn with the proper language code. i don't have the list, but somehow, english code is -409 (and my local is -421).
i think you can experiment with Number format, change the language field, then change it back to custom format.
user261479
Posted 2009-09-15T23:04:12.367
Reputation: 11
1Welcome to SuperUser. Answers should always be self contained, so please expand your answer into a working example. Also we don't know where you come from, so my local is a little unspecific. – Tim – 2013-10-10T16:18:37.627
1
A1 cell: 1/8/2009 B1 cell: =A1 then press ctrl+1 (format cell) select number tab, click custom then type "DDDD" on the type txtbox
user334332
Posted 2009-09-15T23:04:12.367
Reputation: 11
0
Format Cells - Date - Calendar Type (select Gregorian English) - Type (section has your required format Wednesday, March 14, 2001)
Raj
Posted 2009-09-15T23:04:12.367
Reputation: 1
0
WEEKDAY Function can be used in Vba Codes . For example :
Label1.Caption = WeekdayName(Weekday(TextBox1, 0), False, 0)
Day name is gotten from TextBox1 in above example. Result is "Monday" .
I used this function in when I created userform about date entry to active cell with right-click menu.
kadrleyn
Posted 2009-09-15T23:04:12.367
Reputation: 1
Suggestion: what's unique about your answer, the part that directly answers the question, is weekdayname, not weekday. That's the term to focus on in the first sentence. – fixer1234 – 2016-11-30T02:52:03.707
0
Displays current date
=TEXT(WEEKDAY(MONTH(TODAY())),"dddd")
Displays current date with required required text.
=CHOOSE(WEEKDAY(MONTH(TODAY())), "S-U-N-D-A-Y","M-O-N-D-A-Y","T-U-E-S-D-A-Y","W-E-D-N-E-S-D-A-Y","T-H-R-S-D-A-Y","F-R-I-D-A-Y","S-A-T-U-R-D-A-Y")
Glen Lloyd
Posted 2009-09-15T23:04:12.367
Reputation: 1
This is just duplication of several earlier answers. – fixer1234 – 2016-06-29T04:28:17.470
4Well, 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 – 2009-09-15T23:07:07.133
Oh yeah :o) I could indeed type it. I'll update the question to make it more specific – Don Vince – 2009-09-15T23:13:56.683