Skip to main content

Dear Community,


Need help in trying to calculate the difference in months and days between two dates.


Q1: is it even possible to calculate difference and return result in month and days? Or is it only possible in either months OR days?


Q2: I entered a formula to only display difference in months, but choosing Jan 1 to Dec 31 is showing 11 months, and not 12 months. I get it that is subtracts and does not include Dec 31 in it’s calculation, but selecting the employement period dates (in our case) to be Jan 1 to Jan 1 would technically create confudion to the employee, as the period ends Dec 31. Any work around this. Super thanks in advance.




Hi @Khuned_Sachdev


You could use this formula to add 1 day and get 12 months:


DATETIME_DIFF(DATEADD({date end}, 1, "days"), {date start}, "months")

Hi @Khuned_Sachdev


You could use this formula to add 1 day and get 12 months:


DATETIME_DIFF(DATEADD({date end}, 1, "days"), {date start}, "months")


Dear @Databaser Thanks lots. I think that shall solve the number of months issue.


Any suggestion if it is at all possible to diplay the difference in Months and Days…?

Dear @Databaser Thanks lots. I think that shall solve the number of months issue.


Any suggestion if it is at all possible to diplay the difference in Months and Days…?


No, only 1 unit is posible. You could of course make 2 formula fields for 2 units and then concatenate them into a third one.

Hi @Khuned_Sachdev


You could use this formula to add 1 day and get 12 months:


DATETIME_DIFF(DATEADD({date end}, 1, "days"), {date start}, "months")



Hi @Databaser, just used this and it works perfect! Thanks.

No, only 1 unit is posible. You could of course make 2 formula fields for 2 units and then concatenate them into a third one.


@Databaser, would the second “date unit” formula not result in total number of days, and concatenate it would be incorrect?


So, 3 months and 2 days, would end up being something like 3 months and 93 days? Or is there more to the concatenate formula that I am unaware of?

@Databaser, would the second “date unit” formula not result in total number of days, and concatenate it would be incorrect?


So, 3 months and 2 days, would end up being something like 3 months and 93 days? Or is there more to the concatenate formula that I am unaware of?


Or do yo mean to have 4 date fields, 2 for dates that fall exactly in months, and then 2 others to enter the date start/end just for the additional days part? Seems too risky and tedious. I must be missing out something you are suggesting.

Or do yo mean to have 4 date fields, 2 for dates that fall exactly in months, and then 2 others to enter the date start/end just for the additional days part? Seems too risky and tedious. I must be missing out something you are suggesting.



No, you are right. The “Days” formula would result in the total number of days, and not the saldo. You could make a formula that compensates this, but that would be tedious indeed. So never mind my suggestion 😊

Reply


Terms & Conditions