Date diff rounded by years months and days

This is going to be a complicated formula, and the result it gives you will purely be for display – if you want something that you can use in other formulas, you'll want to use DATETIME_DIFF with 'days'.

What we're going to do is essentially long division. Because DATETIME_DIFF rounds down, we can get the number of years, months, and days progressively. I'll give the formula first using separate fields, and then I'll give the formula for a single field.

1. {Year Diff}, get the difference in years.

   DATETIME_DIFF({End Date}, {Start Date}, 'y')

2. {Year Added Date}, add the difference in years to Start Date.

   DATEADD({Start Date}, {Year Diff}, 'y')

3. {Month Diff}, get the difference in months.

   DATETIME_DIFF({End Date}, {Year Added Date}, 'M')

4. {Month Added Date}, add the difference in months.

   DATEADD({Year Added Date}, {Month Diff}, 'M')

5. {Day Diff}, get the difference in days.

   DATETIME_DIFF({End Date}, {Month Added Date}, 'd')

6. {Output}, combine for output.

   IF({Year Diff} > 0, {Year Diff} & ' year(s), ') 
   & IF({Month Diff} > 0, {Month Diff} & ' month(s), ') 
   & {Day Diff} & ' day(s)'

And those are all the pieces separately. Here's it all together:

So yeah, that's... that. (You can also add an extra condition in the IF function to add the plural "s" only in cases where Year Diff/Month Diff/Day Diff > 1, but that an exercise I'll leave to you, should you feel brave enough.)

Would the end date be included as a day?
Start 2023/8/15 - End 2023/8/15
would be 0.

If you want to include the end date
All Diff formulas are changed to
Year Diff :
DATETIME_DIFF(DATEADD({End},1,'d'), {Start}, 'y')
Month Diff :
DATETIME_DIFF(DATEADD({End},1,'d'), {Year Added Date}, 'M')
Day Diff :
DATETIME_DIFF(DATEADD({End},1,'d'), {Month Added Date}, 'd')
If changed to
Start 2023/8/15 - End 2023/8/15
would be 1.

I thought it would be more natural for it to be 1 in this case, so I tried to think of my own formula, but it turned out to be too complicated and I gave up on it.

@Andy_Lin1  idea is smart and best!

I haven't tested this formula out, and I have no idea if it's more performant, but in theory the math is much simpler and the formula itself is easier to parse.

Note that the SWITCH function is used to simplify the results of two ISBEFORE() functions, and takes advantage of the fact that ISAFTER() does not return a boolean result (i.e. TRUE/FALSE), but 1 and 0. By multiplying one of the results by 2, we then get all possible outcomes represented by a single integer from 0 to 3. For convenience, I'll refer to the components of {Start Date} as Y0, M0, and D0, and the components of {Stop Date} as Y1, M1, and D1.

The reason we check these two conditions is to see if we need to "borrow" the month or day. The first condition, Y1M1D1 < Y1M0D0, or

ISBEFORE({End Date}, 
DATETIME_PARSE(YEAR({End Date}) &"-"& MONTH({Start Date}) &"-"& DAY({Start Date}),'YYYY-M-D'))

can be stated as "Would the End Date precede the Start Date, if the Start Date were in the same calendar year as the End Date?" If it is true, then the End Date's month comes earlier in the year than the Start Date's month, which means we need to borrow a year; 0 means false, and 1 means true (and we need to borrow a year). The second condition repeats the process, this time with both the year and month of the End Date, and the day of the Start Date, thus telling us if the End Date's day is earlier in the month than the Start Date's; 0 means false, and 2 (because we multiply it by 2) means true (and we need to borrow a month).

The rest of the math should be relatively straightforward: we subtract the Start Date's date components from the End Date's date components. Borrowing a year takes the form of subtracting 1 year and adding 12 months. Borrowing a month is slightly more complicated: we subtract 1 month and add the number of days from the Start Date's day to the end of the End Date's preceding month, as in the formula below:

DAY({End Date}) - DAY({Start Date}) + DAY(DATEADD({End Date}, -DAY({End Date}, 'd')))

So, from the inside out, we first subtract the day component of the End Date from the End Date, giving us the last day of the month preceding the End Date. We then get the day component of that date, giving us the number of days in that month. Working backwards, we take away the day component of the Start Date, giving us the number of days between the Start Date's day component and the end of the month prior to the End Date. Finally, we add the day component of the End Date, which is the number of days elapsed in the month of the End Date.

@Sho Unfortunately, this solution doesn't address the matter of single-day employees either, but should be easier to modify.

(Note: If you're looking carefully at the formula, you'll see that the two conditions at the start are backwards. I did this to avoid any possible confusion with order of operations. Also, this formula could be constructed with IF functions instead, but you'd need to repeat one of the conditions (the ISBEFORE functions). Finally, you can also use ISAFTER, I chose ISBEFORE to keep the simpler expression in front for ease of parsing; note that you can accidentally create an issue with single-day employees where they show up as having worked "-1 year(s), 11 month(s), [28/29/30/31] day(s)", if you replace ISBEFORE with ISAFTER and then number the SWITCH cases in reverse (i.e. 3 to 0).)