asked on

excel formula to calculate quarterly, semi annually or annual date increments

is there a formula that will calculate quarterly, semi annually or annual date increments

i.e. if date = 1/8/2013 Annual next date is 1/8/2014
if date = 1/8/2013 next Semi-Annual date is 1/2/2014
if date = 1/8/2013 next Quarterly date is 1/12/2013

Thanks in advance

Ingeborg Hawighorst (Microsoft MVP / EE MVE)

Hello,

what version of Excel?

With Excel 2010 and later (possibly also 2007, I forget the details), you can use Edate()

=EDATE(A1,6)

Where the first parameter is the source date and the second parameter is the number of months, so annual would be 12, semi-annual would be 6 and quarterly would be 3

In my book, a quarter year after 1-Aug-2013 is 1-Nov-2013. You say you want December instead. Is that a typo or a different requirement?

cheers, teylyn

Jagwarman

ASKER

Correction if date = 1/8/2013 next Quarterly date is 1/11/2013.

I presume I would use =EDATE but I don't seem to be able to join all 3 in an IF statement.

I am working on the basis that the user has a field with the original date. They also have a field that is populated with either 'Q', 'S' OR 'A'.

My formula for quarterly [only is =EDATE(F9,3) which returns 1/11/2013

Appreciate assistance to perform this under an IF statement.

Thanks

ASKER CERTIFIED SOLUTION

Ingeborg Hawighorst (Microsoft MVP / EE MVE)

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Jagwarman

ASKER

brilliant thanks.