simpler date ranging?

[jme52425]

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

[Dave Patton]

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 ]

[Dave Hawley]

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)

[Dave Patton]

=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 ]

[jme52425]

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!

[Dave Patton]

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)

[Yogi Anand]

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!