Kathleen wrote: My column/row FQ2 holds your formula. Each row may have a different static date.: Resulting in cell FQ2 holding the formula =DATEDIF(AR2,GD2,'y')+IF(DAY(AR2)=29,EDATE(AR2,12.DATEDIF(AR2,GD2,'y')+12)=GD2,0) Unless I have the formula wrong again, then something is not loaded properly in my Excel program. Not clear to me whether the #NAME error persists, or if you have solved the problem somehow. You marked my speculations as 'the answer'; but it seems like the error persists. However, I do not see a new thread to initiate (restart) a discussion of it. Anyway, I should have suggested the following at the outset.

• Date Of Birth Calculator From Date Of Death

Age Calculation in Excel - How to Calculate Age in Excel up to the Current Date or to a Specified Date. In this post, you will learn How To Calculate Age From Date of Birth In Excel by creating formula with DATEDIF and TODAY. Microsoft Excel provides a function that displays a person's age based on his date of birth. In your business, this function can help you keep track of your workers.

When faced with a mystifying problem, 'divide and conquer'. First, open a new workbook. That is, close all instances of Excel, and double-click the Excel icon on the Desktop. Enter 2/29/1980 into A1 and 2/28/2010 into A2. Put the following formula into A3: =DATEDIF(A1,A2,'y') There should be no error. It should result in 29.

Now put the following formula into A4: =DAY(A1) There should be no error. It should result in 29. Finally, put the following formula into A5: =EDATE(A1,12) You should see the date 2/28/1981, but probably only after you format the cell as Date (click Format Cells Number, and select Date). But do you actually see a #NAME error? If you do, click Tools Add-ins.

Do you see a checkmark next to Analysis ToolPak? (Ignore Analysis ToolPak - VBA.) If not, checkmark ATP. Then select A5, and press A2, then Enter. You should see 2/28/1981 after you format the cell as Date. If you still see a #NAME error, you might need to reinstall the ATP from the source of Office 2003 or Excel 2003 - usually a CD. Frankly, I am not familiar with the procedure.

Excel does provide something to help you (supposedly): click Help Detect and Repair. Some people suggest using that to repair Excel when it is 'broken'. But I must forewarn you: D&R is not for the faint at heart, IMHO. When I did it, it did require the installation CD. And as a result, I lost much of my customized settings - which I set over the course of 6 years, with little recollection of what they are. Hopefully, you can reinstall the ATP from the CD without having to reinstall all of Office or Excel - that is, without relying on the D&R feature of Excel Help. Please report your findings in a response in this thread.

Kathleen wrote: Sorry for the confusion. There are too many inconsistencies for me to deal with. I'm afraid I will not be able to help you further. My suggestion: repost the problem, starting a new thread. Hopefully that will attract fresh blood. I'm not sure anyone else is paying attention to this thread anymore, since it seemed that we had resolved your original problem. Cannot see the forest for the trees.

Hint: When you start a new thread, I don't think it's necessary to rehash past history. Stick with the current facts, to wit: (a) you are using 'this' formula; (b) when you enter 'this' date into AR2, it seems to work fine; and (c) when you enter 'that' date in AR2, you get 'this' error. Copy-and-paste the contents of AR2, GD2 and any other relevant cells from the Formula Bar into your new posting. Also make note of whether the calculation mode is automatic or manual; and if the latter, what happens when you press ctrl+alt+F9. Some final comments.

Kathleen wrote: I didn't say 'another report' Of course you did. Copy-and-pasting from your posting, you wrote: 'its working great - using the date that came over on another report'. Perhaps you meant to write: ``I didn't meanto say 'another report'``. No problem if you merely misspoke again.

If I had a dollar for every time I inadvertently omitted words - especially important words like 'not' - I'd be richer than Gates, if not Buffett;-);-). Kathleen wrote: Cell AR2 held the date of birth 7/28/1966. Once your formula was entered into GD2, it accurately calculated the age to be 44. However, the correct date of birth in AR2 should be 7/29/1965.

I went back to AR2 and changed the date of birth from 7/28/1966 to 7/29/1965 - the result in GD2 should have been 45. Instead the GD2 result is the error message #NAME? What is 'my formula' doing in GD2? The one you posted is: =IF(AR2=',0,DATEDIF(AR2,GD2,'y') +IF(DAY(AR2)=29,EDATE(AR2,12.DATEDIF(AR2,GD2,'y')+12)=GD2,0)) That presumes that GD2 contains a date, namely the 'valuation date' - what used to be TODAY in your previous formula. If you truly put that formula into GD2, that would create a circular reference.

You should get a warning, unless you set the Iteration calculation option. (Bad idea!) But even when the Iteration option is set (bad idea!), the eventual cell error is #NUM, not #NAME, if AR2 in non-blank. (Or #VALUE if AR2 contains text, not a date number.) Kathleen wrote; And when you go to Tools Add-Ins, do you a checkmark next to Analysis ToolPak?

