FORMULA FROM DATE TO DATE

Options

Good day all,

My questions is, I need to know from my list the age (years & Months) of the person at the time I received the approval of their requets. I have it in excel =DATEDIF(D3;F3;"Y") . Can someone help me make this formula for my spreadsheet?

Regards,

Laura G.

Best Answer

  • Jeffrey_WRK
    Jeffrey_WRK ✭✭✭
    Answer ✓
    Options

    Your Excel formula only gives years, not years and months. Here's a few options.

    If Date1 is their birthday and Date2 is their request date, then you can get years by this:

    =ROUNDDOWN(NETDAYS([Date1]@row, [Date2]@row) / 365, 0)

    RESULT: 62

    -------------------------------

    If you want decimal years, use it without the rounding (you may be a few days off due to leap years):

    =NETDAYS([Date1]@row, [Date2]@row) / 365

    RESULT: 62.93

    ------------------------------

    If you want years and months, it's more complicated, but leap years won't have an impact (the result is "YEARS / MONTHS"):

    =IF(MONTH([Date2]@row) - MONTH([Date1]@row) < 0, ((YEAR([Date2]@row) - YEAR([Date1]@row) - 1) + " / " + (MONTH([Date2]@row) - MONTH([Date1]@row) + 12)), (YEAR([Date2]@row) - YEAR([Date1]@row)) + " / " + (MONTH([Date2]@row) - MONTH([Date1]@row)))

    RESULT: 62 / 11

    -------------------------------

    If you don't mind 2 helper columns plus the result column, you can achieve the above results with simpler formulas:

    Helper column: MONTHS = (MONTH([Date2]@row) - MONTH([Date1]@row))

    Helper column: YEARS = YEAR([Date2]@row) - YEAR([Date1]@row)

    Result column: Years / Months = IF(MONTHS@row < 0, (YEARS@row - 1) + " /" + (MONTHS@row + 12), YEARS@row + " / " + MONTHS@row)

    RESULT: 62 / 11

Answers

  • Jeffrey_WRK
    Jeffrey_WRK ✭✭✭
    Answer ✓
    Options

    Your Excel formula only gives years, not years and months. Here's a few options.

    If Date1 is their birthday and Date2 is their request date, then you can get years by this:

    =ROUNDDOWN(NETDAYS([Date1]@row, [Date2]@row) / 365, 0)

    RESULT: 62

    -------------------------------

    If you want decimal years, use it without the rounding (you may be a few days off due to leap years):

    =NETDAYS([Date1]@row, [Date2]@row) / 365

    RESULT: 62.93

    ------------------------------

    If you want years and months, it's more complicated, but leap years won't have an impact (the result is "YEARS / MONTHS"):

    =IF(MONTH([Date2]@row) - MONTH([Date1]@row) < 0, ((YEAR([Date2]@row) - YEAR([Date1]@row) - 1) + " / " + (MONTH([Date2]@row) - MONTH([Date1]@row) + 12)), (YEAR([Date2]@row) - YEAR([Date1]@row)) + " / " + (MONTH([Date2]@row) - MONTH([Date1]@row)))

    RESULT: 62 / 11

    -------------------------------

    If you don't mind 2 helper columns plus the result column, you can achieve the above results with simpler formulas:

    Helper column: MONTHS = (MONTH([Date2]@row) - MONTH([Date1]@row))

    Helper column: YEARS = YEAR([Date2]@row) - YEAR([Date1]@row)

    Result column: Years / Months = IF(MONTHS@row < 0, (YEARS@row - 1) + " /" + (MONTHS@row + 12), YEARS@row + " / " + MONTHS@row)

    RESULT: 62 / 11

  • LauraGallego
    Options

    Thank you so much! :) I was able to do my whole smartheet.


    Regards,

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!