adding three months to a date (but factor in weekends and holidays)

[daveyc18]

so i have a cell that's referencing h4 and has the date 8/16/2023...the rule is if the date is less than the current date (in this case 8/17/2023), add 3 months

so when i use edate(h4,3), it gives me 11/11/23, but it should be 11/13/23, because 11/11/23 falls on a weekend

i would also like thte formula to take into acocunt holidays (of course via a holidays table) if possible

 

[MARK858]

First of all Edate(h4,3) gives me 16/11/2023 if there is 16/8/2023 in H4 not 11/11/2023 (my number format is dd/mm/yyyy) so in the below I have 11/8/2023 in H4.
Holidays is a named range containing your holiday dates

Book1 (version 2).xlsb
	H	I
4	11/08/2023	Monday 13/11/2023
Sheet4

Cell Formulas
Range	Formula
I4	I4	=IF(H4<>TODAY(),IF(WEEKDAY(EDATE(H4,3),2)>5,WORKDAY(EDATE(H4,3),1,Holidays),EDATE(H4,3)),"today")

Named Ranges
Name	Refers To	Cells
Holidays	=Sheet4!$K$1:$K$7	I4

 

[Tetra201]

Here is another way of taking into account weekends and holidays:

=WORKDAY(EDATE(H4,3)-1,1,Holidays)

 

[MARK858]

Not heavily tested but you might just be able to use the formula in I5 (same as previous post Holidays is a named range containing your holiday dates

Book1 (version 2).xlsb
	H	I
4	11/08/2023	Monday 13/11/2023
5		Monday 13/11/2023
Sheet4

Cell Formulas
Range	Formula
I4	I4	=IF(H4<>TODAY(),IF(WEEKDAY(EDATE(H4,3),2)>5,WORKDAY(EDATE(H4-1,3),1,Holidays),WORKDAY(EDATE(H4-1,3),1,Holidays)),"today")
I5	I5	=IF(H4<>TODAY(),WORKDAY(EDATE(H4-1,3),1,Holidays),"today")

Named Ranges
Name	Refers To	Cells
Holidays	=Sheet4!$K$1:$K$7	I4:I5

EDIT: looks like @Tetra201 had the same idea