# 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?

-

``````=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.
-
would never have guessed by the name! thanks :-) – Tim Abell Jan 14 '15 at 23:01
documentation help.libreoffice.org/Calc/EDATE – Tim Abell Jan 14 '15 at 23:02
watch out for problems where there's no equivalent day of the month in the new month. superuser.com/a/774808/8271 – Tim Abell Feb 20 '15 at 10:42

## 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
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) – user244488 Feb 20 '15 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. – Tim Abell Feb 20 '15 at 10:40

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. – Tim Abell Feb 20 '15 at 10:35

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. – 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