# date of last friday of previous month

1. ## date of last friday of previous month

I am trying to figure out how to compute the following:

user enters in a date in A1.
B1 needs to calculate the month prior.
C1 needs to calculate the last friday of the month prior to B1.

so if A1 is 11/14/2005,
B1 should be October 2005
C1 should be 9/30/2005

I know how to do B1. I am having a hard time with C1.

i currently have:
TEXT((6-WEEKDAY(EOMONTH(A1,-2)-7))+(EOMONTH(A1,-2)-7),"m/d/yyyy")

This works as long as that last friday is not the last day of the month - which is not good enough. I am sure there is also a better and easier way to do it than the way i did it above. So any assistance would be appreciated.

2. ## Re: date of last friday of previous month

On Mon, 14 Nov 2005 09:52:36 -0600, tkaplan
<tkaplan.1yhulo_1131983716.8627@excelforum-nospam.com> wrote:

>
>I am trying to figure out how to compute the following:
>
>user enters in a date in A1.
>B1 needs to calculate the month prior.
>C1 needs to calculate the last friday of the month prior to B1.
>
>so if A1 is 11/14/2005,
>B1 should be October 2005
>C1 should be 9/30/2005
>
>I know how to do B1. I am having a hard time with C1.
>

One problem -- 30 Oct 2005 was a Sunday. Do you want the last Sunday of the
prior month or the last Friday.

B1: =A1-DAY(A1)
Format as: mmmm yyyy

C1: =B1+1-WEEKDAY(B1+2)
will give the last Friday of the month prior to the month in A1

If you want the last Sunday of the month, then:

C1: =B1+1-WEEKDAY(B1)

--ron

3. i need the last friday of the month prior to the month in b1.

september 30 was a friday. if a1 is 11/14/05, i need b1 to say October 2005, and c1 to say 9/30/2005.
b1 i know how to do, i am having a problem with C1.

4. ## Re: date of last friday of previous month

On Mon, 14 Nov 2005 09:52:36 -0600, tkaplan
<tkaplan.1yhulo_1131983716.8627@excelforum-nospam.com> wrote:

>
>I am trying to figure out how to compute the following:
>
>user enters in a date in A1.
>B1 needs to calculate the month prior.
>C1 needs to calculate the last friday of the month prior to B1.
>
>so if A1 is 11/14/2005,
>B1 should be October 2005
>C1 should be 9/30/2005
>
>I know how to do B1. I am having a hard time with C1.
>

B1: =A1-DAY(A1)

C1: =B1-DAY(B1)+1-WEEKDAY(B1-DAY(B1)+2)

--ron

5. ## Re: date of last friday of previous month

I got results with this formula in C1:
=B4-MOD(WEEKDAY(B4)+1,7)

6. ## Re: date of last friday of previous month

On 14 Nov 2005 08:50:47 -0800, "Dave O" <CycleZen@yahoo.com> wrote:

>I got results with this formula in C1:
>=B4-MOD(WEEKDAY(B4)+1,7)

1. You misread the same as I did. The OP wants the last Friday of the month
PRIOR to the month in *B4*.

2. The MOD function seems superfluous as WEEKDAY effectively performs a MOD
function by itself.

=B4+1-WEEKDAY(B4+2)

will return the same value as your formula.

--ron

7. thank you ron. that worked

8. ## Re: date of last friday of previous month

On Mon, 14 Nov 2005 11:39:16 -0600, tkaplan
<tkaplan.1yhzgm_1131990005.7482@excelforum-nospam.com> wrote:

>
>thank you ron. that worked

--ron

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1