So I have a problem that I'm dealing with for days now and I cant figure it out. I have this problem in excel, I am looking for assistance regarding a calculation. I want to be able to calculate a payment date schedule based on a set monthly payment date but to exclude exclude weekends and specific holiday dates e.g. Here's My file I'm Working on:[].

Here is some function that I already used : =IF(TEXT(O2,"dddd")=$U$2,O2 2,IF(TEXT(O2,"dddd")=$U$3,O2 1,O2))This works for only weekends, It doesn't work on holidays, I want it to work not only on weekends and on holidays too.

Thanks in advance.

CodePudding user response：

This can be done by using the WORKDAY() function together with the DATE() function.

B2 contains year - 2012 B3 contains payment day - 28 A5:A17 contain payment periods (month numbers) - 1..12 D6:D16 contain holidays

=WORKDAY(DATE($B$2,A6,$B$3-1),1,$D$6:$D$14)

Also see the linked image. (NB: My Excel version uses semi-colon ";" in stead of comma "," to separate arguments in functions.)

Best of luck!

Xharx

Here's How u Do it. First we have to have starting month from where were gonna start counting I made this formula which works great, =WORKDAY(EDATE($B$15,COUNT($B$15:B17))-1,1,holiday1) were freezing the starting date and counting how many rows are we from the starting position , then were gonna minus 1 from it , and in days were gonna type 1, if there's holidays that I wanna exclude too I just can enter it and give it a name, (Some of u who doesn't understand this, its just saying that its referring to specified cells which we named holidays) and it works flawlessly. B15 = starting month