Forum Discussion

CurtisMcHenry's avatar
CurtisMcHenry
Qrew Member
5 years ago

Calculating Number of Months and Days between 2 dates?

Hey there,

I have two fields [Start Date] and [End Date], where I need to calculate the number of Months and Days between them.

For Example:

[Start Date] = 08-30-2019
[End Date] = 10-01-2019

Should come out to '1 month, 1 days', but due to the variance in the number of days in each month, I can't for the life of me figure out how to reliably calculate this. The state requires it presented as 'x months, x days', so I can't have it be approximate.


If anyone could provide me with some guidance that would be greatly appreciated!

Thank you.



------------------------------
Curtis McHenry
------------------------------
Formulas and functions

4 Replies

Sort By
  • XavierFan's avatar
    XavierFan
    Qrew Cadet
    5 years ago
    I think you'd need a definition from the state as to how they define a "month".

    Do they have any examples of calculations?

    We can think of any number of ways to calculate this - but sounds like what matters is what the state accepts as "accurate".

    ------------------------------
    Xavier Fan
    ------------------------------
  • BlancaAnderson's avatar
    BlancaAnderson
    Qrew Assistant Captain
    5 years ago
    Were you able to get the formula for this?  I need to calculate the number of months between two dates.

    ------------------------------
    Blanca Anderson
    ------------------------------
    • BlancaAnderson's avatar
      BlancaAnderson
      Qrew Assistant Captain
      5 years ago
      Answer:  I was using the wrong field type.  I was using duration but needed to use Formula Numeric.  
      This formula worked for me.

      (([End Date] - [Start Date]) / Days(30))

      ------------------------------
      Blanca Anderson
      ------------------------------
      • ParadoxHomes's avatar
        ParadoxHomes
        Qrew Member
        4 years ago
        This formula worked, but sometimes skipped a number for me. I found this formula to work pretty well on an amortization table I was building.

        var number numOfYears = Year([Payment Date]) - Year([First Payment Date]);
        var number numOfMonths = Month([Payment Date]) - Month([First Payment Date]);
        var number npers = If($numOfYears = 0,
        $numOfMonths,
        ($numOfYears*12) + $numOfMonths);
        $npers

        ------------------------------
        Paradox Homes
        ------------------------------