Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: simpler date ranging?

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    the task sounds simple enough, but is driving me mad-- I have to be able to have a formula calculate and then enter into a sentence the ending date for a quarter. Quarters are not always of equal length, and it cannot be the actual month end date, but rather must be the last workday of the quarter. Ignoring the Concatenation part , and assuming (B1) has the starting date in m/dd/yyy format, and that weekday is operating on a Moday=1 scale, i have the following:

    =IF(WEEKDAY((EOMONTH(B1,2)),2)=6,SUM((B20)-1),IF(WEEKDAY((EOMONTH(B1,2)),2)=7,SUM((B20)-2),SUM(B20)))

    Is there an easier way than this?
    many thanks,
    jim

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,423
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default


    1. use a lookup

    =VLOOKUP(MONTH(B1),$F$2:$H$5,3)

    2. extend the following for the other quarters

    =IF(MONTH(B2)<=3,WORKDAY(DATE(YEAR(B2),4,1),-1),"")

    3. =WORKDAY(DATE(YEAR(B3),3*INT((MONTH(B3)+2)/3)+1,1),-1)



    [ This Message was edited by: Dave Patton on 2002-04-06 21:12 ]

  3. #3
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Jim

    Not too sure i follow, but the CHOOSE Function is very handy for returned numbers between 1 and 30

    =CHOOSE(WEEKDAY(EOMONTH(B1,2)),B20,B20,B20,B20,B20,B20,B20-1,B20-2)

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,423
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    =WORKDAY(EOMONTH(B4,IF(OR(MONTH(B4)={1,4,7,10}),2,IF(OR(MONTH(B4)={2,5,8,11}),1,0)))+1,-1)

    Revise if you just need the information for a date in the last month of the quarter.

    Revise references as necessary.

    An advantage of the workday approach versus the various lookup solutions is that the dates are not fixed to a particular year.

    [ This Message was edited by: Dave Patton on 2002-04-07 08:56 ]

  5. #5
    New Member
    Join Date
    Apr 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    WORKDAY(EOMONTH(B4,IF(OR(MONTH(B4)={1,4,7,10}),2,IF(OR(MONTH(B4)={2,5,8,11}),1,0)))+1,-1)

    works great-- i didn't realize/didn't even think about workday as a method of calculating. thanks again!

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,423
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default


    If you just want the date if the day in B is in the last month of the quarter and revising your approach, consider

    =IF(OR(MONTH(B1)={3,6,9,12}),EOMONTH(B1,0)-(WEEKDAY(EOMONTH(B1,0),2)=7)*2-(WEEKDAY(EOMONTH(B1,0),2)=6)*1)

  7. #7
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi jme52425 and Dave Patton:
    On 2002-04-07 09:00, Dave Patton wrote:

    If you just want the date if the day in B is in the last month of the quarter and revising your approach, consider

    =IF(OR(MONTH(B1)={3,6,9,12}),EOMONTH(B1,0)-(WEEKDAY(EOMONTH(B1,0),2)=7)*2-(WEEKDAY(EOMONTH(B1,0),2)=6)*1)
    Dave:
    I did not quite follow your use of an array within the formula -- if you would please shed some light on it.
    Also, how about the following formula, wherein I have made use of the mround function:

    =WORKDAY(DATE(YEAR(B1),MROUND(MONTH(B1)+1,3)+1,0),0)

    jme52425: Please post back if it works for you ... otherwise explain a little further and let us take it from there!
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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