Greetings HoneyCoders,

Have a super-noob question. Fairly familiar with spreadsheets and am doing a simple spreadsheet to calculate a persons age to kick off this new learning. I have kept the cell formatting as auto and also tried to format the columns in applicable formats

I started with a super simple 3 column setup




That gave me a Circular Ref error

Then I decided to split it up



Seems logical that both of these should work. Something extremely simple I'm missing I'm sure, can anyone give me a clue? I'm about to go read / watch some intro spreadsheet formula tutorials I guess. If I find the answer I'll post back.

Thanks and have a great one!

Hi @Seba-8461,

Welcome to the community! :honeybee:

Thanks for posting here, I can show you how to use date and time functions to achieve this.

One way to get the age, would be to subtract the years from two dates. I made a table of this to show you with a Birthday and Today column, and a third column with the following formula: =YEAR(TODAY())-YEAR([Birthday])

If you want to break it down further to get the date difference in months and days, you can do the similar structure with the MONTH() and DAY() formulas. Just to demonstrate this, I broke them down into separate columns to show you:

And can then create a string with CONCATENATE() to display a user's age as desired.

In my example, I used =CONCATENATE([Age (Years)]," Years, ",[Age (Months)]," Months, and ",[Age (Days)]," Days old")

Let me know if this helps! :slightly_smiling_face:

1 Like

This helps immensely!

Can you explain why the two methods I tried don’t work? Are they both just fundamentally wrong or is it something like the cells not knowing what the data type is or something like that? Knowing why these failed will help me learn more and how to fail in new and interesting ways in the future (to learn even more) :laughing:

Thank you so much!

1 Like

Hi @Seba-8461,

Awesome, I'm happy that helped! :star2:

And sure, I'm happy to explain :slight_smile: First off, the ~CIRCULAR~REF~ error happened because in your formula, you're having the column you're located in trying to call itself (In this case, the [Age] column). It's referring to its own value, which will result in an error.

And looking at your formula again, I'm realizing there's was just a minor fix for it. You would enter:

Rather than calling the [Age] column, you would put "Y" for years, "M" for months, or "D" for days. The difference in this compared to my example above is that it would show you the total months and days between those ages, rather than just the difference in the months and days in the date.

So for example with DATEDIF():

Just another way to play with date and time differences :clock1030:

Let me know if you have any further questions on this. :slight_smile:


This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.