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?
Super User is a question and answer site for computer enthusiasts and power users. It only takes a minute to sign up.Sign up to join this community
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.
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.
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.
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.
As given in reference: Date Arithmetic, this adds one to the month:
=DATE(YEAR(A1), MONTH(A1)+1, DAY(A1))
Using LibreOffice Version: 18.104.22.168 (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.
FWIW this is the solution I used where the month and year matters:
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.
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