my problem is as under
sample file uploaded
Cell A2 has project start date
Cell B2 has project days
Cell F1 to F17 has holidays, range named as holidays
Cell C2 is project end date after calculation
my problem is that workday function treats saturdays as holidays which in my case should be working days
sample.xlsx.
In other words, how should I calculate project end date after treating sundays and specfied holidays only.
Last edited by anilrini; 03-09-2012 at 06:45 AM.
You can use a kind of NETWORKDAYS formula as described at this page
many thanks
Originally Posted by Pepe Le Mokko
But this has slowed down the process because i have to wrote down VBA mod for new formula
can any (possibly an array formula) formula be written instead of VB module
Last edited by anilrini; 03-02-2012 at 12:15 AM.
Pl see the attached file. UDF Workday2 is defined which counts saturdays and holidays are taken into account.
Clarifications Welcome.
Please Login or Register to view this content.
anybody please
i want this to be used as an array formula with existing functions only
thanks but i want this as an formula with existing functions
Pl see the attached file. UDF Workday2 is defined which counts saturdays and holidays are taken into account.
Clarifications Welcome.Please Login or Register to view this content.
you can use this, where range "F:F" is your holidays and range "G:G" is your rest days (or sundays)
=A2+B2+COUNTIFS(F:F,"<="&A2+B2,F:F,">="&A2)+COUNTIFS(G:G,"<="&A2+B2,G:G,">="&A2)
If you liked my solution, please click on the Star -- to add to my reputation
If your issue as been resolved, please clearly state so and mark the thread as [SOLVED] using the thread tools just above the first post.
Thanks
I assume that one has to write all sundays in G:G to work this. But if i had to do this i can achieve it with this formula
=C2+SMALL(IF(COUNTIF(holidays,C2+ROW(INDIRECT("1:"&D2+COUNT(holidays)))),"",ROW(INDIRECT("1:"&D2 +COUNT(holidays)))),D2)
confirmed with CTRL+SHIFT+ENTER
where C2 has start date, D2 has number of days and holidays is a named range for holidays. (i can include all sundays in this named range too)
But i do not want to list all sundays.
Apologies anilrini, I meant to reply to your query elsewhere but kept forgetting....
I don't believe that approach will work DGagnon, even if you list all Sundays. The formula finds all Sundays and holidays between A2 and A2+B2 and adds those to A2+B2.....but that doesn't account for additional holidays or Sundays that might be between A2+B2 and that new date.
Try this "array formula"
=SMALL(IF(WEEKDAY(A2+ROW(INDIRECT("1:"&B2*10)))<>1,IF(COUNTIF(holidays,A2+ROW(INDIRECT("1:"&B2*10)))=0, ROW(INDIRECT("1:"&B2*10)))),B2)+A2
confirmed with CTRL+SHIFT+ENTER
This assumes you will never have as many as 10 non-workdays consecutively
Last edited by daddylonglegs; 03-02-2012 at 06:17 PM.
Audere est facere
thanks
i think if this assumption has to varied i.e. assuming that not more than 15 holidays/closing days consecutively, we may change the formula by replacing 10 with 15.
That's right, or you can replace 10 with COUNT(holidays)*2
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks