| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| I NEED A FORMULA THAT TAKES THE DATE AND CONVERT IT IN A QUARTER | Excel Worksheet Functions | |||
| Formula - Period Quarter End | Excel Worksheet Functions | |||
| Excel should have a QUARTER formula for DATE calculations | Excel Worksheet Functions | |||
| Formula for Calendar Quarter | Excel Worksheet Functions | |||
| Formula to fill inthe Quarter | New Users to Excel | |||
Hybrid Mode
