Home 
Search 
Today's Posts 
#1




Use array/braces **WITHIN** a formula?
Excel 2003
I'm trying to set up a formula that gives the date range for the first week (or partial week) of the month. If the first day of the month is a Sunday, then the result would read "September 17"; if the first day is a Monday, then the result would read "September 16"; and so on. The hard way is a formula like this: =IF(WEEKDAY(C3)=1,TEXT(C3,"mmmm d") & "" & TEXT(C3+6,"d"),IF(WEEKDAY(C3)=2,TEXT(C3,"mmmm d") & "–" & TEXT(C3+5,"d")))... [and so on] This gets cumbersome, matching up all the parentheses. Can this be abbreviated with an arraytype formula? I was hoping the following would work, but it doesn't: =IF(WEEKDAY(C3)={1,2,3,4,5,6,7},TEXT(C3,"mmmm d") & "–" & TEXT(C3+{6,5,4,3,2,1,0},"d")) I typed the braces myself, which is incorrect. You have to enter array formulas (including the braces) with controlshiftenter, but that only works if the whole formula is an array formula, right? Anyway, is there any way to abbreviate what I'm trying to do? Thanks. 
#2




Use array/braces **WITHIN** a formula?
=TEXT($C$3,"mmmm d") & "" &TEXT(8WEEKDAY($C$3),"d")
The date in C3 must be the 1st day of a month... 09/01/2011  Jim Cone Portland, Oregon USA http://www.mediafire.com/PrimitiveSoftware (Extras for Excel addin: convenience builtin) "wal" wrote in message ... Excel 2003 I'm trying to set up a formula that gives the date range for the first week (or partial week) of the month. If the first day of the month is a Sunday, then the result would read "September 17"; if the first day is a Monday, then the result would read "September 16"; and so on. The hard way is a formula like this: =IF(WEEKDAY(C3)=1,TEXT(C3,"mmmm d") & "" & TEXT(C3+6,"d"),IF(WEEKDAY(C3)=2,TEXT(C3,"mmmm d") & "–" & TEXT(C3+5,"d")))... [and so on] This gets cumbersome, matching up all the parentheses. Can this be abbreviated with an arraytype formula? I was hoping the following would work, but it doesn't: =IF(WEEKDAY(C3)={1,2,3,4,5,6,7},TEXT(C3,"mmmm d") & "–" & TEXT(C3+{6,5,4,3,2,1,0},"d")) I typed the braces myself, which is incorrect. You have to enter array formulas (including the braces) with controlshiftenter, but that only works if the whole formula is an array formula, right? Anyway, is there any way to abbreviate what I'm trying to do? Thanks. 
#3




Use array/braces **WITHIN** a formula?
On Thu, 1 Sep 2011 05:48:16 0700 (PDT), wal wrote:
Excel 2003 I'm trying to set up a formula that gives the date range for the first week (or partial week) of the month. If the first day of the month is a Sunday, then the result would read "September 17"; if the first day is a Monday, then the result would read "September 16"; and so on. The hard way is a formula like this: =IF(WEEKDAY(C3)=1,TEXT(C3,"mmmm d") & "" & TEXT(C3+6,"d"),IF(WEEKDAY(C3)=2,TEXT(C3,"mmmm d") & "–" & TEXT(C3+5,"d")))... [and so on] This gets cumbersome, matching up all the parentheses. Can this be abbreviated with an arraytype formula? I was hoping the following would work, but it doesn't: =IF(WEEKDAY(C3)={1,2,3,4,5,6,7},TEXT(C3,"mmmm d") & "–" & TEXT(C3+{6,5,4,3,2,1,0},"d")) I typed the braces myself, which is incorrect. You have to enter array formulas (including the braces) with controlshiftenter, but that only works if the whole formula is an array formula, right? Anyway, is there any way to abbreviate what I'm trying to do? Thanks. With any date in the desired month in C3: =TEXT(C3,"mmmm d") & TEXT(C3+8WEEKDAY(C3),"d") 
#4




Use array/braces **WITHIN** a formula?
You have a couple of solutions to the abbreviating your formula.
I would just like to clarify your thoughts about curly braces inside formulas. Yes, you can use them to create arrays within a formula. =LOOKUP(B1,{1,2,3,4},{"A","B","C","D"})) which saves having to create a lookup table range on sheet. =SUM((LEN(A1)LEN(SUBSTITUTE(A1,{1,2,3,4,5,6,7,8,9},"")))*{1,2,3 ,4,5,6,7,8,9}) Sums only numbers within a string like 1234abcd4321 Gord Dibben Microsoft Excel MVP On Thu, 1 Sep 2011 05:48:16 0700 (PDT), wal wrote: I typed the braces myself, which is incorrect. You have to enter array formulas (including the braces) with controlshiftenter, but that only works if the whole formula is an array formula, right? 
#5