Yes - I'm not familiar with what this does and will learn more No need to. You should see a checkmark next to Analysis ToolPak.

That enables you to use EDATE. I was merely looking for confirmation. If there were no checkmark next to Analysis ToolPak, we would expect the use of EDATE to generate a #NAME error.

Only after you close and restart Excel with Analysis ToolPak uncheckmarked. And (drum roll!). Only when the birthdate in AR2 is on day 29. Kathleen wrote: Well, I went back again to play with the dates, entering new, changing back to old, etc. It seems that the problem is with '29' in the formula. I've been using the date 7/29/1965 and definitely get the error (tried 12/29, 1/29, etc all result in error). However, if I use the date 7/1/1965, it accurately shows 45.

The problem is not 'with 29 in the formula' per se. But I can believe that the problem is exposed when the birthdate is on day 29. The IF function is not a true function. It behaves more like a 'conditional operator'; that is, Excel evaluates its arguments only as needed. So if AR2 is blank, no usage error in the DATEDIF and EDATE expressions would be exposed because Excel evaluates only the first value-if-true part, namely 0. Similarly, if AR2 contains a birthdate that is not on day 29, no usage error in the EDATE expression would be exposed because Excel evaluates only the first DATEDIF expression and the value-if-false part of the second IF function, namely 0.

But when AR2 contains a birthdate that is on day 29, Excel would evaluate the EDATE expression and expose any usage error therein, as well as any usage error in the first DATEIF experssion. So yes, your problem seems to be with the EDATE expression. But when I copy-and-paste the formula that you posted into a cell other than GD2, I get valid results for all three conditions, namely: when AR2 is empty; when AR2 is the numeric date 7/28/1966; and when AR2 is the numeric date 7/29/1965. So it seems that something else is going on, something specific to your Excel file or your Excel environment (e.g.

Workbook event macros, personal.xls, etc). Something 'lost in translation' to your posting.

As I noted above, if EDATE is not enabled, you will get a #NAME error if a birthdate in AR2 is a date on day 29. (And only after you close and restart Excel.) EDATE is not enabled if there is no checkmark next to Analysis ToolPak in the menu when click Tools Add-ins. Note that I talking about 'Analysis ToolPak' per se, not 'Analysis TookPak - VBA'.

You can checkmark both, if you wish. But you must checkmark at least the non-VBA Analysis ToolPak. If any of that helps, good.

Otherwise, ignore it, and start a new thread to attract fresh blood.

Excel Tips: Calculating Age Calculating Age in Excel. Working Out a Person's Age in Excel Published: 19 July 2001 Revised: 29 April 2013 Author: Martin Green Screenshots: Excel 2010, Windows 7 For Excel Versions: All 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 open the Format Cells dialog by (in Excel 2007 and later) expanding the Number section of the Home tab (click the arrow in the lower right corner) or (in Excel 2003 and earlier) opening the Format menu and choosing 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 is a 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 (see NOTE below). It looks like this. In this example the formula in cell A3 is =A1-A2 NOTE: In older versions of Excel you might get another date.

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 (in Excel 2003 and older) going to Format Cells General or (in Excel 2007 and later) choosing 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 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 do any multiplying and dividing before doing any 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) 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 A3 is =INT((TODAY-A2)/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. Also, because the way this formula calculates, if a person was born in a leap year and today is their birthday it doesn't get their age right until tomorrow.

When I updated this tutorial I ran a check again and came up with 29 such errors out of a total of 41,393 possible dates since 1 January 1900. But if you are going to do it, you might as well do it right! So how can we get an exact, guaranteed correct figure? 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. The person was born on 23rd August 1975.

How old are they? 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 they have had their birthday. If they 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. We do it all the time without thinking about it. But explaining the rules to Excel is a bit more complicated. =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.

Date Of Birth Calculator From Date Of Death

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.

But What About the DATEDIF Function? Those in the know will tell you that you don't need all this messing about with IFs because Microsoft has provided the DATEFIF function. That's perfectly true but note that I said 'those in the know'. The DATEDIF function will accurately determine the number of specified time intervals between two given dates. There is an excellent explanation of it on Chip Pearson's web site here: But, whilst it is works in Excel, Microsoft has seen fit not to support it in any version since Excel 2000. 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 saying, although it seems to work perfectly for me, but I prefer to play safe. And anyway, if you've read this far, you've at least learned something about nested 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 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 function 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 function. 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 functions 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 function 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 from version 2007 on Excel can handle up to forty-seven (count them!) nested IF functions in a single calculation (older versions could only manage a mere seven!). ©2013 Martin Green.