# Re: Convert months into the formatting of "[x] years, [y] months"

145 1
cancel
Showing results for
Did you mean:  4 - Data Explorer

Hiya!

I’m trying to calculate employee tenure with DATETIME_DIFF. I’m able to show it by years, months, or days. What I’d ideally like is to break it down to say: 3 years, 2 months.

So I have a field for start date and end date (as screenshot suggests). I have the formula applying `today()` if they are still employed. Just can’t figure out how to get that month figure to be months into the current year.

Thought I’d add my current formula.

``````DATETIME_DIFF(
IF({End Date}=BLANK(),TODAY(),{End Date}),
{Start Date}, 'Years') & " Years, "
& DATETIME_DIFF(
IF({End Date}=BLANK(),TODAY(),{End Date}),
{Start Date}, 'M') & " Months"
``````

Thanks!! 2 Replies 2  4 - Data Explorer

I think I may have gotten it in a not very elegant way (but it works lol)

``````DATETIME_DIFF(
IF({End Date}=BLANK(),TODAY(),{End Date}), {Start Date}, 'Years')
& " Years, "
& ((DATETIME_DIFF(IF({End Date}=BLANK(),TODAY(),{End Date}), {Start Date}, 'M'))
- (12 * DATETIME_DIFF(IF({End Date}=BLANK(),TODAY(),{End Date}), {Start Date}, 'Y')))
& " Months"
``````

Basically what I did was repeat the formula to get the years. I used that to multiply by 12. I subtracted that from the original month calculation to get the months into the current year. It’s not pretty but it works… lol.  18 - Pluto

Welcome to the community, @chrisko! :grinning_face_with_big_eyes: Yup, that’s pretty much the process. The logic to use either `TODAY()` or `{End Date}` can be simplified a bit to omit the `BLANK()` function. With the exception of numeric fields1, you can use this pattern:

``````IF({Field Name}, result_if_field_is_full, result_if_field_is_empty)
``````

A non-empty field is equivalent to `True` (or truthy), and an empty field is equivalent to `False` (or falsy).

That simplification turns your formula into this:

``````DATETIME_DIFF(
IF({End Date},{End Date},TODAY()), {Start Date}, 'Years')
& " Years, "
& ((DATETIME_DIFF(IF({End Date},{End Date},TODAY()), {Start Date}, 'M'))
- (12 * DATETIME_DIFF(IF({End Date},{End Date},TODAY()), {Start Date}, 'Y')))
& " Months"
``````

1 For a numeric field, concatenate the field name with an empty string:

``````IF({Field Name} & "", result_if_field_is_full, result_if_field_is_empty)
``````

This is required because a value of 0 is also falsy. Even though a 0 in a field makes it non-empty, the first formula above would still treat it as false. Concatenating a number with an empty string turns it into a string—0 becomes “0”, and a non-empty string is also truthy. 