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

#1

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

0 Likes

#2

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?

0 Likes

#3

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

0 Likes

#4

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

0 Likes

#5

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"

0 Likes

#6

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.

0 Likes

#7

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

0 Likes

#8

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

0 Likes