Date Calculation: how to increment a date by exactly 1 month


Is there a way to increment a date by 1 month. Just like the "edate" Excel function. For example, if today's date is April 15, 2019, i want a result equals to that date + 1 month (May 15, 2019). Using a static 30 day addition causes errors for dates near the 1st day or last day of the month because some months have 28,30, or 31 days

Example1: March 1st 2019 - 30 days = January 30th 201, instead i'd want February 1st, 2019
Example2: March 31st 2019 - 30 days = March 1st 2019, instead i'd want February 28th, 2019

Running Aggregate 1.6.1, Android 6.0, Techno L8 Lite

I searched the ODK forum without any success



I think your question is still a little under-defined... Specifically, what would you expect from incrementing, say, May 31 by "one month": June 30, or July 1?



Hi there, i've edited my post. For May 31st, i'm looking for Jun 30th increment. Same for any last day or first day of the month, i want to increment by 1 month, so that I do not skip a month.
Ex: Feb 1st + 1 month = March 1st
Ex: Feb 28th + 1 month = March 28th
Ex: March 31th - 1 month = Feb 28th



That seems a bit weird to me... Presumably you'd want Feb 27 + 1 month = March 27, right?

[my first inclination would be to make any days that exceed the number of days in the following month all map to the last day of that month; ie Jan 29,30,31 --> Feb 28. Assuming not a leap year. If a leap year then it gets even messier...]

Perhaps, to take a step back and ask, what is the usecase you are trying to address here? That may lend itself to what is the most appropriate definition of "a month".



Yes you're right , Feb 27 + 1 month = March 27. I edited that.

My use case is basic; i want to display as a label the names of the 12 months that come before the date of the interview. For ex: interview date is today (4/16/19). I want to show 12 labels starting on the interview date: "April"..."March"..."February"....etc... back to "April"



How about then just setting the day to 1, eg if the interview is 2019-02-27 (Feb 27) then it becomes 2019-02-01 (Feb 1). And then just keep adding (or subtracting) 1 to the month, mod 12 [so that it wraps around], and using a format-date(${newdate}, '%b') to get the month abbreviation ("Jan", "Feb", "Mar",...) for these new dates.



"And then just keep adding (or subtracting) 1 to the month, mod 12 [so that it wraps around]"

That's exactly what i'm trying to accomplish. How do you add 1 to the month?

I've tried using the formula =int(format-date(${today},'%n'))+1 to get the month number + 1, but how do i turn it back into a date?



You can manually construct a valid hyphen-separated date string using the concat() function. Specifically

concat(${year}, '-', ${month}, '-', ${day})

However, the trick is that the month and day need to be zero-padded, which regular numbers wont be. So there is some additional work that needs to be done to zero-pad the month when necessary [since we're explicitly setting the day to '01' in this particular case it is already fine].

Have a play with this and adapt it to your needs:

addmonth.xls (5.5 KB)

1 Like

Add date-from-components() XPath function
split this topic #9

A post was split to a new topic: Add date-from-components() XPath function