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 1-7"; if the first day
is a Monday, then the result would read "September 1-6"; 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 array-type 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 control-shift-enter, 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.

Use array/braces **WITHIN** a formula?
 

=TEXT($C$3,"mmmm d") & "-" &TEXT(8-WEEKDAY($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 add-in: convenience built-in)





"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 1-7"; if the first day
is a Monday, then the result would read "September 1-6"; 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 array-type 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 control-shift-enter, 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.

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 1-7"; if the first day
is a Monday, then the result would read "September 1-6"; 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 array-type 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 control-shift-enter, 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+8-WEEKDAY(C3),"d")

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 control-shift-enter, but
that only works if the whole formula is an array formula, right?

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(8-WEEKDAY($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 add-in: *convenience built-in)

"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 1-7"; if the first day
is a Monday, then the result would read "September 1-6"; 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 array-type 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 control-shift-enter, 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.

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 1-7"; if the first day
is a Monday, then the result would read "September 1-6"; 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 array-type 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 control-shift-enter, 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+8-WEEKDAY(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(C3-DAY(C3)+1,"mmmm d-") & TEXT(C3+9-DAY(C3)-WEEKDAY(C3-DAY(C3)),"d")

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(C3-DAY(C3)+1,"mmmm d-") & TEXT(C3-DAY(C3)+8-WEEKDAY(C3-DAY(C3)+1),"d")