Excel formula for day of week

※ Download: Excel formula for day of week You'll also learn how to troubleshoot, trace errors, and fix problems. In this case we will reference the cell that contains the date. After adding that formula you normally have to change the cell formatting back to General. I'm trying to make a calendar to give my police officer son in law for Christmas. As you can see we don't have exact dates in them. Return The Weekday of Date in Excel - Many thanks Alex I have a spread sheet where i have a cell for sum of time worked for Monday through to Thursday N,18 with an adjacent cell O,18 for Friday. Happy to try anyway! Bottom line: Learn a few different ways to return the name of the day or weekday name for cell that contains a date value. Skill level: Beginner In this post we will look at a few different ways to return the day name for a date in Excel. This can be very useful for creating summary reports, pivot tables, and charts. Method 1: The Number Format Drop-down Menu Often times you have a date in a cell and you just want to know the name of the day for the date. The easiest way to see the weekday name is to select the cell, then press the Number Format Drop-down menu button on the Home tab of the Ribbon. The Long Date format shows a preview of the date and includes the name of the day for the date in the selected cell. The keyboard shortcut for the Number Format Drop-down menu is: Alt,H,N,Down Arrow Checkout my article on to learn more about dates. Method 2: The Format Cells Dialog Window We can also see the Long Date format in the Format Cells Window on the Number tab. This window can be opened by right-clicking the cell, then pressing Format Cells… The keyboard shortcut for the Format Cells Window is: Ctrl+1 Click on the Long Date format and the Sample box will show a preview that includes the day of the week. You can also apply a Custom number format to just display the weekday name in the cell. The cell value will still contain the date, but the name of the day will be displayed instead of the date. Method 3: Use the TEXT Function The TEXT function is really handy for converting dates to different text formats. This is great for adding fields to the source data of a pivot table. It can be used as an alternative to the Date Grouping feature of a pivot table. The TEXT function contains two arguments. In this case we will reference the cell that contains the date. The number format should be wrapped in quotes. My friend Dave Bruns at ExcelJet has a great article that explains how to use the in more detail. Use the Weekday Name in the Source Data of a Pivot Table We can then use this technique to add a column to your pivot table source data for the day name. This basically allows us to group dates by the day of the week. The example below shows how I used this technique to analyze the sales trend by the day of the week. With a pivot table you could quickly create a chart or report that shows the total or average sales by weekday for a given period. We could also add a slicer with the day name to filter the pivot table or chart. This would be similar to the technique I explained in the. Checkout my to learn more about these great tools in Excel. What Do You Use the Name of Day For? Well, there are a few quick tips for getting the name of the day for a date. Do you have any other ways to get the weekday name? Please below with any questions or suggestions. Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. Assume that A1 number of month 1 A2 year 2018 and D5 starting date 1 2 3 4 5 6 7 … T F S S M T W…. Please coordinate is any formula there. I need to have another cell E11 use that date to determine what day of the week that date is ie monday, tuesday etc and return text depending on the result. If it matters I have a master sheet that has the today function in it and each day I just make a copy of the master sheet so that the date is B6 is always todays date. I am using excel 2010. Can you help me with how to do this? You can find the number format of any date in the Format Cells menu by first formatting a cell with the date format from the Date tab. Then open the format cells menu again and select the Custom tab. This will show the date format. You can use that in the 2nd argument of the TEXT function. I hope that helps. Let me know if that answers your question. We can use an IF formula for that. Here is what the formula would look like. If it is blank then it returns a blank value represented by double quotes. Here is an that should help. As I work with a student I enter the information to track what I am doing, student needs, courses, programs ect. The problem I have is when I copy the formula down to the blank cells it continues to populate the cells with January and Saturday respectively. So I go from April and Wednesday for the last student I worked with to January and Saturday for a cell that is blank. I have tried to use an if function with this to no luck so far. Does this make sense? Mike - April 14, 2016 Hi Mike, Great question! Apparently January 0, 1900 was a Saturday according to Excel. Anyways, you can use an IF formula for this. If your date is in cell A2, here is the formula. Here are links to articles on the and the , if you want to read more about it. I hope that helps. Let me know if you have any questions. Riaan - December 1, 2016 Hi Jon, I need to work with time sheets and overtime. Saturdays and Sundays carry a different hourly rate. I therefore need to use an IF statement to see of any date falls on a Saturday or Sunday. Your insight will be highly appreciated. You can use the WEEKDAY function for this. The WEEKDAY function returns the day number of the week. If you set this argument to 2 then Monday will be the first day of the week and return a 1. Here is what the formula would look like if cell C3 contains a date. I hope that helps. Apiyo - April 9, 2016 Jon, I have dwonloaded data from SQL database SAP. Ihave created a table and used that as source data for a pivot table. I neededtouse TEXT function to return Day Name as you have just shown upthere. Find a link in post number 31 The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious. I used your formula and it works. To begin with, let's find out a week number corresponding to each sale. How to get the day of month from date DAY function Microsoft Excel provides a special DAY function to retrieve the day of the month from a date: Note. If this workbook is not available, open up a new file for practice. Method 1: The Number Format Drop-down Menu Often times you have a date in a cell and you just want to know the name of the day for the date. I know I have to subtract 1 SAT or 2 SUN but when I put -1 or -2 it doesn't work. The second distinction in weekly reporting is what day is used to represent the entire week.