Age between a start date and now()

[dannyok90]

Hi All,

How could i work out how many years between a job start date and now() a person was:


- under the age of 22
- over 22 but under 41
- over 41


I've worked out the persons age from their DOB but my brain is frazzled trying to work the rest out!!


Any help appreciated as always


Dan

 

[Joe4]

I am not sure I undersrtand what you are after.
Why would a person's current age have any bearing on the time elapsed between Job Start Date and Now?

 

[dannyok90]

I am not sure I undersrtand what you are after.
Why would a person's current age have any bearing on the time elapsed between Job Start Date and Now?

Hi Joe
So, in the UK (not sure if you're from the UK) if you are made redundant (not needed any more) from work there are pay bands.

- for every year you worked for the business that you were under the age of 22 you get half your weeks pay
- for every year you worked for the business between the ages of 22 and 41 you get a full weeks pay
- for every year you worked for the business that you were over the age of 41 you get a week and a halfs pay

so i need to figure out how many years a person sat in these bands from the date they started employment to the date they were made redundant
somebody could be 65 and have worked at the business since being 16 and hence giving him:



- 6 years under 22
- 19 years between 22 and 41
- 24 years over 41


its trying to work out the formula to band that, if you follow?
Thanks,
Dan

 

[Joe4]

OK. So what cells are all these different dates stored in?
And can you post the age formula that you are using now?

 

[dannyok90]

OK. So what cells are all these different dates stored in?
And can you post the age formula that you are using now?

So the employment start date would be in a1
The employment end date in a2
The persons date of birth in a3
And the formula for working out the persons age from there date of birth is in a4

A4 is =DATEDIF(A3,NOW(),"y")

 

[Joe4]

I do not know how you account for partial years, so these formulas may need a little tweaking, but these should give you a good spot to start from.
I am calculating the 22-41 and over 41 ranges, then subtracting these values from total service to get the under 22 years.

So here are my formulas:
- Under 22 in cell A5: =DATEDIF(A1,A2,"y")-A6-A7
- Between 22 and 41 in cell A6: =MAX(MIN(41,DATEDIF(A3,A2,"y"))-MAX(22,DATEDIF(A3,A1,"y")),0)
- Over 41 in cell A7: =MAX(DATEDIF(A3,A2,"y")-41,0)

 

[dannyok90]

I do not know how you account for partial years, so these formulas may need a little tweaking, but these should give you a good spot to start from.
I am calculating the 22-41 and over 41 ranges, then subtracting these values from total service to get the under 22 years.

So here are my formulas:
- Under 22 in cell A5: =DATEDIF(A1,A2,"y")-A6-A7
- Between 22 and 41 in cell A6: =MAX(MIN(41,DATEDIF(A3,A2,"y"))-MAX(22,DATEDIF(A3,A1,"y")),0)
- Over 41 in cell A7: =MAX(DATEDIF(A3,A2,"y")-41,0)

I don't need to account for partial years. just whole years for the purpose of this exercise.

this is absolutely fantastic. thanks for your help!

 

[Joe4]

You are welcome.