Calculate difference between two dates

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.

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

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

@bamaeric ,

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

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

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

Link to Form

@

• Garrett the Link to Form is not working any more, can you resend it aging pls?