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

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:

1. =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.

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

Hi,

This seems pretty versatile

=A1+7-WEEKDAY(A1+1)

The last number (1) is the significant number. In the range 1 to 7. A 1
returns Friday a 2 Thursday etc


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"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


.

Thanks very much Ron.
Laurence

"Ron Rosenfeld" wrote in message
...
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

On Fri, 16 Apr 2010 14:40:35 +0200, "Laurence Lombard"
wrote:

Thanks very much Ron.
Laurence

Glad to help.
--ron