| 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
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"," ") |
|
#7
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 |
|
#8
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
<BG Let's do it! -- Biff Microsoft Excel MVP "Rick Rothstein" wrote in message ... 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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formula for first saturday of quarter
Perhaps,
=DATE(YEAR(A1),1,1)+7-WEEKDAY(DATE(YEAR(A1),1,1),1)+(13*7)*(ROUNDUP(MONT H(A1)/3,0)-1) Mike "T. Valko" wrote: I'm guessing this ought to spark a how short can we get it war <BG Let's do it! -- Biff Microsoft Excel MVP "Rick Rothstein" wrote in message ... 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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formula for first saturday of quarter
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" wrote: Perhaps, =DATE(YEAR(A1),1,1)+7-WEEKDAY(DATE(YEAR(A1),1,1),1)+(13*7)*(ROUNDUP(MONT H(A1)/3,0)-1) Mike "T. Valko" wrote: I'm guessing this ought to spark a how short can we get it war <BG Let's do it! -- Biff Microsoft Excel MVP "Rick Rothstein" wrote in message ... 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 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formula for first saturday of quarter
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" wrote: 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" wrote: Perhaps, =DATE(YEAR(A1),1,1)+7-WEEKDAY(DATE(YEAR(A1),1,1),1)+(13*7)*(ROUNDUP(MONT H(A1)/3,0)-1) Mike "T. Valko" wrote: I'm guessing this ought to spark a how short can we get it war <BG Let's do it! -- Biff Microsoft Excel MVP "Rick Rothstein" wrote in message ... 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 |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formula for first saturday of quarter
Definately my last effort
=DATE(YEAR(A14),1,8)-WEEKDAY(DATE(YEAR(A14),1,1),1)+91*(ROUNDUP(MONTH(A 14)/3,0)-1) Mike "Mike H" wrote: 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" wrote: 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" wrote: Perhaps, =DATE(YEAR(A1),1,1)+7-WEEKDAY(DATE(YEAR(A1),1,1),1)+(13*7)*(ROUNDUP(MONT H(A1)/3,0)-1) Mike "T. Valko" wrote: I'm guessing this ought to spark a how short can we get it war <BG Let's do it! -- Biff Microsoft Excel MVP "Rick Rothstein" wrote in message ... 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 |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formula for first saturday of quarter
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) -- Rick (MVP - Excel) "Mike H" wrote in message ... Definately my last effort =DATE(YEAR(A14),1,8)-WEEKDAY(DATE(YEAR(A14),1,1),1)+91*(ROUNDUP(MONTH(A 14)/3,0)-1) Mike "Mike H" wrote: 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" wrote: 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" wrote: Perhaps, =DATE(YEAR(A1),1,1)+7-WEEKDAY(DATE(YEAR(A1),1,1),1)+(13*7)*(ROUNDUP(MONT H(A1)/3,0)-1) Mike "T. Valko" wrote: I'm guessing this ought to spark a how short can we get it war <BG Let's do it! -- Biff Microsoft Excel MVP "Rick Rothstein" wrote in message ... 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 |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formula for first saturday of quarter
=7+("1/1/"&YEAR(A1))-WEEKDAY("1/1/"&YEAR(A1))+91*INT((MONTH(A1)-1)/3)
I haven't tested it but we may have a winner! I wonder if that date format holds up for non U.S. English regional settings. Here's the shortest I could come up with: =DATE(YEAR(B1),INT((MONTH(B1)+2)/3)*3-2,8)-WEEKDAY(DATE(YEAR(B1),INT((MONTH(B1)+2)/3)*3-2,1)) I've been trying to shorten this bit: INT((MONTH(B1)+2)/3)*3-2 -- Biff Microsoft Excel MVP "Rick Rothstein" wrote in message ... 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) -- Rick (MVP - Excel) "Mike H" wrote in message ... Definately my last effort =DATE(YEAR(A14),1,8)-WEEKDAY(DATE(YEAR(A14),1,1),1)+91*(ROUNDUP(MONTH(A 14)/3,0)-1) Mike "Mike H" wrote: 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" wrote: 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" wrote: Perhaps, =DATE(YEAR(A1),1,1)+7-WEEKDAY(DATE(YEAR(A1),1,1),1)+(13*7)*(ROUNDUP(MONT H(A1)/3,0)-1) Mike "T. Valko" wrote: I'm guessing this ought to spark a how short can we get it war <BG Let's do it! -- Biff Microsoft Excel MVP "Rick Rothstein" wrote in message ... 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 |
|
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formula for first saturday of quarter
=7+("1/1/"&YEAR(A1))-WEEKDAY("1/1/"&YEAR(A1))+91*INT((MONTH(A1)-1)/3)
I haven't tested it but we may have a winner! I wonder if that date format holds up for non U.S. English regional settings. I would think it would as the day and the year are the same, so there couldn't be any month/day confusion. Or were you referring to the slashes? That one I don't know, but Excel is pretty clever about forming dates, so I would think it has a shot of working even when the regional setting is for some other delimiter character. For example, this works on my system and the dash is not a date delimiter on my computer... =7+("1-1-"&YEAR(A1))-WEEKDAY("1-1-"&YEAR(A1))+91*INT((MONTH(A1)-1)/3) Rick |
|
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
formula for first saturday of quarter
Damn,
I thought I had that one, <BG. Mike "Rick Rothstein" wrote: 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) -- Rick (MVP - Excel) "Mike H" wrote in message ... Definately my last effort =DATE(YEAR(A14),1,8)-WEEKDAY(DATE(YEAR(A14),1,1),1)+91*(ROUNDUP(MONTH(A 14)/3,0)-1) Mike "Mike H" wrote: 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" wrote: 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" wrote: Perhaps, =DATE(YEAR(A1),1,1)+7-WEEKDAY(DATE(YEAR(A1),1,1),1)+(13*7)*(ROUNDUP(MONT H(A1)/3,0)-1) Mike "T. Valko" wrote: I'm guessing this ought to spark a how short can we get it war <BG Let's do it! -- Biff Microsoft Excel MVP "Rick Rothstein" wrote in message ... 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 |
| 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 | |||
Linear Mode