Use array/braces **WITHIN** a formula?
Thanks to all for the solutions.
(I think with Ron's, the "8" needs to be a "7", assuming a Sun.Sat. week.) On Sep 1, 10:12*am, "Jim Cone" wrote: =TEXT($C$3,"mmmm d") & "" &TEXT(8WEEKDAY($C$3),"d") The date in C3 must be the 1st day of a month... 09/01/2011  Jim Cone Portland, Oregon USAhttp://www.mediafire.com/PrimitiveSoftware (Extras for Excel addin: *convenience builtin) "wal" wrote in ... Excel 2003 I'm trying to set up a formula that gives the date range for the first week (or partial week) of the month. *If the first day of the month is a Sunday, then the result would read "September 17"; if the first day is a Monday, then the result would read "September 16"; and so on. The hard way is a formula like this: =IF(WEEKDAY(C3)=1,TEXT(C3,"mmmm d") & "" & TEXT(C3+6,"d"),IF(WEEKDAY(C3)=2,TEXT(C3,"mmmm d") & " " & TEXT(C3+5,"d")))... [and so on] This gets cumbersome, matching up all the parentheses. Can this be abbreviated with an arraytype formula? *I was hoping the following would work, but it doesn't: =IF(WEEKDAY(C3)={1,2,3,4,5,6,7},TEXT(C3,"mmmm d") & " " & TEXT(C3+{6,5,4,3,2,1,0},"d")) I typed the braces myself, which is incorrect. *You have to enter array formulas (including the braces) with controlshiftenter, but that only works if the whole formula is an array formula, right? Anyway, is there any way to abbreviate what I'm trying to do? *Thanks. 
#6




Use array/braces **WITHIN** a formula?
On Thu, 01 Sep 2011 14:34:32 0400, Ron Rosenfeld wrote:
On Thu, 1 Sep 2011 05:48:16 0700 (PDT), wal wrote: Excel 2003 I'm trying to set up a formula that gives the date range for the first week (or partial week) of the month. If the first day of the month is a Sunday, then the result would read "September 17"; if the first day is a Monday, then the result would read "September 16"; and so on. The hard way is a formula like this: =IF(WEEKDAY(C3)=1,TEXT(C3,"mmmm d") & "" & TEXT(C3+6,"d"),IF(WEEKDAY(C3)=2,TEXT(C3,"mmmm d") & "–" & TEXT(C3+5,"d")))... [and so on] This gets cumbersome, matching up all the parentheses. Can this be abbreviated with an arraytype formula? I was hoping the following would work, but it doesn't: =IF(WEEKDAY(C3)={1,2,3,4,5,6,7},TEXT(C3,"mmmm d") & "–" & TEXT(C3+{6,5,4,3,2,1,0},"d")) I typed the braces myself, which is incorrect. You have to enter array formulas (including the braces) with controlshiftenter, but that only works if the whole formula is an array formula, right? Anyway, is there any way to abbreviate what I'm trying to do? Thanks. With any date in the desired month in C3: =TEXT(C3,"mmmm d") & TEXT(C3+8WEEKDAY(C3),"d") Mistake: the above requires the first of the month in C3. To do it with any day of the month in C3: =TEXT(C3DAY(C3)+1,"mmmm d") & TEXT(C3+9DAY(C3)WEEKDAY(C3DAY(C3)),"d") 
#7




Use array/braces **WITHIN** a formula?
On Thu, 1 Sep 2011 16:17:25 0700 (PDT), wal wrote:
Thanks to all for the solutions. (I think with Ron's, the "8" needs to be a "7", assuming a Sun.Sat. week.) Yes: For any date in the month in C3: =TEXT(C3DAY(C3)+1,"mmmm d") & TEXT(C3DAY(C3)+8WEEKDAY(C3DAY(C3)+1),"d") 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Array formula: how to join 2 ranges together to form one array?  Excel Worksheet Functions  
Find specific value in array of array formula  Excel Worksheet Functions  
Negative numbers use braces  Excel Discussion (Misc queries)  
Array formulas and braces { }  New Users to Excel  
Excel2000 A working formula has {braces} which disappear. Why?  Excel Worksheet Functions 