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 …

[image] richardnguyen5: 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 …

[image] richardnguyen5: Ex: Feb 28th + 1 month = March 31st 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; i…

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

[image] richardnguyen5: i want to display as a label the names of the 12 months that come before the date of the interview 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 t…

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

@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)

[image] ARIF_AZAD_KHAN: Could you please explain this calculation number(${month}>=12) ${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…

[image] ARIF_AZAD_KHAN: it's an amazing example incrementing one month based on date selection. 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 substan…

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 "fo…

[image] richardnguyen5: I get the type mismatch error when converting 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. [image] richardnguyen5: the format-date function can return the 0-pad…

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}<1…

ODK Forum

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

Support

odk-xlsform, form-design

show post in topic

Home
Categories
Guidelines
Terms of Service
Privacy Policy

Powered by Discourse, best viewed with JavaScript enabled

Looking for a better way? Try ODK Cloud, the official managed hosting service from the creators of ODK.