formula for first saturday of quarter

[sher11a]

the below formula works but i need to add months 6 and 9 (this would be the
first saturday of the quarter. I can't seem to add another month for some
reason but i am a newbie.

=IF(OR(MONTH(E1)=1,AND(MONTH(E1)=3,DAY(E1)<8)),"*ICCFQRTR"," ")

 

[Mike H]

Hi,

Maybe this. Returns the date of the first saturday of the quarter of a date
in E1

=DATE(YEAR(E1),(CHOOSE(ROUNDUP(MONTH(E1)/3,0),1,4,7,9)),1)+CHOOSE(WEEKDAY(DATE(YEAR(E1),(CHOOSE(ROUNDUP(MONTH(E1)/3,0),1,4,7,9)),1)),6,5,4,3,2,1,0)

Mike

 

[Mike H]

OOPS,

Q3 begins in October not September, try this instead

=DATE(YEAR(E1),(CHOOSE(ROUNDUP(MONTH(E1)/3,0),1,4,7,10)),1)+CHOOSE(WEEKDAY(DATE(YEAR(E1),(CHOOSE(ROUNDUP(MONTH(E1)/3,0),1,4,7,10)),1)),6,5,4,3,2,1,0)

 

[T. Valko]

A few keystrokes shorter:

=DATE(YEAR(A1),INT((MONTH(A1)+2)/3)*3-2,1)+CHOOSE(WEEKDAY(DATE(YEAR(A1),INT((MONTH(A1)+2)/3)*3-2,1)),6,5,4,3,2,1,0)

 

[Ron Rosenfeld]

A few keystrokes shorter:

=DATE(YEAR(A1),INT((MONTH(A1)+2)/3)*3-2,1)+CHOOSE(WEEKDAY(DATE(YEAR(A1),INT((MONTH(A1)+2)/3)*3-2,1)),6,5,4,3,2,1,0)

And even shorter:

=DATE(YEAR(A1),INT((MONTH(A1)-1)/3)*3+1,0)-WEEKDAY(DATE(YEAR(A1),INT((MONTH(A1)-1)/3)*3+1,0))+7

--ron

 

[Rick Rothstein]

And for a different (shorter and one function call less) approach...

=DATE(YEAR(A1),3*INT((MONTH(A1)-1)/3)+1,8-WEEKDAY(DATE(YEAR(A1),3*INT((MONTH(A1)-1)/3)+1,1)))

 

[Rick Rothstein]

I'm guessing this ought to spark a how short can we get it war.... my
formula is two character shorter (with the same number of function
calls).<g>

 

[T. Valko]

I'm guessing this ought to spark a how short can we get it war

<BG>

Let's do it!

 

[Mike H]

Perhaps,

=DATE(YEAR(A1),1,1)+7-WEEKDAY(DATE(YEAR(A1),1,1),1)+(13*7)*(ROUNDUP(MONTH(A1)/3,0)-1)


Mike

 

[Mike H]

Even better, I had a mindset of 13*7 but 91 will do

=DATE(YEAR(A1),1,1)+7-WEEKDAY(DATE(YEAR(A1),1,1),1)+(91)*(ROUNDUP(MONTH(A1)/3,0)-1)

Mike

 

[Mike H]

Please don't linewrap

=DATE(YEAR(A1),1,8)-WEEKDAY(DATE(YEAR(A1),1,1),1)+(91)*(ROUNDUP(MONTH(A1)/3,0)-1)

 

[Mike H]

Definately my last effort

=DATE(YEAR(A14),1,8)-WEEKDAY(DATE(YEAR(A14),1,1),1)+91*(ROUNDUP(MONTH(A14)/3,0)-1)

Mike

 

[Rick Rothstein]

Using your formula "layout", but substituting string and math manipulations
for some of the function calls yields this much smaller formula...

=7+("1/1/"&YEAR(A1))-WEEKDAY("1/1/"&YEAR(A1))+91*INT((MONTH(A1)-1)/3)

 

[Mike H]

Damn,

I thought I had that one, <BG>.

Mike

Using your formula "layout", but substituting string and math manipulations
for some of the function calls yields this much smaller formula...

=7+("1/1/"&YEAR(A1))-WEEKDAY("1/1/"&YEAR(A1))+91*INT((MONTH(A1)-1)/3)