# How to add month to date in LibreOffice calc

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?

## 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) Jan 14, 2015 at 23:01
• 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)
– user337861
Feb 20, 2015 at 7:08
• 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. Feb 20, 2015 at 10:40
``````=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.

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

``````=DATE(YEAR(A1), MONTH(A1)+1, DAY(A1))
``````
• 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. Feb 20, 2015 at 10:35

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.

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.

• 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. Jul 9, 2013 at 18:42
• There may be some ideas on this site that could help you. cpearson.com/excel/datearith.htm Jul 10, 2013 at 15:35
• Great reference. Looks like it works. Jul 10, 2013 at 19:10

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.

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
``````

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