If you have a cell that contains the function =NOW(), let’s say that is in A1, and another cell, B1, that contains a date, and a third cell that contains =A1 - B1, that will return the elapsed time between the two dates in days.
If you use the function Now() Excel will tell you the current date and time. If A1 has the date you want to check:
=Now()-A1
Then format it to display how you want.
The above answers will give you the number of days since a given date. The OP asked for “days, months, years”, but that’s not well defined since different months are not the same length, and similarly different (calendar) years are not the same length. So if the OP really wants months and years, they need to clarify what they mean by that.
Dates and times in Excel confuse a lot of people because what’s going on under the hood is not obvious. And the various formatting options further mask the difference between the *value *Excel is actually dealing with versus what you see displayed on the screen in the cell.
I would never use NOW() to compare with a date. Use TODAY() instead. Other than that quibble the earlier advice is good as far as it goes.
If you (OP) want to understand, instead of just copying recipes blindly, let us know. Somebody will be happy to oblige.
Thinking it over, I don’t need that precision I suggested in my OP, just the value in years.
Once I do the TODAY subtract the starting year, that gives me the number of elapsed days, correct?
So I could just do another operation, where the number of elapsed days is divided by 365.25, round it to two decimal places, and that gives me the number I need.
(My data table has approx. 200 dates in it, and I don’t need exact precision (Years, months, days) for every single one.)
Dates in Excel are stored as the decimal number of days since 12/31/1899, where 0:00 on 1/1/1900 = 1. The decimal part is fractions of a day, so for example 1/24 = 1 hour.
You can do date arithmetic with dates in Excel, but if you try to display a negative number as a date Excel will barf and your cell will fill with “#” signs.
In your case, the advice above is to use the TODAY function, which will give the date value for the current date. If you subtract your other date from TODAY, the result is a decimal number that gives the number of days elapsed. You can use various functions to display that difference in days/months/years. Here is one option:
=TEXT(TODAY()-A1,“d ““days”” m ““months”” y ““years”””)
DATEDIF referenced in a post above also works although it is included for compatibility with old Lotus 1-2-3 files and does seem to have a known issue.
You don’t need to do all that; you can let Excel do it for you:
=TEXT(TODAY()-A1,“y ““years”””)
The difference is that TODAY gives only the integer portion which will only tell you what day it is, and NOW gives the full decimal version which also tells you what time it is.
Go here and scroll down to the section on “Converting… calendar date to Julian day number.” There is a formula there that outputs the day number given the year, month, and day, even if the year is in the 17th century. Then you can subtract.
Once it gets to be after 12:00 noon on any day, the fractional part of any subtraction or addition involving a pure date (i.e. an integer) and NOW() will have a fractional part > 0.5. Now introduce some rounding in further calculations and you’re off by a day. Oops.
Given how little people understand the fundamentals of how dates, times, and datetimes are stored and formatted, randomly throwing ever-changing fractions between 0.00000001 and 0.9999999 into the stew is just asking for errors.
That’s why you use TODAY() instead of NOW() when your values are pure dates.