Excel VBA code help - converting a date to nearest quarter end date

[cmansf]

Hi,

New to the forum, looking for help.

In my spreadsheet a purchased date is entered. Is there a code that can convert or roundup the date to nearest quarter end date i.e.

25/2/2011 = 31/3/2011
5/6/2013 = 30/6/2013

Quarter End Dates
31/3
30/6
30/9
31/12

Any help would be great.

Regards

Chris

[Bob Phillips]

Try

=EOMONTH(A2,MOD(3-MOD(MONTH(A2),3),3))

[cmansf]

Try

=EOMONTH(A2,MOD(3-MOD(MONTH(A2),3),3))

Hi,

Thanks for taking the time. The only problem and I should have clarified it; I need the year to be present which this formula doesnt do, and secondly, i distribute this to a lot of staff and require the date always to round up to the nerest quarter end as they alway forget. Therefore, a VBA code will probably be required as I only have one cell to enter the purchased date.

[Bob Phillips]

It creates a date so the year is present, there is nothing to add it. Any VBA code would need something to run it, so it is no different to a formula. You could add data validation to error check date input and not allow a non-quarter-end date.