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