Rolling quarterly due dates

Kelpot86

New Member
Joined
Aug 23, 2013
Messages
14
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.

Thanks in advance.
 

Some videos you may like

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
Joined
Aug 23, 2013
Messages
14
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
Joined
Aug 23, 2013
Messages
14
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)
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top