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

[wal]

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.

 

[Jim Cone]

=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" <[email protected]>
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.

 

[Ron Rosenfeld]

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")

 

[Gord]

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

 

[wal]

Thanks to all for the solutions.

(I think with Ron's, the "8" needs to be a "7", assuming a Sun.-Sat.
week.)

 

[Ron Rosenfeld]

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")

 

[Ron Rosenfeld]

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")