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!

A2	B2	C2	D2	E2	F2
24-Dec-17	25-Jan-18	22-Feb-18	25-Mar-18	25-Apr-18	24-May-18

<tbody>
</tbody>

 

[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)
​