Solved

Calculate difference between two dates

  • 26 October 2023
  • 7 replies
  • 232 views
J
Userlevel 2
Badge +9

Hello,

I am  trying to calculate 12 months (From date /To date) exactly.

So from 1/1/2023 to 12/31/2023 I continue to get 11 months in my form. I am doing this within NAC Forms. I used the function below and continue to get 11 months. Am I using the wrong function?

“dateDiff([Datetime].[TotalMonths],[Form].[Repeating Section].[Current row].[FROM Date],[Form].[Repeating Section].[Current row].[TO Date])”


The formula I am trying to mimic is out of an excel form. That formula is listed below and outputs 12 months when I enter in the same dates.
=IF(F32="","",YEARFRAC(D32,F32)*12)

Your help is appreciated!

Thanks

icon

Best answer by bsikes 26 October 2023, 22:30

View original

7 replies

bamaeric
Userlevel 5
Badge +13

Try configuring a text form variable and using a combination of the DateAdd and FormatDate functions.

Here’s the formula I used to get a date 12 months from a date entered into a Date/Time control named “Start Date”:

          formatDate(dateAdd("months",12,[Form].[Start Date]),"MM/DD/YYYY")

If you just want the date to set a Date/Time control, you can remove the formatDate function.

J
Userlevel 2
Badge +9

@bamaeric ,

Thanks for this! But what I am actually trying to do is grab a whole number of the months between 2 dates entered in.

So.

 

But I get 11 months...I’m still trying to figure out how to apply your response.

bamaeric
Userlevel 5
Badge +13

Hi @jpacheco,

Maybe try this to see if it gets you what you are looking for.

  1. Calculate the number of days between the dates -- Create a form variable of Integer type (named intDateDiffDays) and use this formula:

    dateDiff("TotalDays",[Form].[From Date],[Form].[To Date])
     
  2.  Next divide intDateDiffDays by 30 -- Create a form variable of decimal type (named decDaysToMonthsCalc) and use this formula:

    [Form].[intDateDiffDays]/30
     
  3. Finally round up the decDaysToMonthsCalc with 0 decimal points -- Create a form variable of decimal type (named decDaysDiffRounded) and use this formula:

    round([Form].[decDaysToMonthsCalc],0)

These form variables chained together will result in a whole number of months between those two dates. You may have to mix in some form rules to account for exceptions (for example, less than 30 days).

J
Userlevel 2
Badge +9

@bamaeric ,

Thanks again for the quick response. I will attempt this.

J
Userlevel 2
Badge +9

@bamaeric ,

 

Ok, I applied and tested and got 12 months once entering in the above. You rock and that worked out well!!! Now as you mentioned, rules may need to be created to work with less than 30 days…

I tested below (screen shot) with dates of 1/1/2024 TO 12/31/2030 and received 85 months when I believe it should be 84…

Would the rule need to apply to [Form].[intDateDiffDays]/30?? I’m really confused...
 

 

B
Userlevel 4
Badge +10

I’d just go your initial route, and use the dateAdd function to add 1 day to your TO Date. 

dateDiff([Datetime].[TotalMonths],[Form].[Repeating Section].[Current row].[FROM Date],dateAdd("days",1,[Form].[Repeating Section].[Current row].[TO Date]))

Garrett
Userlevel 6
Badge +16

@bsikes solution is the simple and straight forward method. Add 1 day to the “TO Date”

Link to Form

 

Reply


View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings