Problem with Substring Widget

https://www.jotform.com/build/252271885368064

I have created a substring widget to extract the number of the month in the START DATE field. The results of this calculation are used to create formulas to prorate dues for new members. The dues year is May - April.

The substring widget's source field ID is #month_60 and it starts with the 0 character and ends with 2 character. It appears that should be sufficient for getting the first numerals, the month, off the date.

The product of the widget is used in calculating ADJUSTED MONTH AND REMAINING MONTHS.

Everything works fine May - December. May is assigned to be the 0 ADJUSTED MONTH, each month increases with December being 7. The problem happens in January when January has an ADJUSTED MONTH value of -4 (instead of being the 8th month) and the January REMAINING MONTHS shows as 16 instead of 4. February is ADJUSTED MONTH -3/REMAINING MONTHS 15, should be 9th month with 4 months remaining. March is ADJUSTED MONTH -2/REMAINING MONTHS 14, should be 10th month with 2 months remaining. April is ADJUSTED MONTH -1/REMAINING MONTHS 13, should be 11th month with one month remaining.

I don't know where the problem is. But both the ADJUSTED MONTH and REMAINING MONTHS results are correct May through December and have errors January - April, following a predictable pattern. The REMAINING MONTH formula is simple, 12-ADJUSTED MONTH. The ADJUSTED MONTH formula is: SUBSTRING WIDGET-5+12%12. (Not sure if that makes sense?) The settings for the SUBSTRING WIDGET (above) also came from support.

I'm not sure if the problem is with the SUBSTRING WIDGET the ADJUSTED MONTH formula. (or something else!) I hope you can help me. Thanks.

Hi Andy,

Thanks for reaching out to Jotform Support. I'm an AI assistant. I'll do my best to help you until someone from the support team is available.

Regarding your substring widget issue, the problem with the ADJUSTED MONTH and REMAINING MONTHS calculations for January to April likely stems from how the formula handles the month values crossing the year boundary.

Your formula ADJUSTED MONTH = SUBSTRING WIDGET - 5 + 12 % 12 seems to be intended to adjust the month number relative to May, but the modulo operation might not be applied correctly to handle negative values as expected.

Let me help you with that:

1. Check the substring widget settings to ensure it correctly extracts the month number as an integer (from 1 to 12).
2. Modify the ADJUSTED MONTH formula to properly handle the wrap-around from December to January. For example, use a formula like: ((MonthNumber + 7) % 12) to shift May to 0 and January to 8.
3. Verify the REMAINING MONTHS formula as 12 - ADJUSTED MONTH once the adjusted month is correctly calculated.

This approach should fix the negative values and incorrect remaining months for January through April.

Give it a try and let us know how it goes.

Best regards,
Jotform Support

1. How do I do this? What am I looking for to see if it extracts the number as an integer from (1-12) and actually the way it appears to set up May is month 0 and April should be month 11. Not sure if that matters.
2. In the formula you suggest what do I use as MonthNumber, substring widget?

Hi Andy,

To check if the substring widget extracts the month as an integer (1-12), preview your form and see the widget's output value for the START DATE field. It should show the month number, like "05" for May.

For your formula, use the substring widget's output as MonthNumber. For example, if your substring widget field is named month_60, your formula would be:

((month_60 + 7) % 12)

This shifts May to 0 and April to 11 as you want. Then use 12 - ADJUSTED MONTH for remaining months.

Try this adjustment and see if it fixes the issue.

Best regards,
Jotform Support

Hi Andy,

Thanks for the follow-up—great questions. Here’s the exact setup that will fix the January–April wrap-around and answer both points:

1) Check that the Substring widget outputs the month as a number (1–12).

• Preview the form and look at the Substring widget’s current value for your START DATE.
• It should show the month digits (e.g., 05 for May).
• In the Form Calculation, make sure this field is treated as numeric (tap the field so it turns brown/number mode). If it’s treated as text, just add + 0 after it to coerce to a number.

2) Use the Substring widget’s output as MonthNumber in the formula.

If your Substring widget field is month_60, use:

((month_60 + 7) % 12)

• + 7 shifts May → 0, so the series becomes:
• May = 0, Jun = 1, … Dec = 7, Jan = 8, Feb = 9, Mar = 10, Apr = 11.
• % 12 handles the wrap-around at the year boundary and prevents negatives.

3) Remaining months formula:

Once ADJUSTED MONTH is computed as above, set:

REMAINING MONTHS = 12 - ADJUSTED MONTH

Quick sanity check:

• May should show 0, December 7, January 8, April 11.

Give it a try and let us know if you run into anything else.

Best!!

First I can't see the widget's output value - how do I do that?

Second the formula did not work. I tried a number of other variations and none of them worked. The flawed SUBSTRING WIDGET -5+12%12 works the best - correct May - December and then off as I noted January - April.

Yigit-

1. Here's the problem. The Substring Widget's output value is correct up to December. In January it goes to -4 instead of 8. February is -3 instead of 9, March is -2 instead of 10 and Apr is -1 instead of 11. I can't tell why the widget would be reading those months as negative numbers. It seems like it is somewhere inside the Substring Widget. If we could get it to put out the right numbers for the four months everything would work. Can you play around with it? I don't know how the substring widget was created. It was done by support for me. I was told it was the only way JotForm could extract the month from a date to use in formulas. Let me know what you find, but I've got to go to bed. https://www.jotform.com/build/252271885368064

I'm sorry, I'm getting punchy. The Substring Widget is actually reading the number of each month, i.e. January is 01 and December is 12. That seems correct

The problem seems to lie with the ADJUSTED MONTH formula. We are currently using: Substring Widget -5+12%12. I assume that is to give a number to use in equations that would help me prorate the the dues amount. The widget is generating the right number for all months. It seems there must be something wrong with the adjusted month formula. What we want that formula to do is to take the Widget number which corresponds to the calendar month and turn it into a number representing months of the dues year. For instance May, called by the widget as the 05 month, should appear as Adjusted Month 0. December, called by the widget as the 12 month, should appear as Adjusted Month 7. January is called by the widget as the 01 month, and should appear as Adjusted Month 8. (Though this is where the errors begin as the Adjusted Month comes out as -4. It seems that if the widget is correct, the problem lies with the rest of the formula. https://www.jotform.com/build/252271885368064

Hi Andy,

I’ll need a bit of time to look into this. I’ll get back to you as soon as I can. We really appreciate your patience and understanding while we’re looking into this.

Thanks for your effort, but never mind. I finally solved it. The formula that works in ADJUSTED MONTH is: (Substring Widget+7)%12

Bracket placement was critical. All the prorating functions work perfectly now even Jan-April.

Thanks again.