Age Brackets

[Best88771]

I want to calculate a persons period of employment within specific "Age Brackets" with 3 dates available
I do not want to enter the dates in cells - but to enter formulas in each cells to calculate period within each "Age Bracket" example below
period (a) from start date to 1st birthday with company (note employee birthday 30th July)
period (b) age bracket 22-40 (follows on from period A - to employee 40th birthday)
period (c) age bracket 41-50 (follows on from period B - to employee 50th birthday)
period (d) age bracket 51+ (follows on from period C - to employee birthday prior to leaving)
period (e) age bracket (follows on from period D - to leave date)
I hope this makes sense

1) DOB 30/07/1955
2) START 01/07/1992
3) LEAVE 12/07/2013

a) START 1ST BIRTHDAY
01/07/1994 30/07/1994

b) AGE 22-40
31/07/1994 30/07/1999

c) AGE 41-50
31/07/1999 30/07/2009

d) AGE 51+
31/07/2009 30/07/2012

e) LAST BIRTDAY LEAVE DATE
31/07/2012 12/07/2013

[Jacc]

Post a workbook with some sample data and pointers of what you want. Makes it much easier to get started with a solution.

[Best88771]

Thank you for your reply - please see data below and template

I require excel formulas in cells
A12, A17, A23, A28
C6, C12, C17, C23, C28

I can do formulas in D6, D12, D17, D23, D28


EXAMPLE...

A1 29/07/1958 DATE OF BIRTH
A2 01/07/1994 STAR TDATE
A3 02/07/2013 LEAVE DATE

START DATE A6 = A2
1ST BIRTHDAY C6 (DATE)

FORMULA required for C6
this means employee first birthday since date of starting employment on 1st July 1994
C6 = as employee birthday 28th July - so year started 1994 - so calculation date will be 28/07/1994
if depending on start date - this may be following year
D6 = date diff between A6 and C6 to be "days "



1) age bracket 22 - 40
A12 (date) employee age 22 (plus 1 day)
C12 (date) employee age 40 years

FORMULA required for A12 and C12
A12 will be 1 day after Cell C6 - example C6 = 28/07/1994 -- so A12 = (C6 + 1 DAY) = 29/07/1994
C12 will be employee age at 40 years - example C12 = (28/07/1958 + 40 years) so C12 date will be 28/07/1998
D12 = date diff between A12 and 12 to be "years "


2) age bracket 41 - 50
A17 (date) employee age 41 (plus 1 day)
C17 (date) employee age 50 years

FORMULA required for A17 and C17
A17 will be 1 day after Cell 12 - example C12 = 28/07/1998 therefore A17 = (28/07/1998 + 1 day) = 29/07/1998
C17 will be employee age at 50 years -- example C17 = (28/07/1958 + 50 years) so C17 date will be 28/07/2008
D17 = date diff between A17 and C17 to be "years "


3) age bracket 51+
A23 (date)employee age 50 (plus 1 day)
C23 (date) employee last birthday in year prior to leave date

FORMULA required for A23 and C23
A23 will be 1 day after C17 - example C17 = 28/07/2008 -- therefore A23= (28/07/2008 + 1 day) = 29/07/2008
C23 - if employee leaves on 02/07/2013 - then last birthday will be 29/07/2012 therefore C23 = 28/07/2012
D23 = date diff between A23 and C23 to be "years "


4) Bracket
A28 (date) last birthday (plus 1 day)
C28 = A3 leave date

FORMULA required for A26
A28 will be 1 day after C23 - example C23 = 28/07/2012 -- therefore A28= (28/07/2012 + 1 day) = 29/07/2012
C26 - will be leave date = A3
D27 = date diff between A28 and C28 to be "days "


TEMPLATE RESULT

a) START - 1ST BIRTHDAY
01/07/1994 -- 28/07/1994 27 days

b) AGE 22-40
29/07/1994 -- 28/07/1998 4 years

c) AGE 41-50
29/07/1998 -- 29/07/2008 10 years

d) AGE 51+
29/07/2008 -- 29/07/2012 4 years

e) LAST BIRTDAY -- LEAVE DATE
29/07/2012 -- 02/07/2013 339 days


other Forum URL Link
http://www.mrexcel.com/forum/excel-q...-brackets.html

[HaHoBe]

Hi, Best88771,

I'm afraid your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

Ciao,
Holger

[Best88771]

Thank you for pointing out rule 8 - apologies as new member of these forums and not reading rules in full. I have now posted URL Link and edited my last post - I hope this helps...

[Best88771]

