simpler date ranging?

jme52425

New Member
Joined
Apr 5, 2002
Messages
2
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
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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
 
Upvote 0
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)
 
Upvote 0
=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
 
Upvote 0
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!
 
Upvote 0
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)
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,089
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top