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.
SWITCH(
2 * ISBEFORE({End Date}, DATETIME_PARSE(YEAR({End Date}) &"-"& MONTH({End Date}) &"-"& DAY({Start Date}),'YYYY-M-D'))
+ ISBEFORE({End Date}, DATETIME_PARSE(YEAR({End Date}) &"-"& MONTH({Start Date}) &"-"& DAY({Start Date}),'YYYY-M-D'))
,0, YEAR({End Date}) - YEAR({Start Date}) & " year(s)"
& MONTH({End Date}) - MONTH({Start Date}) & " month(s)"
& DAY({End Date}) - DAY({Start Date}) & " day(s)"
,1, YEAR({End Date}) - YEAR({Start Date}) & " year(s)"
& MONTH({End Date}) - MONTH({Start Date}) - 1 & " month(s)"
& DAY({End Date}) - DAY({Start Date}) + DAY(DATEADD({End Date}, -DAY({End Date}, 'd'))) & " day(s)"
,2, YEAR({End Date}) - YEAR({Start Date}) - 1 & " year(s)"
& MONTH({End Date}) - MONTH({Start Date}) + 12 & " month(s)"
& DAY({End Date}) - DAY({Start Date}) & " day(s)"
,3, YEAR({End Date}) - YEAR({Start Date}) - 1 & " year(s)"
& MONTH({End Date}) - MONTH({Start Date}) + 11 & " month(s)"
& DAY({End Date}) - DAY({Start Date}) + DAY(DATEADD({End Date}, -DAY({End Date}, 'd'))) & " day(s)"
)
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.
| Y1M1D1 < Y1M0D0 | 2 * (Y1M1D1 < Y1M1D0) | Sum |
| 0 (False) | 0 (False) | 0, no borrowing required |
| 1 (True, borrow year) | 0 | 1, borrow year |
| 0 | 2 (True, borrow month) | 2, borrow month |
| 1 | 2 | 3, borrow year and month |
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).)
