If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Week ending Thursday formula
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 |
#2
|
|||
|
|||
Week ending Thursday formula
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 |
#3
|
|||
|
|||
Week ending Thursday formula
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 . |
#4
|
|||
|
|||
Week ending Thursday formula
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 |
#5
|
|||
|
|||
Week ending Thursday formula
On Fri, 16 Apr 2010 14:40:35 +0200, "Laurence Lombard"
wrote: Thanks very much Ron. Laurence Glad to help. --ron |
Thread Tools | |
Display Modes | |
|
|