In the first argument of my formula, when it was negative, I added 7. The key is in adjusting the +7 calculation. For that, I’m going to create a named range called WEEK. If I want it to be more flexible than that, I need to include the week number in my calculation. The above formula works if I want the first week. But I need to adjust the formula to ensure the calculation puts me in the correct week. 3, 2022, was the first Monday of the year, which is correct. Using the above formula, Excel tells me that Jan. To get to the right day, I need to add this to my starting date: =FIRSTDAY+IF(DAYNUMBER-WEEKDAY(FIRSTDAY)<0,DAYNUMBER-WEEKDAY(FIRSTDAY)+7,DAYNUMBER-WEEKDAY(FIRSTDAY)) Here is my formula thus far: =IF(DAYNUMBER-WEEKDAY(FIRSTDAY)<0,DAYNUMBER-WEEKDAY(FIRSTDAY)+7,DAYNUMBER-WEEKDAY(FIRSTDAY)) And if it isn’t negative, then I can just add to the starting date. What I need to do is to set up an IF function to say that if the difference is negative, I will add 7 to adjust for that fact. If I wanted to know when the first Monday of the year was, I couldn’t just deduct 5 from the first day or I’d end up in the wrong year. The formula for that would be as follows: =DAYNUMBER - WEEKDAY(FIRSTDAY) If my desired day is Monday, then I need to calculate the difference between those days of the week. The day the year begins on serves as an important starting point. Calculating the difference between the first day and your desired day of the week If you’re not sure about yours, you can use the WEEKDAY function on a day that is a Monday (or whichever day you wish to use) to determine the number associated with that. On my regional settings, that is weekday #2. I’m going to use Monday as the day of the week my week starts on. The two names ranges I’m going to set up are for the day of the week (DAYNUMBER) that I want to calculate for, and the first day of the year (FIRSTDAY). You can make this into a large and complex formula, but I’m going to make it a bit more organized by utilizing named ranges.
0 Comments
Leave a Reply. |