|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#21
April 26th, 2006, 12:41 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
1st Monday of a month in date range??
scwilly Wrote: Example: How many 5th Fridays are between 5/1/2006 and 6/30/2006 The answer should be 1, your formula works if I change the end date to 7/1/2006 but not for 6/30/2006 which I need and would love. It is more for a date range where the begin and end date could fall on days other than the first of the month. Thanks again, cheers Hi scwilly, how are you getting 1 for the above? If you include the start and end dates should it not be 2, 31st March and 30th June? Or are you not counting the start and end dates as part of the range? Assuming you are you could use this formula =SUMPRODUCT(--(TEXT(ROW(INDIRECT(A1&":"&A2)),"ddd")=A4),--(ABS(DAY(ROW(INDIRECT(A1&":"&A2)))-A3*7+3)4)) works for any start/end dates -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=535648 
|
|
#22
April 26th, 2006, 12:49 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
1st Monday of a month in date range??
scwilly Wrote: Example: How many 5th Fridays are between 5/1/2006 and 6/30/2006 The answer should be 1, your formula works if I change the end date to 7/1/2006 but not for 6/30/2006 which I need and would love. It is more for a date range where the begin and end date could fall on days other than the first of the month. Thanks again, cheers Hi scwilly, how are you getting 1 for the above? If you include the start and end dates should it not be 2, 31st March and 30th June? Or are you not counting the start and end dates as part of the range? Assuming you are you could use this formula =SUMPRODUCT(--(TEXT(ROW(INDIRECT(A1&":"&A2)),"ddd")=A4),--(ABS(DAY(ROW(INDIRECT(A1&":"&A2)))-A3*7+3)4)) works for any start/end dates -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=535648
|
|
#23
April 26th, 2006, 04:49 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
1st Monday of a month in date range??
Voila that works!! Thank you both RON and DADDYLONGLEGS for all of your help. Regards, -- scwilly ------------------------------------------------------------------------ scwilly's Profile: http://www.excelforum.com/member.php...o&userid=18251 View this thread: http://www.excelforum.com/showthread...hreadid=535648
|
| Thread Tools | |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Finding the date on the 'nth' Monday in this Month in this Year | agarwaldvk | Worksheet Functions | 1 | April 17th, 2006 10:53 PM |
| How do you sort a date range by month? | Brewisc13 | General Discussion | 13 | July 7th, 2005 06:45 PM |
| Date Range and Average F/X Rate | David | General Discussion | 0 | June 23rd, 2005 02:26 PM |
| Date of Month vs. Now | Walter Steadman | Running & Setting Up Queries | 10 | June 9th, 2005 09:50 PM |
| Count the occurances of a month in a range of date fields | Keith Brown | Worksheet Functions | 8 | March 14th, 2005 11:24 AM |
Linear Mode
