Week ending Thursday formula

[Laurence Lombard]

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

 

[Ron Rosenfeld]

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

 

[Mike H]

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]

Thanks very much Ron.
Laurence

In general:

=A1+7-WEEKDAY(A1-DOW)

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

So, for Thursday:

=A1+7-WEEKDAY(A1-5)

--ron

 

[Ron Rosenfeld]

Thanks very much Ron.
Laurence

Glad to help.
--ron