Formula to calculate dates accurately

Hi All,

I'm new to the site and have never used excel formulas or VBA before I started this project a few weeks ago so please bear with me while I try to explain my dim witted issue!

I have a comprehensive spreadsheet tracking the requirements of companies to send me certain information at agreed timescales. I've amended an existing, much simpler spreadsheet to fit purpose but am having trouble getting the dates to reflect accurately.

an example of the formula I am using for "next due" is:

=IF(AB9="Monthly",(AA9+$CH$1),IF(AB9="Quarterly",(AA9+$CH$2),IF(AB9="Half yearly",(AA9+$CH$3))))

with:
CG CH
1 Monthly 30
2 Quarterly 91
3 Half Yearly 180


As not every month has 30 days etc it is throwing the dates off quite drastically. Main points of use for the dates are:

mail macro pulling incorrect dates (should always be last day of the month)
summary sheet dates based on original input and conditional formatting on sheet 1 is based on these dates
the "next due" column on sheet 1 is showing beginning of the following month, not end of the correct month sometimes

So, it's quite important that I get this right before rolling out to 22 portfolios!

Below is the spreadsheet in question (hopefully!!!) with some dummy information.

Accounts blank.xlsm

Obviously is anyone spots anything else that is incorrect or could be done simpler, please feel free to correct me as the while thing is built from googling so far!

Please help!!

If you want the last date of the current month:

=DATE(YEAR(AA9),MONTH(AA9)+1,0)

If you want the first of the next month:

=DATE(YEAR(AA9),MONTH(AA9)+1,1)

You can change the +1 to +3 for quarterly and +6 for half-yearly - the formulae will automatically adjust for spanning into a new year etc.

Hope this helps.

Pete

Hi Pete,

Thanks for the speedy reply. How would I use this within the current forumla? I assume I still need the IF statements?

Lou

Yes, you would still need the IF statement, but instead of your term:

(AA9+$CH$1)

where you are adding a fixed number of days on for "Monthly", you will use the appropriate formula that I gave you (i.e. for the last date of the current month or the first of the next month. Similarly for the quarterly and half-yearly terms.

Then you won't need that little table in CG1:CH3.

Hope this helps.

Pete

PERFECT!!!! Thanks very much. Spent so much time tralwing forums trying to make it fit. Didn't realise it was so simple!

Thanks!