Excel Tips: Calculating Age. Published: 1. 9 July 2. Revised: 2. 9 April 2. Author: Martin Green. Screenshots: Excel 2. Windows 7. For Excel Versions: All. How Excel Works with Dates.

The TEXT Function converts a value to text in a specific number format. Convert a date value to a day or format a number inside a text string. The DATE function returns the sequential serial number that represents a particular date. Syntax: DATE(year,month,day) The DATE function syntax has the following. Dates and times are two of the most common data types in Excel, but they can be incredibly frustrating to work with. Learn to master Excel dates and times! Excel Functions List with Full Description of Each of Excel's Built-In Functions, Including Examples, Tips and Common Errors.

Excel considers dates as numbers. Each date is assigned a unique. For example, the 2. September 1. 99. 9 was date serial. Fortunately, you don't need to know this but the fact that all. Windows uses the 1. January 1. 90. 0 is date serial 1, 2nd January 1.

Have you ever needed to convert date to number or text in Excel? This article can give you the favor for this job. Excel Formula Examples of Text, Date and Number formulas used in Excel Worksheets. View an alphabetical listing of Excel Tech Tips. Adding Data Forms to the Excel 2010 Quick Access Toolbar. Those of you that enjoyed using the feature.

To find out the serial number of a date, select the cell. Format Cells dialog by (in. Excel 2. 00. 7 and later) expanding the Number section of the Home. Excel 2. 00. 3. and earlier) opening the Format menu and choosing Cells.

List of different formats that makes numbers display in specific formats like dates, currency, and text.

Go to the Number tab and. General in the Category list. The date's serial number. 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 is a number of days later, or subtract a. You can take one date from another to find.

There are lots of ready- made date functions. Working Out a Person's Age. A person's age is the amount of time since they were born (I know you. So, all we have to do is put today's date in one cell and. Well, sort of.. Because you took a date serial from another date.

NOTE below). It looks. In this example the formula in cell A3 is =A1- A2. NOTE: In older versions of Excel you might get another date. 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. Because the first cell was formatted as a. Excel showed you the result as a date, although you wanted to see a. Just reformat the cell manually by (in Excel 2. Format > Cells. General or (in Excel 2. General from. the dropdown in the Format section of the Home tab.

We need to convert this number of days into a number of years. Most. years have 3. So the average. number of years is 3. Let's modify our formula.. In this example the formula in cell A3 is =(A1- A2)/3. Note the brackets around the first part of the formula. Brackets mean. . I've used them here to stop Excel trying.

A2 by 3. 65. 2. 5 before taking it away from A1. Excel formulas do. 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. 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. All. you need to do is place this function into the age calculation formula. In this example the formula in cell A3 is. INT((TODAY()- A2)/3. How Accurate Do You Need to Be? This formula yields pretty accurate results but it isn't infallible.

Supposing the person. Also, because the way this.

When I. updated this tutorial I ran a check again and came up with 2. January 1. 90. 0. But if you are going to do it, you might as well do it right! So how can we get an exact. 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 1.

How old are they? You can't say. The person was born in August 1.

How old are they? You still. can't say. The person was born on 2. August 1. 97. 5. 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.

If they have had their birthday you subtract their birth year. If they have not yet had their birthday you. 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. 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).. 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. YEAR(TODAY())- YEAR(A1).. 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.. Phew! But What About the DATEDIF() Function? Ah yes! Those in the know will tell you that you don't need all this. IFs because Microsoft has provided the DATEFIF().

That's perfectly true but note that I said . Update Catalyst Control Center Linux more. The DATEDIF() function will accurately determine the number of. There is an excellent. Chip Pearson's web site here. But, whilst it is.

Excel, Microsoft has seen fit not to support it in any version. Excel 2. 00. 0. You won't find it mentioned in Help or listed in the. Insert Function tool. If there is a reason for that Microsoft isn't. I prefer to play. And anyway, if you've read this far, you've at least learned.

IFs. About IF Functions. An IF function contains three arguments: IF(Logical Test,Value If True,Value If False)Logical Test: This is a question or circumstance than can be. Value If True: This is what the function should do if the. Value If False: This is what the function should do if the. In other words, you ask a question. If the answer is yes you do one. If the answer is no you do something else.

What the IF function. I've used here). But what if you don't have a simple yes/no question. The solution is. a nested IF function. Instead of inserting a number, piece of text or. IF functions themselves. They normally need a bit of working out.

Excel does. If. it doesn't work first time, try writing it a different way. And try. . If the answer is no do this or. The Incredibles Walkthrough Part 1 Part 1 there.