How to calculate age in Google Sheets using a specific date?

Hey everyone! I’m working on a spreadsheet to figure out people’s ages. But here’s the thing: I don’t want to use today’s date. I need to calculate their ages as of September 1, 2011.

I’ve got this formula that works with today’s date:

=YEAR(TODAY())-YEAR(C2)-1 + (MONTH(TODAY())>MONTH(C2)) + (MONTH(C2)=MONTH(TODAY()))*(DAY(TODAY())>=DAY(C2))

The birthdays are in column C. How can I change this formula to use a fixed date instead of TODAY()? I’m not sure how to modify it to use September 1, 2011, as the reference date.

Any help would be awesome! I’m pretty new to Google Sheets formulas, so a simple explanation would be great. Thanks in advance!

I’ve encountered a similar challenge before. Here’s a simplified approach you might find useful:

=DATEDIF(C2, DATE(2011,9,1), “Y”)

This formula calculates the difference in years between the birthday in C2 and September 1, 2011. The “Y” parameter tells DATEDIF to return complete years.

If you need more precision, you can use:

=DATEDIF(C2, DATE(2011,9,1), “Y”) & " years, " & DATEDIF(C2, DATE(2011,9,1), “YM”) & " months"

This will give you the age in years and months. It’s more straightforward than the complex formula you’re currently using and easier to modify for different reference dates.

hey luke! i think i got ya covered. try replacing TODAY() with DATE(2011,9,1) in ur formula. so it’d look like this:

=YEAR(DATE(2011,9,1))-YEAR(C2)-1 + (MONTH(DATE(2011,9,1))>MONTH(C2)) + (MONTH(C2)=MONTH(DATE(2011,9,1)))*(DAY(DATE(2011,9,1))>=DAY(C2))

that should do the trick! lmk if u need anything else

I’ve been in your shoes, Luke. When I was working on a project for my local community center, we needed to calculate ages for a specific date too. Here’s what worked for me:

=INT((DATE(2011,9,1) - C2) / 365.25)

This formula takes the difference between your fixed date (Sept 1, 2011) and the birthday, then divides it by 365.25 (accounting for leap years) to get the age in years. The INT function rounds down to ensure you’re not counting partial years.

It’s simpler than the original formula and gets the job done. Just remember, it might be off by a day or two for some edge cases, but for most purposes, it’s accurate enough. Hope this helps!