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