On Fri, 16 Apr 2010 13:43:27 +0200, "Laurence Lombard"
wrote:

I am looking for an elegant solution for a week end Thursday (or any other
day) formula:

Up to now I have successfully used the following formula for "week ending
Friday" with date in A1: A1+6-MOD(A1,7) in other words this formula gives
the first following Friday in which the date falls.

Now I wanted to have my week ending Thursday and I changed the formula to
=A1+5-MOD(A1,7). All the days work correctly except Friday - the Friday
belongs to the week ending the Thursday before, not the following Thursday
(which figures, I worked out later, as it is basically the Friday formula
less one day)

The only way I could get it to work was to use the unwieldy formula which is
captures the special case of Friday with an if function

=IF(MOD(A1,7) = 6, A1 + 6, A1 + 5 - MOD(A1,7)

Interestingly the function WEEKDAY(A1,1) gives the same values as MOD(A1,7)
except for Saturdays where the formulas give 7 and 0 respectively.

is there a more elegant solution, and that would work for week ending "any
day of the week"?

Any help would be appreciated
Thanks
Laurence


In general:

=A1+7-WEEKDAY(A1-DOW)

where DOW: Sun=1, Mon=2, ...

So, for Thursday:

=A1+7-WEEKDAY(A1-5)

--ron