Excel 2010 has new return_type arguments for the WEEKDAY Function. How useful they are is debatable, but you never know when you might come across a particular need.
Excel Weekday Function
The Excel WEEKDAY Function in has two arguments: serial_number and return_type. The serial_number argument is simply a number that represents a date. The return_type argument is optional, and assumed to be 1 if omitted.
Versions prior to Excel 2010 have only the first three return_type arguments: 1,2 3. The new arguments are 11-17, representing differing combinations for the starting day of the week.
The Excel WEEKDAY Function returns an integer value 1-7, based upon the day of the week, with the exception of return_type 3 that returns 0-6.
The Excel WEEKDAY Function Arguments dialog box doesn’t show the new arguments.
However in Excel 2010, when you enter the WEEKDAY Function into a cell, they are shown as you enter the second argument.
Weekly Reporting and the Excel WEEKDAY Function
I’ve only used the WEEKDAY Function with regards to reporting data, and then only sporadically.
Companies normally have weekly reporting requirements. However, while every week has seven days, there are two important distinctions. First is the start and end days of each week. Some companies use a Sunday-Saturday week, others a Monday-Sunday. These two are the most common, although I came across one company that had a Saturday-Friday reporting week.
The second distinction in weekly reporting is what day is used to represent the entire week. In the USA it’s common to use a week-ending date for weekly reports, while in Europe a week-commencing date is used quite frequently.
I won’t go into specific uses of the WEEKDAY Function for reporting in this post, but the two distinctions mentioned for weekly reporting make it a handy function to know.
I’m trying to make a formula that calculates days late on shipped orders without adding Sat & Sun in the calculation. IE: Order Due Date is 12-14-12 and was shipped on
12-17-12. Order is 1 day late.
Thanks, Bill
Assume Order Due is in cell A2 and the value is 12-14-12. Assume Order Ship is in cell B2 and the value is 12-17-12. The formula for Days Late, which I put in cell C2, is =NETWORKDAYS(A2,B2)-1 and gives the result of 1. The NETWORKDAYS function excludes weekend days. You have to subtract 1 because if the start and end dates are the same the function returns a 1. So if Order Due and Order Ship are both 12/14/12 then the Days Late value is zero.
After adding that formula you normally have to change the cell formatting back to General. You’ll know to change the cell formatting if the answer is 1-1-00.
Hello,
I have a start date and a completion date which i would like to calculate Monday to Friday days only between the two dates. Start date 18/03/2013 (Cell R2) i would like to calculate Monday to Friday from this date in R2 to be the completion date in cell W2. Please help 🙂
Monday to Friday days only are weekdays, which are associated with the NETWORKDAYS Function. Per Excel Help: (It) Returns the number of whole working days between start_date and end_date.
Your formula is:
=NETWORKDAYS(R2,W2)
I want to use weekday to work out a shift pattern. Is there a formula where I can just put in the shift pattern and switch the number (week start day) so that I can work out the optimum shift pattern that gives even weekend work days between shifts.
Check out the Help section of the WEEKDAY function where the second argument can change the week start day for the formula. In later versions of Excel for Windows (2007, 2010, 2013) you can cycle through the entire week for start days. In Excel for Mac you are restricted to the following:
1 or omitted returns numbers 1 (Sunday) through 7 (Saturday). Behaves like earlier versions of Excel.
2 returns numbers 1 (Monday) through 7 (Sunday).
3 returns numbers 0 (Monday) through 6 (Sunday).
I report sales by normal sales day (M-F) but occasionally get an invoice on weekends which throws off average days sales, etc. I would like to be able to determine if the date is a Sat or Sun and if so make it a Friday (if Sat) or Monday (if Sun) BUT – there’s always a but!- never change the calendar month (so Sat would be Monday if it is the 1st and Sunday would be Friday if Monday is the 1st.) Confused yet?
Not confuse, but busy. Yet this bugs me so I spent some time to figure it out. Assume you have date in cell A2. Here is the formula you are looking for:
=IF(WEEKDAY(A2,2)<6,A2,IF(WEEKDAY(A2,2)=6,IF(MONTH(A2)=MONTH(A2-1),A2-1,A2+2),IF(WEEKDAY(A2,2)=7,IF(MONTH(A2)=MONTH(A2+1),A2+1,A2-2))))
I tested it and it works beautifully.
I have list of dates in Column C, I would like Column B to show me the week ending date (Sunday) So I can select the individual week. At present I can only find a formula that gives me the week number rather than a day & date.
The formula you are looking for is:
=C1-WEEKDAY(C1,2)+7
which will give you a week ending date that is always on Sunday.
Thank you! This is exactly what I needed. 🙂
Can I also use a similar formula to show the date as the first of each month so I can group them by month?
You can use this to show the date of the first day of the month.
=DATE(YEAR(C1),MONTH(C1),1)
I want to put a schedule together starting on Monday, June 24th, 2013 (column A5), then I want to add one weekday in (A7). I cannot get the formula to work out.
I don’t know what your formula is, but if you put =A5+1 in cell A7 you will get Tuesday, June 25th, 2013.
That is correct, but if A% is a Friday, June 28th, and I use the formula =A5+1 in A7, I will get Saturday, June 29th, but I want it to = Monday, July 1st.
Hi All,
I have one query could you please help me?
In Excel A column has the date and B column comes the day autommatically so how can i will get weeknum in C column in. Please assist.
Regards,
Mujeeb
The week number can be obtained by using the WEEKNUM function in Column C. Assume 7/7/13 is in cell A1. Then if you enter =WEEKDAY(A1) into cell C1 the result is 28, which represents the 28th week of the year. January 1 resides in the first week of the year in this function.
This function also assumes that weeks start on Sunday and end on Saturday. You can change that to start on Monday and end on Sunday, by adding a 2 to the second argument. Given the previous example, the formula would change to be =WEEKNUM(A1,2) and would return the value 27.