Finding a Day 30 Days From Now unless a weekend or holiday

[wsmalaney]

I need to plot when payments will be made to businesses with net30 billing terms, meaning pay within 30 days. In order to pay at the last possible date, I would like to have a formula that would take a date in column "A" and using that date, place the date it will pay in column "B".

The date in column "B" will be 30 days from column "A" unless it falls on a weekend or holiday and then it should pay to the previous business day. I would plan to reference holidays in a series of cells like "N2:N10".

I have tried just about everything i can think of and short of a massively long if statement whose logic escapes me, I don't know how to do this.

 

[Jonmo1]

Welcome to the board...

Try

=WORKDAY(A1+31,-1,$N$2:$N$10)

The workday function requires the analysis toolpack from Tools - Addins.
If in XL2007 or higher, it's already installed.


Hope that helps.

 

[VoG]

Try

=WORKDAY(StartDate,Days,Holidays)

e.g.

=WORKDAY(A2,30,Z1:Z20)

In Excel 2003 and before this requires the Analysis Toolpak from Tools > Add-Ins.

 

[Excelestial]

In Cell N2:
=WORKDAY(B2+30,1)
Drag down to N10...or where needed

 

[wsmalaney]

The only one that will work is the one jonmo1 submitted because workday counts out by workdays so instead of being 30 regular days from now, it would be 36-40 days or close to that depending on how many weekends and holidays there are in the month.

=WORKDAY(A1+31,-1,$N$2:$N$10)

This is actually pretty clever. I was also trying to use variations of the workday function such as

=WORKDAY(A1+31,NETWORKDAYS(A1,A1+30,$N$2:$N$10),$N$2:$N$10)

You still have the issue of it not counting holidays and I could just never get any of the variations to work.

Thanks JonMo

 

[Jonmo1]

Glad to help, thanks for the feedback.