28

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?

trimbletodd
  • 479
  • 1
  • 4
  • 7

8 Answers8

26
=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.
Braiam
  • 4,709
  • 3
  • 26
  • 57
Tarik FDIL
  • 361
  • 3
  • 3
22

The currently favored answer will skip short months that follow long ones

Set A1=2014-01-31 Then the result using =DATE(YEAR(A1), MONTH(A1)+1, DAY(A1)) will be 2014-03-03, thus skipping February.

The EDATE approach suggested above loses days

Short months cause EDATE to drop days for successive months. E.g. =EDATE(DATE(2014,1,31),1) does produce 2014-02-28, but applying it again results in 2014-03-28, which is not the last day in March.

A solution that does work: increment months with the day set to zero

Set the day to zero, and increment months, while being one month ahead. E.g. To start in January use 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.

Other approaches

Adjustments can be made if you want the last working day in the month, or 30 day increments while not skipping months, etc. Depends on the objective.

  • `edate` worked for me without the problem you mention (Version: 4.2.7.2) – Tim Abell Jan 14 '15 at 23:01
  • 1
    Tim to see edate fail, place in cell C4 =Date(2014,01,31) and place in C7 =EDATE(C4,1) and it will correctly show 2014-02-28. however now place in cell C8 =EDATE(C7,1) and it will give the wrong value, 2014-03-28 which is not the last day of March. Tim are you seeing the last day of March on this second step? (Version 3.5.4.2 - updated version on this Debian distro) –  Feb 20 '15 at 07:08
  • 1
    Ah, I see what you mean now. Thanks for the clear example. I forget what I was doing now but I think I didn't hit that edge case and hadn't followed the subtlety of what you'd explained. Great post. – Tim Abell Feb 20 '15 at 10:40
8

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

=DATE(YEAR(A1), MONTH(A1)+1, DAY(A1))
blade19899
  • 688
  • 2
  • 11
  • 27
trimbletodd
  • 479
  • 1
  • 4
  • 7
  • 5
    Beware that if you start on a day that has no equivalent: e.g. 31st Aug + 1 month is calculated as 1st Oct because there is no 31st Sep. – Tim Abell Feb 20 '15 at 10:35
1

Using LibreOffice Version: 6.2.8.2 (x64)

Row    Formula                   Result
A11    +DATE(2020,3,1)           03/01/20
A12    +DAYSINMONTH(A11) + A11   04/01/20
A13    +DAYSINMONTH(A12) + A12   05/01/20
...
A190   +DAYSINMONTH(A189) + A189 02/01/35

I copied the formula cell in row A12, to a mass select from A13 to a190, and pasted. The result is what I expected.

Rick
  • 11
  • 1
0

FWIW this is the solution I used where the month and year matters:

A1=DATE(2016,1,1)
A2=EOMONTH(A1,0)+1

First row cell to set the start date as a date object. Following cells take the previous cell, get the end of the month, then add a day (A2 = 2016/01/31 + 1). Works for both LibreOffice Calc and Google Spreadsheets.

Thien
  • 186
  • 1
  • 3
0

For year, month, day incrementing by one month, same day of month (like last day accounting for leap years):

A1 "DATE" # Label of column
A2 "=DATE(2011, 2, 0)"
A3 "=EDATE(A$2,COUNTA(A$2))"
A4 "=EDATE(A$2,COUNTA(A$2:A3))"

Then drag A4 down as far as you like. Produces this output:

01/31/11
02/28/11
03/31/11
...
02/29/12
03/31/12
Matt Kneiser
  • 151
  • 8
0

I used this...

A1 =(01/01/2022) <-- Start Date A2 =(DAYSINMONTH(A1)+A1) <-- next month A3 =(DAYSINMONTH(A1*x)+A1) <-- skips ahead x number of months

user1710828
  • 1
  • 1
    Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Jul 16 '22 at 02:45
0

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.

BrianA
  • 1,582
  • 12
  • 12