Can Excel convert a date to an age?

[Thurst]

I know there are many ways to do this very simpily but I'm difficult. I don't want to put in a number then in the next column use a formula like today-A1. I want to be able put a date in, like 1/1/1990 and Excel convert that to 24. The specific file I'm taking over is over 700 entries and the age is put in as text so you have to constantly go in and update it. It would be impossible to keep track of. Can excel do that for me?

[JBeaucaire]

The DATEDIF() function will do that:

=DATEDIF(A1, TODAY(), "y")

[Thurst]

But that requires you to have the date in A1 and the age would show up in A2 correct? All I want to see is the age.

[alansidman]

What you are asking for can only be accomplished with VBA. Would that work for you?

[Tony Valko]

Then you'd have to enter the start date in the formula:

=DATEDIF(DATE(1990,1,1)TODAY(),"y")

[alansidman]

Here is a VBA solution

Please Login or Register  to view this content.

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

1. Press Alt-F8 to open the macro list
2. Select a macro in the list
3. Click the Run button

[JBeaucaire]

I really have to interject, if you've taken over this file, then adding a column for "Date of Birth" or "DOB" would be the correct design decision. I don't think I've ever seen a database where people's age is depicted without that same database have the actual DOB included so the calculation is automatic.

Your use of VBA to manually change a date to an age is STILL not solving the problem, it's just giving you an additional method to have the final result be less than optimal. A year later you're going to be back in this sheet typing in dates again to "convert" them to dates, by any of the methods being suggested.

1) Add a DOB column
2) Enter the dates one time
3) Change the AGE column to one of the formulas given
4) Never visit this issue ever again.

Now that's "control" of your form.