Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old September 1st 11, 01:48 PM posted to microsoft.public.excel.worksheet.functions
wal wal is offline
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2009
Posts: 8
Default 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   Report Post  
Old 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
Default 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   Report Post  
Old 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
Default 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   Report Post  
Old 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
Default 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   Report Post  
Old September 2nd 11, 12:17 AM posted to microsoft.public.excel.worksheet.functions
wal wal is offline
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2009
Posts: 8
Default 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   Report Post  
Old 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
Default 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   Report Post  
Old 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
Default 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")


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Array formula: how to join 2 ranges together to form one array? Rich_84 Excel Worksheet Functions 2 April 1st 09 06:38 PM
Find specific value in array of array formula DzednConfsd Excel Worksheet Functions 2 January 13th 09 06:19 AM
Negative numbers use braces Taras_96 Excel Discussion (Misc queries) 4 May 21st 08 10:44 AM
Array formulas and braces { } Epinn New Users to Excel 4 September 4th 06 11:57 PM
Excel2000 A working formula has {braces} which disappear. Why? SpudHutton Excel Worksheet Functions 3 April 22nd 05 01:30 PM


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

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017