Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Count the remaining weekdays in a data range excluding holidays and start/end dates
Hello,
I need some help. I need to count the remaining weekdays (eg. Mondays) in a date range. I also need to exclude holidays and the date range in my count. A1 Start Date: 07/01/2013 B2 End Date: 07/31/2013 A4 Holidays A5 01/01/2013 A6 05/27/2013 A7 07/04/2013 A9 Number of Remaining Mondays A10 5 A11 Number of Remaining Thursdays (excluding holiday) A12 3 A10 =INT((WEEKDAY(A2-2)-A2+B2)/7)-SUMPRODUCT(--(WEEKDAY(A5:A7)=2),--(A5:A7=A2),--(A5:A7<=B2)) A12 =INT((WEEKDAY(A2-5)-A2+B2)/7)-SUMPRODUCT(--(WEEKDAY(A5:A7)=5),--(A5:A7=A2),--(A5:A7<=B2)) I need A10 to be 4 to exclude the start date. Whenever I change the start date, the count for the remaining weekdays must excludes that start date Eg. On Tuesday, 07/30/2013, there should be 0 count for the remaining Tuesdays in the date range btw 07/30/2013 and 07/31/2013. Likewise, for Wednesday 07/31/2013, there is a 0 count for the remaining Wednesdays in the month of July. Please help. Thanks Addatone |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count the remaining weekdays in a data range excluding holidays and start/end dates
On Tue, 23 Jul 2013 22:32:50 +0100, Addatone wrote:
Hello, I need some help. I need to count the remaining weekdays (eg. Mondays) in a date range. I also need to exclude holidays and the date range in my count. A1 Start Date: 07/01/2013 B2 End Date: 07/31/2013 A4 Holidays A5 01/01/2013 A6 05/27/2013 A7 07/04/2013 A9 Number of Remaining Mondays A10 5 A11 Number of Remaining Thursdays (excluding holiday) A12 3 A10 =INT((WEEKDAY(A2-2)-A2+B2)/7)-SUMPRODUCT(--(WEEKDAY(A5:A7)=2),--(A5:A7=A2),--(A5:A7<=B2)) A12 =INT((WEEKDAY(A2-5)-A2+B2)/7)-SUMPRODUCT(--(WEEKDAY(A5:A7)=5),--(A5:A7=A2),--(A5:A7<=B2)) I need A10 to be 4 to exclude the start date. Whenever I change the start date, the count for the remaining weekdays must excludes that start date Eg. On Tuesday, 07/30/2013, there should be 0 count for the remaining Tuesdays in the date range btw 07/30/2013 and 07/31/2013. Likewise, for Wednesday 07/31/2013, there is a 0 count for the remaining Wednesdays in the month of July. Please help. Thanks Addatone Given a Start Date and End Date, the following formula will return the number of any particular weekday, not counting the Start Date: =SUMPRODUCT(--(WEEKDAY(WORKDAY(StartDate,ROW( INDIRECT("1:"&-1+NETWORKDAYS(StartDate,EndDate,Holidays))), Holidays))=DOW)) DOW = Day of week where Monday = 2, Thursday = 5 Holidays is a named range containing the holidays. The formula generates an array of the workdays; we then see if they are equal to the desired DOW and count them. |
#3
|
|||
|
|||
Thank you Ron for your reply; truly appreciated.
The formula works very well except when I change the StartDate to 07/31/2013. Then I get a #REF! error in all my cells: A1 Start Date: 07/01/2013 B2 End Date: 07/31/2013 A4 Holidays A5 01/01/2013 A6 05/27/2013 A7 07/04/2013 When the Start Date is changed to 07/30/2013. It still works. See below. Mon Tuesday Wednesday Thursday Friday 0 0 1 0 0 When the Start Date is changed to 07/31/2013, I get the #REF! Mon Tuesday Wednesday Thursday Friday #REF! #REF! #REF! #REF! #REF! I tried removing the End Date from the formula but get the #VALUE!. I think the formula needs to be adjusted to only exclude holidays and the Start Date. My concern then is when the Start Date and End Date are the same, e.g 07/31/2013. What happens? I also tested for the month August. Please see below: When the Start Date is 08/29/2013, the formula works. A1 StartDate 8/29/2013 B1 EndDate 8/31/2013 Mon Tuesday Wednesday Thursday Friday 0 0 0 0 1 When the Start Date is 08/30/2013, I get the #REF! error. A1 StartDate 8/30/2013 B1 EndDate 8/31/2013 Mon Tuesday Wednesday Thursday Friday #REF! #REF! #REF! #REF! #REF! Thank you in advance for all your help and patience. Quote:
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count the remaining weekdays in a data range excluding holidays and start/end dates
On Wed, 24 Jul 2013 16:27:53 +0100, Addatone wrote:
I tried removing the End Date from the formula but get the #VALUE!. Ithink the formula needs to be adjusted to only exclude holidays and the Start Date. Please provide an example where it is excluding other dates. It should not be. My concern then is when the Start Date and End Date are the same, e.g 07/31/2013. What happens? In that case, or any case (such as your August example), where there is one or less WorkDays, an error will result. A quick fix is to test for that condition: =IF(NETWORKDAYS(StartDate,EndDate,Holidays)<=1,0,S UMPRODUCT( --(WEEKDAY(WORKDAY(StartDate,ROW(INDIRECT("1:"&-1+ NETWORKDAYS(StartDate,EndDate,Holidays))),Holidays ))=DOW))) However, there is another potential problem -- can StartDate ever be on a weekend or holiday? And, if so, how should that be handled? (If not, I still have to make some changes, but I don't have time right now). Later. |
#5
|
|||
|
|||
Hello Ron,
Thanks for your prompt reply and assistance. I apologize for the miscommunication in regards to the below quote: Ithink the formula needs to be adjusted to only exclude holidays and the Start Date.[/i][/color] I meant to say, "The formula needs to be adjusted to only exclude holidays and the Start Date and not to exclude the End Date as I originally wrote". The new formula you sent works perfectly and I'm no longer getting the #REF! error when there is one or less WorkDays left in the month. Yay! In regards to your question, a Start Date can never be on a Holiday or weekend. It always has to be a Business Day. Thanks once again for your help. You totally saved the day. Later, Addatone Quote:
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count the remaining weekdays in a data range excluding holidays and start/end dates
On Wed, 24 Jul 2013 21:10:30 +0100, Addatone wrote:
I meant to say, "The formula needs to be adjusted to only exclude holidays and the Start Date and not to exclude the End Date as I originally wrote". OK, that is how the formula is designed. The new formula you sent works perfectly and I'm no longer getting the #REF! error when there is one or less WorkDays left in the month. Yay! Glad to help; thanks for the feedback. In regards to your question, a Start Date can never be on a Holiday or weekend. It always has to be a Business Day. In that case, no change is required for the formula. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
difference between two dates & time by excluding holidays & weeken | Excel Programming | |||
Calculates Dates Based on 7 day week excluding Holidays | Excel Discussion (Misc queries) | |||
fill a series of dates excluding holidays | Excel Worksheet Functions | |||
How do you count work days excluding weekends and holidays? | Excel Discussion (Misc queries) | |||
formula to calculate # of days between dates, excluding holidays | Excel Discussion (Misc queries) |