CODEWITHSUNDEEP
exceldateexcel-formula
user8837495
user8837495

Reputation: 51

Converting date (age) into a number in Excel

I have made an age calculator to calculate the age of people participating in a test I'm making. I need to use the age (year and month) to further down the line calculate their final score (a person between 5 year and 2 months would get a different score than a person who is 5 years and 8 months, even if they had the same amount of right answers.)

Is there any way to convert the age (5 years and 2 months) into a number I can use in an IF-statement? E.g. =IF(AND(B6>5,0;B6<5,02);"TRUE";"FALSE")

enter image description here

Upvotes: 0

Views: 185

Answers (3)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60174

Excel stores dates as the number of days since 1-Jan-1900. Since years and months have varying number of days, I would suggest you use the days difference for the different scores and VLOOKUP for the different date ranges (in days). In your example, days difference would be given by. for example ="2010-3-1" - "2005-1-1"

Or, depending on how your original data is set up:

= test_date - birth_date

where test_date refers to a cell containing the full date, and the same for birth_date

If your original data is as you show in your screenshot, then you have to build the dates:

=DATE(B2,C2,D2) - DATE(B3,C3,D3)

-

Upvotes: 1

Sudipto Duyari
Sudipto Duyari

Reputation: 1

You can convert your age difference to number with Try the formula =Value(Concat(B4,C4,D4))

Upvotes: 0

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96753

With the age in years in A1 and the months in A2, in A3 enter:

=A1+A2/12

enter image description here

This is a rough value that is "good enough" to compare the ages of two people if all you have is their ages in years and months.

(the approximation assumes that one month is one twelfth of a year)

Upvotes: 1

Related Questions