Hello - I have since managed to work out several formulas asked in my first post - therefore I now only require two formulas

Example Data
EXAMPLE...

A1 29/07/1958 DATE OF BIRTH
A2 01/07/1994 STAR TDATE
A3 02/07/2013 LEAVE DATE

1st Formula
START DATE A6 = A2
1ST BIRTHDAY C6 (DATE)

FORMULA required for C6
this means employee first birthday since date of starting employment on 1st July 1994
C6 = as employee birthday 28th July - so year started 1994 - so calculation date will be 28/07/1994
if depending on start date - this may be following year


2nd Formula
age bracket 51+
A23 (date)employee age 50 (plus 1 day)
C23 (date) employee last birthday in year prior to leave date

FORMULA required for C23
{ A23 will be 1 day after C17 - example C17 = 28/07/2008 -- therefore A23= (28/07/2008 + 1 day) = 29/07/2008 **I have A23 formula now }
C23 - if employee leaves on 02/07/2013 - then last birthday will be 29/07/2012 therefore C23 = 28/07/2012


***So to confirm only two formulas required..
1) C6 formula
2) C23 formula

I hope this helps....


TEMPLATE RESULT

a) START - 1ST BIRTHDAY
01/07/1994 -- 28/07/1994 27 days

b) AGE 22-40
29/07/1994 -- 28/07/1998 4 years

c) AGE 41-50
29/07/1998 -- 29/07/2008 10 years

d) AGE 51+
29/07/2008 -- 29/07/2012 4 years

e) LAST BIRTDAY -- LEAVE DATE
29/07/2012 -- 02/07/2013 339 days


Many thanks for your time ..look forward to resolution.

kind regards

[Best88771]

Dear Friends - I hope this as I only need two formulas (please see below)

29/07/1958 Date of Birth = A1
01/07/1994 Start Date = A2
02/07/2013 Leave Date = A3

1) Start Date 1st Birthday START 1ST BIRTHDAY
01/07/1994 29/07/1994 A2 C6 (formula) C6 formula required


2) AGE 22 AGE 40 AGE 22 AGE 40
30/07/1994 29/07/1998 C6+1 EDATE(A1,40*12)


3) AGE 41 AGE 50 AGE 41 AGE 50
30/07/1998 29/07/2008 C9+1 EDATE(A1,50*12)


4) AGE 51 + last birthday AGE 51 Last birthday
plus 1 day plus 1 day
30/07/2008 29/07/2012 C12+1 C23 (formula) C23 formula required


5) Last Birthday Leave Date Last Birthday LEAVE DATE
plus 1 day plus 1 day
30/07/2012 02/07/2013 C23+1 A3


FORMULA required for C6
this means employee first birthday since date of starting employment on 1st July 1994
C6 = as employee birthday 28th July - so year started 1994 - so calculation date will be 28/07/1994
if depending on start date - this may be following year



FORMULA required for C23
{ A23 will be 1 day after C17 - example C17 = 28/07/2008 -- therefore A23= (28/07/2008 + 1 day) = 29/07/2008 **I have A23 formula now }
C23 - if employee leaves on 02/07/2013 - then last birthday will be 29/07/2012 therefore C23 = 28/07/2012

[ChemistB]

To post a spreadsheet, EDIT post and "Go Advanced" > Manage Attachments.

[Best88771]

age brackets--2013.xlsx

Apologies for the confusion --- I hope this helps...

Please see excel spread sheet within post - two formulas required as follows
Please let me know if anyone requires any further information..

FORMULA required for C6
this means employee first birthday since date of starting employment on 1st July 1994
C6 = as employee birthday 28th July - so year started 1994 - so calculation date will be 28/07/1994
if depending on start date - this may be following year

FORMULA required for C15
C15 - if employee leaves on 02/07/2013 - then last birthday will be 29/07/2012 therefore C15 = 28/07/2012

[ChemistB]

In C6
=IF(A2<=DATE(YEAR(A2),MONTH(A1),DAY(A1)), DATE(YEAR(A2), MONTH(A1), DAY(A1)),DATE(YEAR(A2)+1, MONTH(A1), DAY(A1)))

In C15
=IF(A3< DATE(YEAR(A3),MONTH(A1),DAY(A1)),DATE(YEAR(A3)-1, MONTH(A1), DAY(A1)), DATE(YEAR(A3),MONTH(A1), DAY(A1)))
Does that work for you?

[Best88771]

Absolutely brilliant - many thanks for your time ...

[Best88771]

I have now marked this as "Solved" ...thanks.....