Remember Me?

#1
September 1st 11, 01:48 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Oct 2009 Posts: 8
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.

#2
September 1st 11, 03:12 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Apr 2008 Posts: 1,549
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.

#3
September 1st 11, 07:34 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jun 2010 Posts: 1,045
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")

#4
September 1st 11, 08:46 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2011 Posts: 86
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?

#5
September 2nd 11, 12:17 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Oct 2009 Posts: 8
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.

#6
September 2nd 11, 12:46 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jun 2010 Posts: 1,045
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")
#7
September 2nd 11, 12:53 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jun 2010 Posts: 1,045
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")

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Rich_84 Excel Worksheet Functions 2 April 1st 09 06:38 PM DzednConfsd Excel Worksheet Functions 2 January 13th 09 06:19 AM Taras_96 Excel Discussion (Misc queries) 4 May 21st 08 10:44 AM Epinn New Users to Excel 4 September 4th 06 11:57 PM SpudHutton Excel Worksheet Functions 3 April 22nd 05 01:30 PM

All times are GMT +1. The time now is 11:58 AM.