Hello,
This is a first for me and any help would be appreciated!!!!
I need to calculate adding a certain number of days to a start date counting Monday through Saturday while skipping Sundays and holidays. The holidays are in a range.
For example i need to take a date 01/01/2015 and add 5 days to it. Solution needs to be a date. The date would represent a deadline.
The WORKDAY function uses just a 5 day work week. Wish my company had a newer version of Excel!
Thanks,
Paul
If Excel has WORKDAY.INTL function, this will work. This does not count the start date . If you want to include the start date, add 1 to the result.
Formula:
Please Login or Register to view this content.
A1 is the start date (change to suit your data)
27 is the number of workdays to add to the start date
11 fixes Sunday as the weekend
H1:H15 a range for holiday dates.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
Hello, the WORKDAY.INTL function is not supported in Excel 2007.
Disregard.
Last edited by skywriter; 04-27-2015 at 04:49 PM.
Click the * Add Reputation button in the lower left hand corner of this post to say thanks.
Don't forget to mark this thread SOLVED by going to the "Thread Tools" drop down list above your first post and choosing solved.
The WORKDAY function already excludes skips Sundays and Saturdays. If i added sundays to the holiday list as you suggest Sundays would just simply be skipped again. Am i missing something?
No I was thinking backwards, I edited my post above.Originally Posted by 76paul
Check out this thread. Go to the post by Barry Houdini, maybe it will work for you.
Post.
barry's solution won't exclude holidays.
You can try this array formula where start date is A1, days to add in B1 and holidays in H2:H10
=SMALL(IF(WEEKDAY(A1+ROW(INDIRECT("1:"&B1*3)))>1,IF(ISNA(MATCH(A1+ROW(INDIRECT("1:"&B1*3)),H$2:H$10,0)),ROW(INDIRECT("1:"&B1*3)))),B1)+A1
confirm with CTRL+SHIFT+ENTER
Audere est facere
Awesome! That worked. THANKYOU!!! It added the days to my date skiping Sundays and Holidays. Now, the formula doesn't seem accept a -1 for the days to add. I am also needing to subtract a # of days skipping Sundays and holidays. How can I do this? I really appreciate your help!
To accommodate positiive or negative numbers in B1 (or zero) try this version
=IF(B1=0,0,SMALL(IF(WEEKDAY(A1+SIGN(B1)*ROW(INDIRECT("1:"&ABS(B1)*3)))>1,IF(ISNA(MATCH(A1+SIGN(B1)*ROW(INDIRECT("1:"&ABS(B1)*3)),H$2:H$10,0)),ROW(INDIRECT("1:"&ABS(B1)*3)))),ABS(B1))*SIGN(B1))+A1
Note that the *3 in that formula is a little arbitrary - if you have long runs of holidays on consecutive days then you may need to change the 3 instances of *3 to *10 or higher
That worked!! I am pumped. I searched all over the intranet with no luck. Thanks for your time and sharing your knowledge. SOLVED!!!
Thanks for explaining the *3.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks