if function

[Guest]

this may be a similar post to one entered recently but i desperately need
some help with a formula. i have 2 variables each with 3 options. For each of
the 9 possible combinations i need a different answer. this is to do with
employee leave and is dependent on their paypoint and length of service.
less than 5 YRS hit 5 years btwn 5+ YEARS@01/04/08
@01/04/07 01/04/07&01/04/08

4 - 21 20 0 25
22 - 28 21 0 25
29 - 40 23 0 26

i have the list of paypoints stored in E column and list of date they hit 5
yrs service in column J.
if anyone could provide me with a formula - i have tried and failed with IF
& AND functions - or let me know if i can do it a different way i would be
really grateful!!
thanks in advance!

 

[Guest]

I don't understand what you want?
what do you want the formula to say and where?

 

[Guest]

sorry.i want the formula to say how many days an employees leave allowance is
based on their pay point and number of years service. e.g. if employee is on
paypoint 24 and have been here for more than 5 years they would have 25 days
leave.

 

[Sandy Mann]

sorry.i want the formula to say how many days an employees leave allowance
is
based on their pay point and number of years service. e.g. if employee is
on
paypoint 24 and have been here for more than 5 years they would have 25
days
leave.

I understand that part but what is a pay point and what does:have to do with it?


--
Regards,


Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Guest]

i have tried to put a table in to show what i mean which is perhaps is not
the best option!
if they are on paypoints 4-21 and they have less than 5 yrs service at
01/04/08 they get 20 days leave and if they have more than 5 yrs service at
01/04/07 they get 25 days leave.
if they are on paypoints 22-28 and they have less than 5 yrs service at
01/04/08they get 21 days leave and if they have more than 5 yrs service at
01/04/07 they get 25 days leave.
if they are on paypoints 29 and over and they have less than 5 yrs service
at 01/04/08 they get 23 days leave and if they have more than 5 yrs service
at 01/04/07 they get 26 days leave.
If they hit 5 years between 01/04/07 and 01/04/08 i want them to show as
zero as we need to manually calculate this.
i have got a list of every paypoints and the day on which they hit 5 years
service and was hoping to use this to create a formula to automatically eter
on the spreadsheet what their leave entitlement is.
i attempted using the IF function and i could manage part of it but once i
got past the first few ifs i got very confused with the brackets etc! i hope
this makes things clearer

 

[Sandy Mann]

There may be more elegant ways of doing it but with the person's starting
date in A1 and their pay points in B1:

=IF(AND(TODAY()>=DATE(YEAR(TODAY()),4,1),DATEDIF(A1,DATE(YEAR(TODAY()),4,1),"y")<5,DATEDIF(A1,DATE(YEAR(TODAY())+1,4,1),"y")=5),0,IF(DATEDIF(A1,DATE(YEAR(TODAY()),4,1),"y")<5,23-(B1<29)*2-(B1<22),25+(B1>28)))*(DATEDIF(A1,DATE(YEAR(TODAY()),4,1),"y")>0)

seems to return what you want.


To simplify the formula you can put parts of it in various cells:

C1: =DATEDIF(A1,DATE(YEAR(TODAY()),4,1),"y")
C2: =DATE(YEAR(TODAY()),4,1)
C3: =DATEDIF(A1,DATE(YEAR(TODAY())+1,4,1),"y")

Labelled as "Years Service", "Leave year start" & "Years Service Next Year"
respectively to remind you what they are if you wish.

the formula will then be:

=IF(AND(TODAY()>=C2,C1<5,C3=5),0,IF(C1<5,23-(B1<29)*2-(B1<22),25+(B1>28)))*(C1>0)

Alter the formula(s) to you own ranges.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk