Hi Laurence,
I understand that you are looking for an elegant solution for a week ending Thursday (or any other day) formula.
You are correct that the formula you have been using for "week ending Friday" is
A1+6-MOD(A1,7), which gives the first following Friday in which the date falls.
To get the week ending on Thursday, you can modify this formula slightly. Instead of adding 6, you would add 4 to the date in A1. This is because Thursday is four days before Friday. So the formula for week ending Thursday would be:
- =A1+4-MOD(A1,7)
This formula should work for any day of the week. You just need to adjust the number you add to the date based on how many days before the end of the week your desired day falls.