Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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. |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Array formula: how to join 2 ranges together to form one array? | Excel Worksheet Functions | |||
Find specific value in array of array formula | Excel Worksheet Functions | |||
Negative numbers use braces | Excel Discussion (Misc queries) | |||
Array formulas and braces { } | New Users to Excel | |||
Excel2000 A working formula has {braces} which disappear. Why? | Excel Worksheet Functions |