What is the formula in calculating the years, months, and days of service in a given date?

Hi Everyone,

Can you pls help me figure out what is wrong with my formula? I wanted to get the exact, years, months, and days of service of our employees. I was able to do this in gsheet, but having a hard time here.

=YEAR(TODAY()) - YEAR([Date Hired]10) - IF(DATE(YEAR([Date Hired]10), MONTH(TODAY()), DAY(TODAY())) < [Date Hired]10, 1, 0) + " years " + IF(MONTH(TODAY()) - MONTH([Date Hired]10) < 0, 12 + MONTH(TODAY()) - MONTH([Date Hired]10), MONTH(TODAY()) - MONTH([Date Hired]10)) + " months " + IF(DAY(TODAY()) - DAY([Date Hired]10) < 0, 30 + DAY(TODAY()) - DAY([Date Hired]10), DAY(TODAY()) - DAY([Date Hired]10)) + " days"

Calculating inside the box is correct, but the rest seems to count backward. What do I need to change here? Appreciate your help.

Answers

  • J Tech
    J Tech ✭✭✭✭✭

    Hi @Mitch18

    =YEAR(TODAY()) - YEAR([Date Hired]@row) - IF(DATE(YEAR([Date Hired]@row), MONTH(TODAY()), DAY(TODAY())) < [Date Hired]@row, 1, 0) + " years " + IF(MONTH(TODAY()) - MONTH([Date Hired]@row) < 0, 12 + MONTH(TODAY()) - MONTH([Date Hired]@row), MONTH(TODAY()) - MONTH([Date Hired]@row)) + " months " + IF(DAY(TODAY()) - DAY([Date Hired]@row) < 0, 30 + DAY(TODAY()) - DAY([Date Hired]@row), DAY(TODAY()) - DAY([Date Hired]@row)) + " days"


    This should work to calculate the years, months, and days of service for each employee based on their individual hire date.

    Regards

    J Tech

    If my response has helped you in any way or provided a solution to your problem, please consider supporting the community by marking it as Insightful, Vote Up, or Awesome. Additionally, you can mark it as the accepted answer, which will make it easier for others to find a solution or get help with a similar issue in the future. Your support is greatly appreciated!
  • Mitch18
    Mitch18 ✭✭

    Hi @J Tech

    Appreciate the help, however, it is the same formula that I am currently using. It is working, however, with regard to future dates the calculation is different. See the screenshot below, Feb 22, 2023, should only be just 28 days, and Feb 21, 2023, should be 29 days.

    What is need to be changed or added to the formula to get the exact calculation for future dates too?

  • J Tech
    J Tech ✭✭✭✭✭

    Hi @Mitch18

    Try =YEAR(TODAY()) - YEAR([Date Hired]@row) - IF(DATE(YEAR([Date Hired]@row), MONTH(TODAY()), DAY(TODAY())) < [Date Hired]@row, 1, 0) + " years " + IF(MONTH(TODAY()) - MONTH([Date Hired]@row) < 0, 12 + MONTH(TODAY()) - MONTH([Date Hired]@row), MONTH(TODAY()) - (MONTH([Date Hired]@row)) - 1) + " months " + IF(DAY(TODAY()) - DAY([Date Hired]@row) < 0, 30 + DAY(TODAY()) - DAY([Date Hired]@row), DAY(TODAY()) - DAY([Date Hired]@row)) + " days"

    Regards

    J Tech

    If my response has helped you in any way or provided a solution to your problem, please consider supporting the community by marking it as Insightful, Vote Up, or Awesome. Additionally, you can mark it as the accepted answer, which will make it easier for others to find a solution or get help with a similar issue in the future. Your support is greatly appreciated!
  • Mitch18
    Mitch18 ✭✭

    Hi @J Tech

    I tried that however, it affects the months in the past dates. See the screenshot below.


    Column A, is the formula and it's calculating ok but not in the future dates:

    =YEAR(TODAY()) - YEAR([Date Hired]@row) - IF(DATE(YEAR([Date Hired]@row), MONTH(TODAY()), DAY(TODAY())) < [Date Hired]@row, 1, 0) + " years " + IF(MONTH(TODAY()) - MONTH([Date Hired]@row) < 0, 12 + MONTH(TODAY()) - MONTH([Date Hired]@row), MONTH(TODAY()) - MONTH([Date Hired]@row)) + " months " + IF(DAY(TODAY()) - DAY([Date Hired]@row) < 0, 30 + DAY(TODAY()) - DAY([Date Hired]@row), DAY(TODAY()) - DAY([Date Hired]@row)) + " days"

    Column B is the formula you have given and which is not calculating the future and affected the past dates:

    YEAR(TODAY()) - YEAR([Date Hired]@row) - IF(DATE(YEAR([Date Hired]@row), MONTH(TODAY()), DAY(TODAY())) < [Date Hired]@row, 1, 0) + " years " + IF(MONTH(TODAY()) - MONTH([Date Hired]@row) < 0, 12 + MONTH(TODAY()) - MONTH([Date Hired]@row), MONTH(TODAY()) - (MONTH([Date Hired]@row)) - 1) + " months " + IF(DAY(TODAY()) - DAY([Date Hired]@row) < 0, 30 + DAY(TODAY()) - DAY([Date Hired]@row), DAY(TODAY()) - DAY([Date Hired]@row)) + " days"

    I don't know how to combine these 2 so we'll have the accurate length of service with the given hired date.

  • Jason Albrecht
    Jason Albrecht ✭✭✭✭✭✭

    Hi Mitch18,

    Building on @J Tech insightful and helpful formula, and if I understand the question correctly, would it help to start the formula with something like 'if the column date is greater than todays date, display 0 years 0 months 0 days, otherwise follow the rest of the formula' i.e.,

    IF([Date Hired]@row > TODAY(), "0 years, 0 months, 0 days", ...)

    Hope this helps and that you have a great day,

    Jason Albrecht MBA, MBus(AppFin), DipFinMgt

    LinkedIn profile - Open to work

  • Mitch18
    Mitch18 ✭✭

    Hi Jason,

    Appreciate your response, however, I am getting the same issue. I don't know what seems to be the problem in calculating future dates here in smartsheet, in gsheet it is calculating accurately. Kindly see the screenshot below.

    here's the formula I used in smartsheet and I am getting this result.

    =IF([Date Hired]@row > TODAY(), "0 years, 0 months, 0 days", YEAR(TODAY()) - YEAR([Date Hired]@row) - IF(DATE(YEAR([Date Hired]@row), MONTH(TODAY()), DAY(TODAY())) < [Date Hired]@row, 1, 0) + " years " + IF(MONTH(TODAY()) - MONTH([Date Hired]@row) < 0, 12 + MONTH(TODAY()) - MONTH([Date Hired]@row), MONTH(TODAY()) - MONTH([Date Hired]@row)) + " months " + IF(DAY(TODAY()) - DAY([Date Hired]@row) < 0, 30 + DAY(TODAY()) - DAY([Date Hired]@row), DAY(TODAY()) - DAY([Date Hired]@row)) + " days")

    here's what I've got in gsheet,

    First time using smartsheet and really don't know how to resolve this.

  • Jason Albrecht
    Jason Albrecht ✭✭✭✭✭✭

    Hi @Mitch18

    May I suggest using the NETDAYS Function for calculating the number of days between the dates?

    I suspect that 'adding 30' to the date will throw out the days, since not every month has 30 days.

    it might look something like: ... + NETDAYS([Date Hired]@row, TODAY()) + " days")

    Hope this helps?

    Hope this helps and that you have a great day,

    Jason Albrecht MBA, MBus(AppFin), DipFinMgt

    LinkedIn profile - Open to work

  • Mitch18
    Mitch18 ✭✭

    Thank you @Jason Albrecht, I tried that but it's calculating the entire days from the date given :(

  • @Mitch18 Try my solution here

    https://community.smartsheet.com/discussion/comment/446982/

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!