Quater dates that fall on the same week day

[Charlene Durand]

I am trying to set up a list of clients and a proposed date for a quaterly meeting. most clents have a spcific day in that week they would want to have it eg. friday

my current formula is:
=DATE(YEAR($A$2)+0,MONTH($A$2)+3,DAY($A$2)*1)

$A$2 = the date of the last meeting for them or i set it as at 2023/12/31 to find quter dates for 2024

Currently this formula gives me weekend and public holidays which i don't want, so if the day fall's on a public holiday give me an alternitive same with weekends.

 

[JamesCanale]

This schedules the meeting for exactly 13 weeks later. If that is on the holiday list, it will push it another week or two.

MrExcelPlayground22.xlsx
	A	B
1	Last meeting	Proposed next meeting
2	4/26/2024	7/26/2024
3
4	List of holidays
5	1/1/2024
6	1/15/2024
7	2/8/2024
8	5/25/2024
9	7/4/2024
10	9/3/2024
11	10/12/2024
12	11/7/2024
13	11/11/2024
14	12/25/2024
Sheet16

Cell Formulas
Range	Formula
B2	B2	=IF(ISNA(MATCH(A2+7*13,A5:A14,0)),A2+7*13,IF(ISNA(MATCH(A2+7*14,A5:A14,0)),A2+7*14,A2+7*15))

 

[Tetra201]

See if the following formula works for you:

=WORKDAY.INTL(EDATE(A2,3)+1,-1,"1111011",Holidays)

Here "1111011" sets the target date to Friday, and Holidays is a named range that contains holiday dates.