Age depending on date

[stuckwithdates]

<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--> Hi,


I have two cells [finish date] and [date of birth] and am trying to work out the formula to find the age at end of the next financial year which is June 30 depending on the finish date.

Is this possible?


Thanks,
KJ

 

[gnrboyd]

If I understood your needs correctly, this should work for you. I assumed that if the completion date was 6/30 that you would use the same date as the end of the financial year rather than waiting until the following year. If that is not correct, the formula can easily be modified.

I wasn't sure how exact you needed the age to be but leap year seems to be the cause of a very slight fractional issue. I used the Excel datefrac formula which calculates the number of fractional years between 2 dates. I would think this would adjust exactly for how many leap years are included but it returns values such as 35.000624 when it should be 35.0. I can only assume that leap year is the culprit as to why this formual does this. I added the round formula out to 2 digits to account for this but I strongly doubt that this trivial issue is even a problem but I thought I would mention it and why the round formula was added.

Hope this helps.

Book1
	A	B	C	D
1	FinishDate	DateofBirth	AgeatEndofNextFinancialYear
2	04/06/2011	09/18/1984	26.78
3	09/11/2011	03/14/1967	45.30
4	06/30/2011	06/30/1979	32.00
5	07/01/2011	06/30/1979	33.00
6	06/30/2011	06/30/2010	1.00
7	06/30/2012	06/30/2010	2.00
8	06/30/2013	06/30/2010	3.00
9	06/30/2014	06/30/2010	4.00
Sheet1

 

[stuckwithdates]

Thanks gnrboyd,

This is what I was looking for but I do need the financial year to be forward rather than in the same year. For example the finish date is October 30, 2010 the persons DOB is November 30, 1955 so they would be 55 at the next financial year June 30, 2011.

Is it possible for you to help with the modification?

Many thanks,
KJ

 

[gnrboyd]

This is what I was looking for but I do need the financial year to be forward rather than in the same year. For example the finish date is October 30, 2010 the persons DOB is November 30, 1955 so they would be 55 at the next financial year June 30, 2011.

First of all, I'm sorry for not replying earlier. You didn't respond for a couple of days after my initial response and then I was out of town for a week or so. Anyway, I believe the example does give you the answer you are looking for. When I plug in the dates in your example above, the result is 55.58. I can add a rounding formula if you'd like that will round the age down to even numbers. Is that what you are looking for? If so, try this in cell C2:

=ROUNDDOWN(YEARFRAC(IF(AND(MONTH(A2)<=6,DAY(A2)<=30),VALUE("06/30/"&YEAR(A2)),VALUE("06/30/"&YEAR(A2)+1)),B2,1),0)