» Working Out a Person's Age in Microsoft Excel
Tip Contributed by Martin Green.Martin Green, Office Tips is a freelance Access and Excel trainer and developer based in London.How Excel Works with Dates
Excel considers dates as numbers. Each date is assigned a unique serial number. For example, the 27th September 1999 was date serial 36430. Fortunately, you don't need to know this but the fact that all dates have numerical values can be very useful. Windows uses the 1900 date system in which 1st January 1900 is date serial 1, 2nd January 1900 is date serial 2 and so on.
When you type a date into a cell, Excel shows you a date but is thinking of a number.
To find out the serial number of a date, select the cell containing the date then go to Format > Cells. Go to the Number tab and click General in the Category list. The date's serial number will appear in the Sample box on the right.
You can make use of these numbers in all sorts of ways. You can add a number to a date to give a date that number of days later (or subtract a number to get a date before), you can take one date from another to find out how many days in between. There are lots of ready-made date functions too.
Working Out a Person's Age
A person's age is the amount of time since they were born (I know you know that but the computer doesn't, and we have to start thinking like the computer). So, all we have to do is put today's date in one cell and the person's date of birth in another cell, then take their date of birth away from today and you get their age - right? Well, sort of... you get a number. Because you took a date serial from another date serial you get the number of days in between*[note]. It looks like this...
In this example the formula in cell A3 is:
=A1-A2
We need to convert this number of days into a number of years. Most years have 365 days but every fourth year has 366 days. So the average number of years is 365.25. Let's modify our formula...
In this example the formula in cell A3 is:
=(A1-A2)/365.25
Note the brackets around the first part of the formula. Brackets mean "Work out this bit first...". I've used them here to stop Excel trying to divide A2 by 365.25 before taking it away from A1. Excel formulas work do any multiplying and dividing before it does adding and subtracting, but anything in brackets gets done first.
Now we can see a number of years, but it's still not quite right. We are getting an accurate result but we don't really want to see the fraction. As a last refinement we'll wrap the whole thing inside an INT() function to give us a whole number (an integer). This is better than changing the number of decimal places displayed, which would risk some numbers being rounded up and giving an incorrect result. Here's the finished result...
In this example the formula in cell A3 is:
=INT((A1-A2)/365.25)
*Note: In fact, to start with, you get another date. Confused? Don't be... Excel is trying to help but has misunderstood what we need. In date calculations, the result cell gets automatically formatted the same way as the first cell in the formula. Because the first cell was formatted as a date Excel showed you the result as a date, although you wanted to see a number. Just reformat the cell manually by going to Format > Cells > General. [back]
Inserting Today's Date Automatically
You can save yourself the effort of entering today's date manually. Excel has a function, TODAY(), that creates the current date. All you need to do is place this function into the age calculation formula in place of the reference of the cell that had today's date in it...
In this example the formula in cell A2 is:
=INT((TODAY()-A1)/365.25)
How accurate do you need to be?
This formula yields pretty accurate results but it isn't infallible. Dividing by the average number of days in a year works for most people most of the time, but sometimes it gets it wrong. Supposing the person in question is a child, who hasn't yet lived through a 366 day year, you should be dividing by 365 and not 365.25. So how can we get an exact, guaranteed correct figure? Read on...
Using Nested IF Statements to Tell It Like It Is!
Supposing it's August and you need to know old someone is...
- The person was born in 1975. How old are they? You can't say.
- The person was born in August 1975. How old are they? You still can't say.
- A person was born on 23rd August 1975. How old are they? At last! You have enough information. You can say for certain.
In order to calculate someone's age precisely you need to know the year in which they were born, and whether or not hey have had their birthday.
If the have had their birthday you subtract their birth year from the current year. If they have not yet had their birthday you subtract their birth year from the current year, and then subtract 1. Easy! We do it all the time without thinking about it. But explaining the rules to Excel is a bit more complicated. Here goes...
=IF(MONTH(TODAY())>MONTH(A1),YEAR(TODAY())-YEAR(A1),
IF(AND(MONTH(TODAY())=MONTH(A1),DAY(TODAY())>=DAY(A1)),
YEAR(TODAY())-YEAR(A1),(YEAR(TODAY())-YEAR(A1))-1))
I've written this calculation on three lines for clarity but you should write is as a single expression without spaces. It assumes that cell A1 contains the person's date of birth. Here's what it says...
- IF(MONTH(TODAY())>MONTH(A1)
If this month is later than the month of the persons birthday... - YEAR(TODAY())-YEAR(A1)
...subtract the year in which they were born from this year because they must have had their birthday.
But what if we haven't passed the month in which they were born. We might be in that month, or we might not have reached it yet. Let's find out...
- IF(AND(MONTH(TODAY())=MONTH(A1),DAY(TODAY())>=DAY(A1))
If we are currently in the month of the person's birthday and it is either their birthday today or we have passed it... - YEAR(TODAY())-YEAR(A1)
...subtract the year in which they were born from this year because they must have had their birthday.
But what if this isn't the month in which they were born. We know we haven't passed their birthday so...
- (YEAR(TODAY())-YEAR(A1))-1
...subtract the year in which they were born from this year then subtract 1, because they haven't had their birthday yet.
Phew!
About IF Statements
An IF statement contains three parts:
IF(Logical Test,Value If True,Value If False)
Logical Test: This is a question or circumstance than can be answered yes or no, or true or false.
Value If True: This is what the function should do if the answer to the test is yes or true.
Value If False: This is what the function should do if the answer to the test is no or false.
In other words, you ask a question. If the answer is yes you do one thing. If the answer is no you do something else. What the IF statement does could be to display a number or piece of text, or it can do a calculation (which is what is does in the example I've used here).
But what if you don't have a simple yes/no question. The solution is a nested IF statement. Instead of inserting a number, piece of text or calculation, the value if true or value if false part (or both parts) can be IF statements themselves. They normally need a bit of working out (!) because they have to use the same logical process as Excel does. If it doesn't work first time, try writing it a different way. And try "translating" it into English as I did above.
A nested IF statement says something like...
"If the answer is yes, do this. If the answer is no do this or this (depending on..."
And if you think the Age Calculator is complicated, consider the fact that Excel can handle up to seven (count them!) nested IF statements in a single calculation.
Book Store:
Recommended Books:
=DATEDIF(A1,NOW(),"y")
That is nothing to do with Excel directly.
You have your computer set up to make that assumption, and Excel has followed your instructions accordingly.
Check out your date settings under Regional Settings.
Alan.
When you enter a date in a cell and you enter only two digits for the year, Excel interprets the year as follows:
The years 2000 through 2029 if you type 00 through 29 for the year. For example, if you type 5/28/19, Excel assumes the date is May 28, 2019.
The years 1930 through 1999 if you type 30 through 99 for the year. For example, if you type 5/28/98, Excel assumes the date is May 28, 1998.
If you are using Microsoft Windows 98 or Microsoft Windows 2000, you can, without the assistance of your system administrator, change the way two-digit years are interpreted.
By using the formula =INT((TODAY()-A1)/365.25), I am getting a whole number as age (e.g. 40).
Now if I want to have the number of month also (Years+Months e.g. 40.3) and when I want to merge with word, the same age will appear with one decimal only (e.g. 40.3) and not fifteen (15) decimals (as 40.29847562534987)
Which formula I have to use.
Many Thanks.
Please can you clarify a little.
The problem is that a month is not a well defined period of time (can be anything from 28 days to 31 days inclusive).
Therefore, it is difficult to use a 'month' as a measure of time (as opposed to an absolute time reference for which it is very well defined).
The same problem arises for a 'year' - as discussed above. A year can be either 365 or 366 days, and is therefore, not well defined either as a measure of time. What exactly would you mean by 0.3 of a year?
Excel has functions that will 'standardise' those period, and resolve the issue that you are encountering by assuming that every year has 360 days, and every month exactly 30 days. See Excel help on the DAYS360 function.
In general, I would suggest that you steer clear of representing someones age as "40.3" if at all possible. Giving the age to three significant figures, implicitly leads the reader to assume a level of accuracy that may not be borne out of you ask a selection of people what it means.
If you have to do this, then you need to define your units explicitly for the reader of the report (and other users of the spreadsheet model) before you start so that everyone knows exactly what you mean.
Does that help? Post back if you want more help, but I suggest that the 360 day year has to be the way to go for you, even though it leads to errors close to a person's birthday.
Alan.
Many thanks for your answer.
It is mail merge between Word and Excel, and my target is to select people using age with one decimal only (as mentioned already).
For example, When I am inserting the date of birth in A1(which is = 14 June 1962) and in B1 (=NOW()) and in another cell, I am using the follwing formula: =IF(A1=0;"";((B1-A1)+1/365). I am getting the following result 41.25018031 formatted in number with one decimal only 41.3.
The problem is when I am reporting this age to my document (Word) the result is as follow: 41.2501533878744171 but I need only to have 41.3 nothing else ... Can you help to resolve this problem by keeping at least the same formula mentioned above.
Thanks
If you change your formula to:
=Round(IF(A1=0;"";((B1-A1)+1/365),1)
then this will change the actual value to be rounded to one decimal place.
Does that work for you?
Alan.
Unfortunately, it does not work with me.
Many thanks for your assistance.
Rebouche
You are right - it doesn't!
I copied your formula from your post, and just put the ROUND outside of it. I should have done it in Excel and pasted to here:
=IF(A1=0,"",ROUND(((B1-A1)+1)/365,1))
Does that work?
Alan.
Using your corrected formula, and when I pass to my document, I am always getting 15 (fifteen) decimals instead of (1) one.
Meawhile, I replaced the (,) by (;).
Thanking you,
Rebouche
I am not familiar with mail merge, but perhaps someone else will be able to help you with why it changesthe value back to fifteen decimals.
The value returned by the formula will either be "" (blank) or will be a number to one decimal place.
However, as a workaround, you could perhaps copy and paste the answers to values, thus removing the formulae, and leaving only the values (to one decimal place), before you mail merge.
Would that work for you?
Alan.
Excel Formula and Function
Excel Date and Time
Excel Macros and VBA
Excel Text, Editing and Format
Excel General
Excel Data
