MacMost Q&A Forum • View All Forum QuestionsAsk a Question

How Do I Calculate the Time Elapsed Between Two Dates In Years, Months and Days Using Numbers?

I want to calculate the time in Years, Months and Days between todays date and some events in the past.

I want to store these values in a spreadsheet so that I can use the TODAY function, and have the durations recalculate whenever I open the spreadsheet.

Due to a family issue I have to complete forms that are stored on a Government Website. I need to set up an account to do this.

Part of the set up process for this account is an ID Check to prove it’s me.

Personal Circumstances meant that the available ID Checks were limited, and on this occasion I was asked questions about my Credit Report. The first time this has ever happened.

One of the questions I was asked was “When did I last open a current account?”. The answer was multiple choice: Last 2 years, 3-5 years, 5 years or more.

How long have you lived at your current address?

Calculating this sort of timing spurred my curiosity, and my interest in setting up the spreadsheet described above.

I have managed to calculate the years and months that have elapsed quite easily I think. I can just use the DATEDIF function on the two dates with Y as the Calc Method for the years and then “YM” for months. However, I’m a bit stumped with the days elapsed.
Malcolm James

Comments: 2 Responses to “How Do I Calculate the Time Elapsed Between Two Dates In Years, Months and Days Using Numbers?”

    2 years ago

    You don't need to use any function at all to calculate the basic duration between two dates. Just subtract one date from the other and you get the answer.

    So if the cell B2 holds a date, then in C2 you can put the formula NOW-B2 and you get the duration in days, hours, minutes, and seconds.

    Now if you want to calculate something else, it gets tricky. That's because years and months don't have a consistent length. Months can be 28, 29, 30 or 31 days long. Years can be 365 or 366 days long.

    But you can convert the dates to years and subtract them to get that. So:
    YEAR(NOW)-YEAR(B2) gives you the numbers of years that have passed.

    But months are a bit of a nightmare. There are always 12 months in a year, so you can calculate the years, multiply by 12, and then add the difference in months and I think that works out. Sometimes the months are negative, but that works out:
    12*(YEAR(NOW)-YEAR(B2)) + (MONTH(NOW)-MONTH(B2))

    I think that is right, but the only problem is it doesn't round correctly. March 31 is treated just like March 1 since only the month is used.

    Anyway, I hope this is enough to get you pointed in the right direction.

    2 years ago

    I know this is an old post... but, this may work

    =DATEDIF(B2,NOW(),"Y") & " years, " & DATEDIF(B2,NOW(),"YM") & " months, and " & DATEDIF(B2,NOW(),"MD") & " days"

Comments Closed.