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?
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.
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?
Code:
=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.