Age Brackets

[BEST88771]

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


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


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

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

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

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


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

 

[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

 

[nuked]

Forgive me, but I'm a little lost.

 

[BEST88771]

Apologies for confusion with my post...how can I import excel spreadsheet in order to show people what I require??

 

[BEST88771]

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 29/07/1958 Date of Birth = A1 01/07/1994 Start Date = A2 02/07/2013 Leave Date = A3 RESULT RESULT START 1ST BIRTHDAY 1) Start Date 1st Birthday (CELL A6) =A2 CELL C6 (formula required ) 01/07/1994 29/07/1994 AGE 22 AGE 40 2) AGE 22 AGE 40 (CELL A9) =C6+1 (CELL C9) =EDATE(A1,40*12) 30/07/1994 29/07/1998 AGE 41 AGE 50 3) AGE 41 AGE 50 (CELL A12) =C9+1 (CELL C12) =EDATE(A1,50*12) 30/07/1998 29/07/2008 AGE 51 + Last birthday 4) AGE 51 + Last Birthday (CELL A15) = C12+1 (CELL C15) (formula required) 30/07/2008 29/07/2012 Last Birthday + 1 LEAVE DATE 5) Last Birthday + 1 day Leave Date (CELL A28) =C15+1 (CELL C28) =A3 30/07/2012 02/07/2013 <tbody> </tbody> <tbody> </tbody>

<tbody>
</tbody>

 

[BEST88771]

This has now been "Solved"
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?