Dates/Autofill

[razaas]

Hi,
I have a manually entered date in cell B2 (24-Dec-17), I need a formula to fill the next dates from cell C2 onwards, the criteria for dates to be 25th of each month if the 25 is not a Friday or Saturday and also not a holiday, sample shown in the table below. The holidays are listed in a range from cell B5 to B25. If the 25th is a Friday, Saturday or Holiday then I need an earlier date that is between Sunday to Thursday which meets the criteria. Hope it makes sense and someone will be able to provide a solution. Thanks!

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]A2[/TD]
[TD="align: center"]B2[/TD]
[TD="align: center"]C2[/TD]
[TD="align: center"]D2[/TD]
[TD="align: center"]E2[/TD]
[TD="align: center"]F2[/TD]
[/TR]
[TR]
[TD]24-Dec-17[/TD]
[TD]25-Jan-18[/TD]
[TD]22-Feb-18[/TD]
[TD]25-Mar-18[/TD]
[TD]25-Apr-18[/TD]
[TD]24-May-18[/TD]
[/TR]
</tbody>[/TABLE]

 

[steve the fish]

Try this in C2 and drag across:

=WORKDAY.INTL(DATE(YEAR(EDATE(B2,1)),MONTH(EDATE(B2,1)),26),-1,7,$B$5:$B$25)

 

[Yongle]

Or this in C2 drag across:
=WORKDAY.INTL(26+EOMONTH(B2,0),-1,7,$B$5:$B$25)

Logic
add 26 to last day of month of date in cell B2 and get previous working day (-1) based on Fri/Sat weekends (7) taking account of holidays

 

[razaas]

Thanks a lot Steve and Yongle, both the formulas works, highly appreciate your quick response. Have a great day, thanks.

Try this in C2 and drag across:

=WORKDAY.INTL(DATE(YEAR(EDATE(B2,1)),MONTH(EDATE(B2,1)),26),-1,7,$B$5:$B$25)

Yongle

Re: Dates/Autofill
Or this in C2 drag across:
=WORKDAY.INTL(26+EOMONTH(B2,0),-1,7,$B$5:$B$25)
​