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 seems like an obvious question.

I have a date column, and I want to create a formula to increase it by one month for each column.

10/2013, 11/2013, 12/2013, 1/2014, 2/2014, ...

How do you add one month to a date?

share|improve this question
add comment

4 Answers

=edate(a1;1)
  • edate returns the date that is the specified number of months after or before the specified date.
  • First argument of edate :start date.
  • Second argument of edate : number of month. If negative, edate calculates the date before.
share|improve this answer
add comment
up vote 2 down vote accepted

As given in reference: Date Arithmetic, this adds one to the month:

=DATE(YEAR(A1), MONTH(A1)+1, DAY(A1))
share|improve this answer
add comment

This is far from a straightforward problem, and none of the answers above completely work out.

The favored answer here, =DATE(YEAR(A1), MONTH(A1)+1, DAY(A1)) may skip short months following long ones. E.g. if A1=2014-01-31 Then the result will be 2014-03-03. In situations analogous to the 'rent being due at the end of the month' this approach does not give desirable results.

=EDATE(DATE(2014,1,31),1) does produce 2014-02-28, but applying it again results in 2014-03-28 rather than moving back to the end of the month for March. This is inconsistent behavior, as we forego the idea of a fixed numbers of days in a month to get from Jan to Feb, but go back to fixed days per month ever after.

One can increment via ends of the months by starting with the first of the next month, and then subtracting 1 day. E.g. DATE(2014,2,0) => 2014-01-31 then DATE(2014,3,0) => 2014-02-28, then DATE(2014,4,0) -> 2014-03-31 as one would expect by logically following the last day of each month.

A hybrid approach that looks at the date being incremented and picks between tracking the beginning of the month, as in the favored solution, or tracking the end of the month, as noted in prior paragraph, will not skip months, and will appear consistent in end of the month situations, though there will be a 'break' date where it switches its beginning/ending attractor, and should we have a situation such as 'a meeting scheduled on the Nth day of every month', where N is greater than the break date (perhaps the 28th), the N may wobble around between months.

It appears one has to understand the purpose behind the data when determining an approach to incrementing months.

share|improve this answer
add comment

I think you may need to use labels as dates are stored just as numbers which are then displayed in a format you choose. To add a month you'll need a complex calculation to determine how many days to take you to the next month. Hope I'm wrong and someone has an answer.

share|improve this answer
    
I also thought about a wrangling together a complicated algorithm of adding 31 days to the date, then going back to the first of the month. Both ways seem overly difficult. –  trimbletodd Jul 9 '13 at 18:42
    
There may be some ideas on this site that could help you. cpearson.com/excel/datearith.htm –  BrianA Jul 10 '13 at 15:35
    
Great reference. Looks like it works. –  trimbletodd Jul 10 '13 at 19:10
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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