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.
|