DATETIME_DIFF Incorrect Months

[Khuned_Sachdev]

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.

[Databaser]

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

[Khuned_Sachdev]

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…?

[Databaser]

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.

[Khuned_Sachdev · Accepted solution]

Databaser:

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

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

[Khuned_Sachdev]

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

[Khuned_Sachdev]

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.

[Databaser]

Khuned_Sachdev:

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 :blush: