Mar 29 2018
10:33 AM
- last edited on
Jul 25 2018
11:31 AM
by
TechCommunityAP
Mar 29 2018
10:33 AM
- last edited on
Jul 25 2018
11:31 AM
by
TechCommunityAP
i have a spreadsheet that has dates for a time frame and it has a "amount of days" that it has to be completed in while deducting for weekends and holidays.
Here is the formula i am using:
=IF(H4="","",NETWORKDAYS(H4,20,W4:AP4))
Formula resides in cell: I4
"H4" is the date
"20" is the time it needs to be finished by
"W4:AP4" is where the holidays are stored
Am I doing something wrong??
Mar 29 2018 02:10 PM - edited Mar 29 2018 02:11 PM
SolutionHi,
The second argument of NETWORKDAYS function should be a date, not a whole number.
And this function gives you the net working days between two dates with taking into account the weekends and holidays and subtract from this net!
But I guess that you want to return the due date after a specific number of days (20) with taking into account the weekends and holidays!
If so, please replace this:
=IF(H4="","",NETWORKDAYS(H4,20,W4:AP4))
With this:
=IF(H4="","",WORKDAY(H4,20,W4:AP4))
Mar 29 2018 02:54 PM
will this still remove the weekends along with the holidays i specified in the last part of formula?
Mar 29 2018 02:58 PM
Yes of course.
You can read this support article to learn more about this function.
Mar 29 2018 03:09 PM
Trust you experience and knowledge on this. (i did have my formula setup just like that and an assistant to my supervisor) went to him and told him her formula was more accurate.
Mar 29 2018 02:10 PM - edited Mar 29 2018 02:11 PM
SolutionHi,
The second argument of NETWORKDAYS function should be a date, not a whole number.
And this function gives you the net working days between two dates with taking into account the weekends and holidays and subtract from this net!
But I guess that you want to return the due date after a specific number of days (20) with taking into account the weekends and holidays!
If so, please replace this:
=IF(H4="","",NETWORKDAYS(H4,20,W4:AP4))
With this:
=IF(H4="","",WORKDAY(H4,20,W4:AP4))