Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

This question already has an answer here:

Hi I want know if there is a formula in excel. Let's day I was hired Feb 24, 2014 so regularization is 6 months so my regularization date would be August 24, 2014. I want to know the formula in excel so I can create an automatic record.

share|improve this question

marked as duplicate by harrymc, CharlieRB, Excellll, Raystafarian, Tog Jun 4 at 19:07

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

1 Answer 1

It somewhat depends on how the regularization date is actually calculated. Is it really 6 months, or is it actually 180 days?

Assuming your date is stored in A1:

6 Months

=DATE(YEAR(A1),MONTH(A1)+6,DAY(A1))

180 Days

=A1+180

An interesting thing about the months addition mode is that it seems to intelligently handle year wraparounds and end-of-month date mismatches, despite what the formula may appear to imply. Literally, the formula would seem to explicitly state that the YEAR and DAY value from A1 is used regardless of the month calculation. In reality, Excel will increment the year when needed (e.g.: your date is in July or later), and it will also account for months lacking a 31st (or 29th or 30th) by pushing the date into the next month.

A perfect example of this would be setting the start date to August 31st. Depending on whether the next year is a leap year, your regularization date would be either March 2nd or 3rd of the next year instead of the very erroneous February 31st of the same year.

More information on adding dates can be found on Microsoft's support site. Though that link is particularly in reference to Excel 2003, the same formulas work up to 2013 just fine.

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.