# Rolling quarterly due dates

#### Kelpot86

##### New Member
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?

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.

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

#### Kelpot86

##### New Member
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.

Code:
``=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)``

Last edited:

#### Kelpot86

##### New Member
I solved this by subtracting 1 from the start date and adding one for the offset, therefore they cancelled each other out.
VBA Code:
``=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)``

Replies
1
Views
153
Replies
2
Views
64
Replies
3
Views
166
Replies
0
Views
80
Replies
1
Views
97

1,126,986
Messages
5,621,992
Members
415,873
Latest member
fuulhouse

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back