+ Reply to Thread
Results 1 to 8 of 8

date of last friday of previous month

  1. #1
    Forum Contributor
    Join Date
    05-04-2005
    Posts
    136

    Question 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.

    Thanks in advance
    Last edited by tkaplan; 11-14-2005 at 11:58 AM.

  2. #2
    Ron Rosenfeld
    Guest

    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.
    >
    >Thanks in advance


    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. #3
    Forum Contributor
    Join Date
    05-04-2005
    Posts
    136
    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. #4
    Ron Rosenfeld
    Guest

    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.
    >
    >Thanks in advance



    My misreading. Try:

    B1: =A1-DAY(A1)

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


    --ron

  5. #5
    Dave O
    Guest

    Re: date of last friday of previous month

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


  6. #6
    Ron Rosenfeld
    Guest

    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. #7
    Forum Contributor
    Join Date
    05-04-2005
    Posts
    136
    thank you ron. that worked

  8. #8
    Ron Rosenfeld
    Guest

    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


    You're welcome. Glad to help.


    --ron

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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