Rolling quarterly due dates

[Kelpot86]

Hello,

I am struggling with a formula. I want it to update due dates on a quarterly basis but when the month rolls forward, it updates all due dates, even if the first due date hasn't passed. E.g when rolling forward from May to June on the 1st Jun, a due date that was 4th June will roll forward to Sep before 4th June has passed. How do I stop this from happening?

=WORKDAY(IF($B$1<C5,C5,EOMONTH(EDATE(C5,CEILING(ABS(MONTH($B$1)-MONTH(C5)+(ABS(YEAR($B$1)-YEAR(C5))*12)),3)),0)),A5,$E$2:$E$554)

B1 being todays date, C5 being the original start date, A5 being the number of work days it's due after the start date, E2:554 being public holidays.

Will be extremely grateful for any advice / tweak to my formula. I have tried to search for an answer for ages and proved fruitless.

Thanks in advance.

 

[Kelpot86]

I have decided to scrap the original formula and start again. However I cant get the Workday function to work if C24 is zero? It skips the weekend dates once C24 has a number above or below zero but still falls on a weekend if C24 is zero.

=WORKDAY(IF(A24="A",EDATE(D24,12),IF(A24="M",EDATE(D24,1),IF(A24="Q",EDATE(D24,3),IF(A24="Opt","Opt","TBC")))),C24,J9:J17)

 

[Kelpot86]

I solved this by subtracting 1 from the start date and adding one for the offset, therefore they cancelled each other out.

=WORKDAY(IF(A23="A",EDATE(D23,12)-1,IF(A23="M",EDATE(D23,1)-1,IF(A23="Q",EDATE(D23,3)-1,IF(A23="Opt","Opt","TBC")))),1,J8:J16)