 # calculate the number of years and months as of today and round to the nearest year

• I am working on a spreadsheet for work to calculate the nearest years and months from today's date. I need this formula to round up to the nearest year. I have the formula worked out to calculate the nearest year, but how do I add the roundup function to the formula? Here is my info:

=DATEDIF(D3,NOW(),"y") & "." & DATEDIF(D3,NOW(),"ym")[/CODE]

for instance, one calculation comes up with 33.11. I need it to round up to 34. Using this spreadsheet for a mail merge.

• Re: calculate the number of years and months as of today and round to the nearest yea

Quick update, I figured it out. I had to add another column and used =ROUNDUP(D3,0)

This gave me the correct outcome, just have multiple columns. It would be nice to have these in the same column if anyone knows how.

• Re: calculate the number of years and months as of today and round to the nearest yea

This maybe?

=DATEDIF(ROUNDUP(D3,0),NOW(),"y") & "." & DATEDIF(ROUNDUP(D3,0),NOW(),"ym")

Ali Enthusiastic self-taught user of MS Excel who's always learning!
If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right. ## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!