How.com.vn is a “wiki,” similar to Wikipedia, which means that many of our articles are co-written by multiple authors. To create this article, 17 people, some anonymous, worked to edit and improve it over time.
This article has been viewed 558,646 times.
You've just entered a bunch of dates into your Excel spreadsheet, but what you really want to see is what day of week those dates happen on. Fortunately, Excel makes it easy to calculate the day of week with a simple formula. With a bit of handiwork, you can get the abbreviated or full weekday name. You'll just have to know the appropriate excel shortcut: =TEXT((A1), "ddd")
Steps
Enter a date reference in a cell. For this example, we'll use the date "11/7/2012." In A1, enter that date.
Calculate the abbreviated weekday name. In cell B1, enter =TEXT((A1), "ddd") into the cell or formula field.- The "ddd" setting tells Excel to use the first three letters of the weekday name. In this example, "ddd" becomes "Wed".
Calculate the full weekday name. In cell C1, enter =TEXT((A1), "dddd").- This will calculate the full weekday name.
- To add additional date info, use the following conventions, in any order:
- Time: hh:mm:ss will give you the full time. You can also enter any part of that for more abbreviated time displays.
- Day of week: as described above, ddd gives you the abbreviated day name, and dddd gives you the full day name.
- Date: dd will give you the date with a leading zero for the 1st through the 9th. A single d will drop the leading zero.
- Month: mmm will give you the abbreviated month, and mmmm will give you the month spelled out.
- Year: For just the decade, use yy. For the complete year, use yyyy.
- For example, to have field A1 (as above) read as "Wed, 7 Nov., 2012" you would enter "=TEXT((A1), "ddd, d mmm., yyyy"). Make sure you include the quotes, and that your parentheses are balanced (as many open ones as closed ones).
Advertisement
Community Q&A
- QuestionWhat is the calculation of 30 to 31 weeks?
Community AnswerLet's say you have a date in cell A1: In B1 type: =weekday(A1;2). To find which day of the month type: =day(A1). For day of the year type : =day360(A1). To find out the week of the year type: =weeknum(A1;2). - QuestionWhat code would I use for the column to automatically do this all the way down when dates are entered into another column?
Community AnswerSimply replicate the formula down the column if using the "A1" cell as started. Otherwise, alter the formula accordingly.
Video
Tips
- Rather than typing in a cell reference (such as A1, above, to reference the date cell), you can simply click on that cell after typing "=TEXT("Thanks
- Rather than write a formula, you can just format the cell containing the date as follows:Thanks
Things You'll Need
- Microsoft office package(excel sheet)
- Desktop or phone using excel
- Microsoft excel knowledge
Is this article up to date?
⚠️ Disclaimer:
Content from Wiki How English language website. Text is available under the Creative Commons Attribution-Share Alike License; additional terms may apply.
Wiki How does not encourage the violation of any laws, and cannot be responsible for any violations of such laws, should you link to this domain, or use, reproduce, or republish the information contained herein.
- - A few of these subjects are frequently censored by educational, governmental, corporate, parental and other filtering schemes.
- - Some articles may contain names, images, artworks or descriptions of events that some cultures restrict access to
- - Please note: Wiki How does not give you opinion about the law, or advice about medical. If you need specific advice (for example, medical, legal, financial or risk management), please seek a professional who is licensed or knowledgeable in that area.
- - Readers should not judge the importance of topics based on their coverage on Wiki How, nor think a topic is important just because it is the subject of a Wiki article.
