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 ?
Answer from Rook on Stack ExchangeI can't find out how to get weekday from date
Kindly help me extract the day out of a date
Excel - how to get if a date is a specific day of the week? - Stack Overflow
Calculating for Previous Monday - Dates
Videos
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 ?
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.
Hello everyone. I wanted to create a spreadsheet in Excel, and I've barely used it before. Something I haven't managed to do is to extract the weekday from a date. This sounds fairly simple, but I just can't wrap my head around it and can't find much to help. Sorry if this is a stupid question.
The format is DD.MM.YYYY
Hi All ! Please help!
I' trying to extract the day out out of a column that has dates in this format (10/29/2020) which should give me 29. Im using the DAY formula but it's not working, any suggestions? Thanks
To determine the weekday of a date in EXCEL use the =WEEKDAY() formula, which evaluates as 1 (Sunday) to 7 (Saturday)
e.g. If A1 contains 12/31/2016 (or 31/12/2016 if you're from where I'm from), the formual =WEEKDAY(A1) would evaluate to 7 (indicating that the last day of 2016 was a Saturday)
To apply this formula to your problem: (assuming that the dates are in row 1 and the values are in row 2)
- insert a new row to hold the
WEEKDAY()value (say, row 2) - in cell A2 type in
=WEEKDAY(A1) - copy this formula as far right as necessary (to include all your dates)
Your average for Mondays is calculated as =AVERAGEIF(2:2, 2, 3:3)
Possibly, you can add a column called [Day Of The Week] and use the following formula to display the day.
TEXT(B4,"dddd")
Then add an 'If'statement to your result cell.