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)

2 Likes

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

@Xiphware it's an amazing example incrementing one month based on date selection.

Could you please explain this calculation number(${month}>=12).

I have tried to do this in a diffrent way for my understanding. But it may not be efficient like yours.addmonth_v2.xls (57 KB)

${month}>=12 is a XPath boolean expression which returns the equivalent of 'true' or false'. You are allowed to mix-n-match XPath sub-expressions of different types - eg booleans, numbers, strings, etc - in the same expression, and XPath will perform the equivalent of type-casting them to the required type of the parent expression; eg using booleans in an arithmetic expression, or strings in a boolean expression, etc. The XPath number() function is basically an explicit operation to convert a boolean subexpression (something producing true or false) to an actual number; in this case true-->1 and false-->0.

So in this example, if the ${month} is less than 12 then the result is 0, so you add 0 to the year (ie same year). Otherwise if the month is 12+ then the result is 1, so you want to increment the year by 1.

Well, its arguably only 'amazing' if yer actually allowed to reset the day to 1 (or something less that 29...) :wink:

Incrementing an arbitrary date by "one month" (whatever THAT means) is substantially more tricky! [as, hopefully, I alluded to previously]

Thanks for the solution! but do you know why I still can't convert this formula (from your shared spreadsheet) back into a date? I get the type mismatch error when converting. The formula works fine when all 3 concatenated variables are fixed strings, but not when there's a dynamic variable like "format-date(${date},'%m')" for ${month}

Also, it looks like the formula already accounts for 0-padding, otherwise, the format-date function can return the 0-padding too. So i don't think that's the issue here.

concat(${newyear},'-', if(${newmonth}<10,'0',''), ${newmonth}, '-', ${day})

date(concat(${newyear},'-', if(${newmonth}<10,'0',''), ${newmonth}, '-', ${day})) -- type mismatch error

What tool(s) are you using to generate your form? I've loaded the above XLSForm into https://opendatakit.org/xlsform/ and it converts and runs fine.

Yes, format-date() can produce zero-padded values; eg using '%m' it will return "03" for March. The problem is that when you then add 1 to this month value, the arithmetic expression will result in 4, which when (implicitly) converted to a string within the concat function, will result in "4". This is a consequence of how XPath typecasts numbers to strings; from XPath1.0:

A number is converted to a string as follows:

  • if the number is an integer, the number is represented in decimal form as a Number with no decimal point and no leading zeros, preceded by a minus sign (-) if the number is negative

Hence the need to explicitly zero-pad the newly calculated month number.

The original XLSForm converts fine, but when i try changing the concat formula output from a string to a date, i get the mismatch error type. The formula works when all three variables are fixed strings, but not when any 1 of them is a dynamic variable.

date(concat(${newyear},'-', if(${newmonth}<10,'0',''), ${newmonth}, '-', ${day})) <-- ERROR: type mismatch error

In actuality, there is no specific 'date' type per se; rather, a 'date' is simply a string that conforms to a specific format - "YYYY-MM-DD". How that string came about doesn't actually matter. In this case, the above concat() function generates a conformant date string, which can be used anywhere thereafter as a 'date'. So there is no reason to attempt to convert the concat() result to a 'date' (using date() or any other mechanism for that matter) - the result is already a valid 'date'.

The error you are seeing is a result of attempting to use the date() function, which is used to generate a date given the number of days from a January 1, 1970 epoch. So date() expects to be given an integer, whereas you are giving it a (date) string, hence the type mismatch error.

I see, but the only reason why i'm bringing the string to date type conversion is because I need to only display the month name and year (Apr-19) and not '04-01-2019'. So I was planning on converting that string to a date in order to use the date-format function.

That string is already a date, so it can be used directly in format-date()

i thought so too, but when converting the form, i get the type mismatch error from this calculation. See attached file, where i added 1 row highlighted in red to your original spreadsheet

format-date(${newdate}, '%Y-%m') <-- creates error

addmonth_v2.xls (20 KB)

A post was split to a new topic: Disabling Validate's static type checking