Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
SUMIF Function Inside SUMPRODUCT Function | Excel Worksheet Functions | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions | |||
Nested IF Function, Date Comparing, and NetworkDays Function | Excel Worksheet Functions |