trystanhuwwilliams1
Programmer
Hi,
Is it possible to add 10 NETWORKDAYS to a date to create a target date in Excel?
Thanks,
Trystan
Is it possible to add 10 NETWORKDAYS to a date to create a target date in Excel?
Thanks,
Trystan
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Create Target Date in Excel 1
- Thread starter trystanhuwwilliams1
- Start date
- Status
-
1
- #2
Hardly elegant but:-
=IF(NETWORKDAYS(A2,A2+12)=10,A2+12,IF(NETWORKDAYS(A2,A2+13)=10,A2+13,IF(NETWORKDAYS(A2,A2+14)=10,A2+14,IF(NETWORKDAYS(A2,A2+15)=10,A2+15,IF(NETWORKDAYS(A2,A2+16)=10,A2+16)))))
or
=SUMPRODUCT(((A2+12)*(NETWORKDAYS(A2,A2+12)=10))+((A2+13)*(NETWORKDAYS(A2,A2+13)=10))+((A2+14)*(NETWORKDAYS(A2,A2+14)=10))+((A2+15)*(NETWORKDAYS(A2,A2+15)=10))+((A2+16)*(NETWORKDAYS(A2,A2+16)=10)))
Should cover all weekends and up to 2 bank holidays either side of a weekend - Don't know about Xmas though
Regards
Ken..............
----------------------------------------------------------------------------
![[peace]](/data/assets/smilies/peace.gif "[peace] [peace]")
It's easier to beg forgiveness than ask permission![[2thumbsup]](/data/assets/smilies/2thumbsup.gif "[2thumbsup] [2thumbsup]")
----------------------------------------------------------------------------
=IF(NETWORKDAYS(A2,A2+12)=10,A2+12,IF(NETWORKDAYS(A2,A2+13)=10,A2+13,IF(NETWORKDAYS(A2,A2+14)=10,A2+14,IF(NETWORKDAYS(A2,A2+15)=10,A2+15,IF(NETWORKDAYS(A2,A2+16)=10,A2+16)))))
or
=SUMPRODUCT(((A2+12)*(NETWORKDAYS(A2,A2+12)=10))+((A2+13)*(NETWORKDAYS(A2,A2+13)=10))+((A2+14)*(NETWORKDAYS(A2,A2+14)=10))+((A2+15)*(NETWORKDAYS(A2,A2+15)=10))+((A2+16)*(NETWORKDAYS(A2,A2+16)=10)))
Should cover all weekends and up to 2 bank holidays either side of a weekend - Don't know about Xmas though
Regards
Ken..............
----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission----------------------------------------------------------------------------
- Status
