Skip to main content

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

  • November 10, 2021
  • 2 replies
  • 49 views
C

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!!

    This topic has been closed for replies.

    2 replies

    C
    • chrisko
    • Author
    • New Participant
    • November 10, 2021

    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.

    Justin_Barrett
    Forum|alt.badge.img+21

    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.


    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.

      Terms & ConditionsAccessibility statement