Last Saturday of the Month

[AdminCo]

Hello,

I am trying to get cell A4 to return the last Saturday of the month based on the date in cell A1 being the month i.e. September 01, 2003. Then in cell A2 to show the Friday just over one week before the last Saturday (8 days).

Cell A1 = September 1, 2003 (Entered)
Cell A2 = September 19, 2003 (Formula)
Cell A4 = September 27, 2003 (Formula)

Any help would be appreciated.

Thanks,
Alan

 

[Aladin Akyurek]

In A2 enter:

=A4-8

In A4 enter:

=A1+7-WEEKDAY(A1)+(7<WEEKDAY(A1))*7+(4-1)*7

where 7 stands for Saturday and 4 4th Saturday.

 

[AdminCo]

Hello Aladin,

Thanks for your response Aladin. I understand Cell A2 looking at Cell A4 and backing up 8 days. So if Cell A4 equalled Sep 27 then Cell A2 would be 8 days less producing Sep 19.

What I am not sure is the formula in cell A4. Cell A4 is looking at Cell A1 which in the example is showing Sep 1, 2003. Somehow cell A4 needs to find the last Saturday of the month based on the first of a given month. In 2003 there are four months that have five Saturdays instead of four. What would have to be done for the formula in cell A4 to find the last Saturday of any given month in any given year where if could be four or five Saturdays in a month? Would "eomonth" maybe have to be used?

 

[Aladin Akyurek]

Hello Aladin,

Thanks for your response Aladin. I understand Cell A2 looking at Cell A4 and backing up 8 days. So if Cell A4 equalled Sep 27 then Cell A2 would be 8 days less producing Sep 19.

What I am not sure is the formula in cell A4. Cell A4 is looking at Cell A1 which in the example is showing Sep 1, 2003. Somehow cell A4 needs to find the last Saturday of the month based on the first of a given month. In 2003 there are four months that have five Saturdays instead of four. What would have to be done for the formula in cell A4 to find the last Saturday of any given month in any given year where if could be four or five Saturdays in a month? Would "eomonth" maybe have to be used?

A4 houses:

=A1+7-WEEKDAY(A1)+(7 < WEEKDAY(A1))*7+(4-1)*7

 

[AdminCo]

Sorry Aladin. If I put November 1, 2003 in cell A1 it returns November 22 in cell A4 and November 14 in cell A2. What I need it to return is Nov 29 in cell A4 and Nov 21 in cell A2. I am trying to find the last Saturday of the month in order to have the formula work... ray: ray:

 

[Aladin Akyurek]

Sorry Aladin. If I put November 1, 2003 in cell A1 it returns November 22 in cell A4 and November 14 in cell A2. What I need it to return is Nov 29 in cell A4 and Nov 21 in cell A2. I am trying to find the last Saturday of the month in order to have the formula work... ray: ray:

=A1+7-WEEKDAY(A1)+(7 < WEEKDAY(A1))*7+(SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&EOMONTH(A1,0))))=7))-1)*7

 

[rrdonutz]

Not the most elegant, but it seems to work:

=SUM(IF(WEEKDAY(EOMONTH(A1,0)-{0,1,2,3,4,5,6})=7,EOMONTH(A1,0)-{0,1,2,3,4,5,6}),0)

--Tom

 

[AdminCo]

Hello Aladin,

If I enter =a1+7-WEEKDAY(a1)+(7) in cell A4 the date produced in cell A4 is November 8th thus making cell A2 return October 31. I'm not clear what I am supposed to do as the date in cell A4 should reflect November 29 which is the last Saturday in November 2003 and November 21 in cell A2 which is the week before on the Friday.

Alan

 

[Aladin Akyurek]

Hello Aladin,

If I enter =a1+7-WEEKDAY(a1)+(7) in cell A4 the date produced in cell A4 is November 8th thus making cell A2 return October 31. I'm not clear what I am supposed to do as the date in cell A4 should reflect November 29 which is the last Saturday in November 2003 and November 21 in cell A2 which is the week before on the Friday.

Alan

See the edited post. Also Tom's.

 

[AdminCo]

Thanks Gentlemen,

Both formulas work just fine. I appreciate all your heip.