![]() ![]() ![]() This formula uses WEEKDAY to get an index for the day of week, and CHOOSE to fetch a roll back value. ![]() If you want to customize behavior based on the day of week, you use an alternative formula that uses the CHOOSE function with hard-coded adjustment values: =B5-CHOOSE(WEEKDAY(B5,2),0,1,2,3,4,5,6) This formula will continue to update on an on-going basis. Here, we are using the TODAY function to inject the current date into the same formula. To get the Monday of the current week, you can use this formula: =TODAY()-WEEKDAY(TODAY(),3) The formula simply exploits this behavior directly: =B5-WEEKDAY(B5,3) In other words, we can use WEEKDAY to generate the roll back values in the table above for any given date. By setting the optional second argument (return_type) to 3, WEEKDAY will return numbers 0-6 for a Monday-based week. WEEKDAY returns a number, normally 1-7 for each day of the week. It turns out that the WEEKDAY function, with a small adjustment, can give us the rollback number we need. How can we figure out the the roll back number? ![]() If the date is a Wednesday, you need to roll back 2 days, if the date is a Friday, roll back 4 days, and so on, as seen in the table below: Date You can see you will need to "roll back" a specific number of days, depending on what day of the week the given date is. Imagine you have any random date and want find the Monday of the week in which the date appears. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |