Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default formula for first saturday of quarter

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)),"*I CCFQRTR"," ")
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,501
Default formula for first saturday of quarter

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),(CH OOSE(ROUNDUP(MONTH(E1)/3,0),1,4,7,9)),1)),6,5,4,3,2,1,0)

Mike

"sher11a" wrote:

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)),"*I CCFQRTR"," ")

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,501
Default formula for first saturday of quarter

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),(C HOOSE(ROUNDUP(MONTH(E1)/3,0),1,4,7,10)),1)),6,5,4,3,2,1,0)

"Mike H" wrote:

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),(CH OOSE(ROUNDUP(MONTH(E1)/3,0),1,4,7,9)),1)),6,5,4,3,2,1,0)

Mike

"sher11a" wrote:

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)),"*I CCFQRTR"," ")

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default formula for first saturday of quarter

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)

--
Biff
Microsoft Excel MVP


"Mike H" wrote in message
...
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),(C HOOSE(ROUNDUP(MONTH(E1)/3,0),1,4,7,10)),1)),6,5,4,3,2,1,0)

"Mike H" wrote:

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),(CH OOSE(ROUNDUP(MONTH(E1)/3,0),1,4,7,9)),1)),6,5,4,3,2,1,0)

Mike

"sher11a" wrote:

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)),"*I CCFQRTR"," ")



  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,651
Default formula for first saturday of quarter

On Sun, 8 Mar 2009 15:19:27 -0400, "T. Valko" wrote:

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


  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,934
Default formula for first saturday of quarter

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

--
Rick (MVP - Excel)


"Ron Rosenfeld" wrote in message
...
On Sun, 8 Mar 2009 15:19:27 -0400, "T. Valko"
wrote:

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


  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,934
Default formula for first saturday of quarter

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 (MVP - Excel)


"T. Valko" wrote in message
...
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)

--
Biff
Microsoft Excel MVP


"Mike H" wrote in message
...
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),(C HOOSE(ROUNDUP(MONTH(E1)/3,0),1,4,7,10)),1)),6,5,4,3,2,1,0)

"Mike H" wrote:

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),(CH OOSE(ROUNDUP(MONTH(E1)/3,0),1,4,7,9)),1)),6,5,4,3,2,1,0)

Mike

"sher11a" wrote:

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)),"*I CCFQRTR"," ")




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
I NEED A FORMULA THAT TAKES THE DATE AND CONVERT IT IN A QUARTER jcm Excel Worksheet Functions 1 November 30th 07 09:25 PM
Formula - Period Quarter End Sam Excel Worksheet Functions 4 August 27th 07 03:34 AM
Excel should have a QUARTER formula for DATE calculations Jeff Stubing Excel Worksheet Functions 5 October 9th 06 04:27 AM
Formula for Calendar Quarter Robert Excel Worksheet Functions 2 August 3rd 06 04:52 AM
Formula to fill inthe Quarter gls858 New Users to Excel 7 June 16th 05 05:53 PM


All times are GMT +1. The time now is 12:10 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"