It may include a few days from last year’s end. I’ve deduced that number in my formula and added one to get a whole seven days week in the year start. You May Also Like: Free Google Sheets Calendar Template Download and How To The Weekday formula would return the number 1 for Sunday, 2 for Monday, and so on by default (please check my note above). With the help of the WEEKDAY function, we can find the number representing the day of the week of the first day in the provided year. In this, there are only six days in the first week. That causes the problem in the above calculations. So in the first week, the number of days may or may not be 7. The week starts on the 1st day of the year, irrespective of Sunday, Monday, or any other day. Note:- To get Monday to Sunday, use the “type” argument within the WEEKDAY(date, type) in my formula above. Usually, one whole week is from Sunday to Saturday.īut for the first week in a year, it may or may not be from Sunday to Saturday. Tips: How to Find the Current Month’s Week Number In Google Sheets What’s that? You may check my date-related function tutorial below, in which I’ve marked the week number in January on a calendar screenshot. Then what about the below formula? =DATE(A2,1,1)+(6*7) It would probably return the last date in week 7, not the starting date. This formula would return the date, which is wrong! Why? In this formula, the first number 7 in the multiplication factor represents the week number 7, and the second number 7 represents the number of days in a week. Some of you may think that a formula like the below one can find the date from the week number in Google Sheets. Then I have the week number in B1, which is # 7. This formula will return the year since the year is 2018 in cell A1. With that year, we can get the very first date in that year using the below formula. Some of you may want to learn how the above formula 1 works. Before going to that, here is the above formula logic. To get the end date, add # 6, i.e., equal to 6 days, to the first formula result. Here is my above-said formula in cell D2, which can help you to get the start date from a given week number.įormula 1: =MAX(DATE(A2,1,1), DATE(A2,1,1)-(WEEKDAY(DATE(A2,1,1))-1)+(B2-1)*7)īelow is the second formula in cell E2 that can use to find the date from the week number but the last date in the week number period.įormula 2: =MIN(DATE(A2,12,31), DATE(A2,1,1)-(WEEKDAY(DATE(A2,1,1))-1)+(B2-1)*7 +6)īoth of the above formulas (bolded part) are almost the same (I’ll explain the MAX/MIN part later). How to Find the Date or Date Range from Week Number in Google Sheets
0 Comments
Leave a Reply. |