#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3
Default if function

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!


  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 791
Default if function

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

"jennie999" wrote:

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!


  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3
Default if function

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.

"Michael" wrote:

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

"jennie999" wrote:

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!


  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,345
Default if function

"jennie999" wrote in message
...
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:
4 - 21 20 0 25
22 - 28 21 0 25
29 - 40 23 0 26

have to do with it?


--
Regards,


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


with @tiscali.co.uk


"jennie999" wrote in message
...
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.

"Michael" wrote:

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

"jennie999" wrote:

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!




  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3
Default if function

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" wrote:

"jennie999" wrote in message
...
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:
4 - 21 20 0 25
22 - 28 21 0 25
29 - 40 23 0 26

have to do with it?


--
Regards,


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


with @tiscali.co.uk


"jennie999" wrote in message
...
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.

"Michael" wrote:

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

"jennie999" wrote:

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!







  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,345
Default if function

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(A 1,DATE(YEAR(TODAY()),4,1),"y")<5,DATEDIF(A1,DATE(Y EAR(TODAY())+1,4,1),"y")=5),0,IF(DATEDIF(A1,DATE(Y EAR(TODAY()),4,1),"y")<5,23-(B1<29)*2-(B1<22),25+(B128)))*(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+(B128)))*(C10)

Alter the formula(s) to you own ranges.

--
HTH

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


with @tiscali.co.uk


"jennie999" wrote in message
...
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" wrote:

"jennie999" wrote in message
...
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:
4 - 21 20 0 25
22 - 28 21 0 25
29 - 40 23 0 26

have to do with it?


--
Regards,


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


with @tiscali.co.uk


"jennie999" wrote in message
...
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.

"Michael" wrote:

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

"jennie999" wrote:

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!







Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) [email protected] Excel Worksheet Functions 0 September 5th 06 04:44 PM
Offset function with nested match function not finding host ss. MKunert Excel Worksheet Functions 1 March 21st 06 11:46 PM
SUMIF Function Inside SUMPRODUCT Function Abdul Waheed Excel Worksheet Functions 17 September 19th 05 05:24 PM
Emulate Index/Match combo function w/ VBA custom function Spencer Hutton Excel Worksheet Functions 2 May 2nd 05 06:26 PM
Nested IF Function, Date Comparing, and NetworkDays Function carl Excel Worksheet Functions 2 December 29th 04 10:57 PM


All times are GMT +1. The time now is 12:40 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"