How To Find Age From Date Of Birth In Excel

So, there I was, staring at a spreadsheet, feeling like a detective faced with a mystery. The case? A list of birthdates, and no obvious way to find out the ages. Now, I could've been old-school and calculated each one manually, but who's got time for that? Certainly not this Excel sleuth. So, I rolled up my sleeves and dived into the world of Excel formulas.

Introducing DATEDIF

Meet your new best friend, DATEDIF. It's an Excel function that calculates the difference between two dates. Now, you might be thinking, "But I want to know the age, not the difference in dates!" Ah, my eager learner, that's where the magic happens.

Step 1: Set Up Your Dates

First things first, let's assume you've got your birthdates in column A, starting from A2. If you don't, well, you know what they say about assuming. Now, in cell B2, type in today's date using the formula "=TODAY()". This will give you the current date, which we'll use to calculate the age.

Step 2: The Magic Formula

Now, here's where DATEDIF comes in. In cell C2, type the following formula: "=DATEDIF(A2, B2, "y")". This tells Excel to calculate the difference between the date in A2 (the birthdate) and the date in B2 (today's date), and to give us the result in years ("y").

But wait, there's more! Excel's a bit sneaky with its ages. It'll give you the age up until the last birthday. So, if today's your birthday, Excel will still give you last year's age. To fix this, we'll use a bit of conditional formatting.

Step 3: Conditional Formatting

Select the cells with your ages (C2 and down), then go to the "Home" tab, click on "Conditional Formatting", then "New Rule". In the dialog box, choose "Use a formula to determine which cells to format". In the box below, type "=TODAY() - A2 < 365". This tells Excel to format the cell if the difference between today and the birthdate is less than a year (i.e., it's their birthday this year).

How to Calculate Age in Excel (In Easy Steps)

Now, choose the formatting you want (I like red, it's... attention-grabbing), then click "OK". Voila! Your ages are all calculated, and the birthday folks are highlighted. You can copy this formula down as many rows as you need.

But What If...?

What if you want the age in days or months? Easy peasy! Just change the "y" in the DATEDIF formula to "d" for days or "m" for months. And if you want to see the age as of a specific date, just replace "=TODAY()" with the date you want.

And there you have it, folks! You're now an age-calculating machine. Happy sleuthing!