![]() When you inspect the data, you will notice that when the 1 st of the month falls on a weekend, the first weekday of that month starts as week #2 and skips week #1: The week number is now blank when the date falls on a weekend. We can then use an IF() statement to check if the value returned is bigger than 5 (Friday).Īfter integrating the IF() statement, the formula now becomes: Cell C5 = IF(WEEKDAY(B5,2)>5,””, WEEKNUM(B5)-WEEKNUM(DATE(YEAR(B5),MONTH(B5), 1)) + 1) In this case, for simplification, use 2 – Numbers 1 (Monday) through 7 (Sunday), which will return the number 6 and 7 for weekends. return_type – tells what number to return for specific days.īy default it uses 1 – Numbers 1 (Sunday) through 7 (Saturday).serial_number – the date you are checking, B5.The syntax of the WEEKDAY() function is: = WEEKDAY(serial_number, return_type] Use a combination of the IF() and WEEKDAY() functions here. Now all that’s left to do is to not display anything when a date falls on a weekend. Cell C5 = WEEKNUM(B5)-WEEKNUM(DATE(YEAR(B5),MONTH(B5), 1)) + 1Īpply the formula down to the last row and now you have the correct week number for February 1. You will again notice that the week now starts counting at 0.Īdd a 1 to the formula to address this. The number 1 is used since we want to find the serial number of the 1 st day of that month so that we can eventually find its corresponding week number. We will be using a combination of the DATE() function to find the serial number of the 1 st of a specific date, and the YEAR() MONTH() functions to find what year and month it should use. Subtract the current week number with the week number of the 1 st of the month and increment it as it goes. Whenever a new month is started, the week counter should reset back to 1. To understand how to fix this, we will go through it step by step. In this example, we want February 1 to be counted as week 1 of February instead of week 5 of January. However, this does not count the 1 st of the month as a new week when it doesn’t fall on a Monday. You will notice that the values rum from week 1 to 53 and then resets to week 1 when it starts a new year. The formula for cell C5 now becomes: Cell C5 = WEEKNUM(B5)ĭrag the formula down to the last row (or double click on the lower right corner of the cell). You will have an option to pick any way of the week. serial_number – this is the date you want to convert.Its syntax is: = WEEKNUM(serial_number, ) Case 1: Using the WEEKNUM() function to start a new week every 1st of the monthĮxcel’s WEEKNUM() function is used for Case 1. While Case 2 starts counting the first full week of the month starting on a Monday. if the 1 st day of the month falls on a Friday, or if the last day of the month falls on a Monday). ![]() In Case 1, it is possible to have a week with only 1 weekday (e.g. Case 2: February 1 is still counted as Week 5 of January.Case 1: February 1 is counted as Week 1 for February.Taking the end of January and the beginning of February as an example, where January 31 is on a Tuesday and February 1 falls on a Wednesday: ![]() Whichever case you are going with for, the report will decide the approach to count the week number. Other format type are yyyy for year and mmmm for the month. In this example, we will use “dddd” to display the weekday (Sunday, Monday, etc). Januis equivalent to 1 and as each day passes, it increments by 1. To give you an idea, dates are pure numbers. In the Type, you can choose however you want the data to be displayed. Press CTRL + 1 to display the Format Cells window.Īlternatively, you can right click on the cell and select Format Cells. To set this up, equate it to the date and merely change the formatting of the cell. Take this data set which has the dates from January 1, 2017, to the beginning of the year 2018 as an example: Setting up the dataįor the sake of demonstration and checking, I have used a Weekday column (A) to show that there is no week count for days that fall on weekends. There are different approaches to creating reports which involve getting the week numbers for each month.īefore jumping in and creating the report, you will have to think about how you’ll handle the cutoff week.ĭo you always want to start in week 1 the moment you get to the first of the month?ĭo you consider the last days of January and the first days in February to all be in week 5? ![]()
0 Comments
Leave a Reply. |