intheskywithdiamonds
New Member
- Joined
- Feb 25, 2013
- Messages
- 10
Hello!
I have visited perhaps every single excel forum post in the entire interwebs that is vaguely related to my excel problem and I cannot find an answer to my query. So, I thought I would just bite the bullet and ask for directions!
I'll try and summarise my project first of all to give you an idea of what i'm working with.
I'm creating a calculator that will determine what date an employee will go onto half-pay due to sickness.
Based on the rule that a full-time employee is entitled to 130 working days absent a year before their pay is reduced to half, this is pro-rated for part-time employees i.e if you work 4 days a week you are entitled to 104 working days absent before you go onto half-pay.
The calculator begins with an option to select how many days you work a week, and based on that input, it determines the amount of absent days you are entitled to.
Then you select the actual days you work; the days of the week are listed and there is a 'YES/NO' drop down selection.
Then the employee inputs their 'spells of absence' which have the start date and end date of an absence. I used this formula to determine the number of working days (based on their inputted work pattern) that they were absent;
=IF(E21="","",SUM(IF(ISERROR(MATCH(WEEKDAY(ROW(INDIRECT(E21&":"&G21))),ExcludeDaysOfWeek,0)),1,0)))
Where E21 is the start date of the absence and G21 is the end date.
'ExcludeDaysOfWeek' is a range which lists the days the employee doesn't work, based on their 'YES/NO' selection.
So to summarise, it counts working days, excluding week days that the employee doesn't work.
Sorry for rambling, but I think background information is necessary for things like this!
Now on to my problem.
Once i've worked out how many days they have been absent based on their working pattern, I need to compare that to how many days they are entitled to, which we worked out at the start! i.e 130 days if you work 5 days a week, 104 if you work 4 days etc. So if an employee works 5 days a week, and they have been absent 125 days, the calculator would work out their 'half-pay date' as 5 working days from their 125th day of absence, to bring them up to 130 days. I'm not sure if this makes sense outside of my own brain.
Basically the bit i'm stuck on is, if an employee works 3 days, monday, wednesday, thursday, meaning they are entitled to 78 days absent, and they've been absent 58 days, HOW can i get excel to count forward those 20 days using only the days the employee works i.e monday, wednesday, thursday! I have tried using my ExcludeDaysOfWeek range from earlier, but it just won't work!
Basically I just need a function that will let me (end_date of latest absence, days left at full pay, (excluding days they don't work)
If anyone can assist me I will give you all the internets.
Long time lurker, please don't let me down Lords of the world wide web
I have visited perhaps every single excel forum post in the entire interwebs that is vaguely related to my excel problem and I cannot find an answer to my query. So, I thought I would just bite the bullet and ask for directions!
I'll try and summarise my project first of all to give you an idea of what i'm working with.
I'm creating a calculator that will determine what date an employee will go onto half-pay due to sickness.
Based on the rule that a full-time employee is entitled to 130 working days absent a year before their pay is reduced to half, this is pro-rated for part-time employees i.e if you work 4 days a week you are entitled to 104 working days absent before you go onto half-pay.
The calculator begins with an option to select how many days you work a week, and based on that input, it determines the amount of absent days you are entitled to.
Then you select the actual days you work; the days of the week are listed and there is a 'YES/NO' drop down selection.
Then the employee inputs their 'spells of absence' which have the start date and end date of an absence. I used this formula to determine the number of working days (based on their inputted work pattern) that they were absent;
=IF(E21="","",SUM(IF(ISERROR(MATCH(WEEKDAY(ROW(INDIRECT(E21&":"&G21))),ExcludeDaysOfWeek,0)),1,0)))
Where E21 is the start date of the absence and G21 is the end date.
'ExcludeDaysOfWeek' is a range which lists the days the employee doesn't work, based on their 'YES/NO' selection.
So to summarise, it counts working days, excluding week days that the employee doesn't work.
Sorry for rambling, but I think background information is necessary for things like this!
Now on to my problem.
Once i've worked out how many days they have been absent based on their working pattern, I need to compare that to how many days they are entitled to, which we worked out at the start! i.e 130 days if you work 5 days a week, 104 if you work 4 days etc. So if an employee works 5 days a week, and they have been absent 125 days, the calculator would work out their 'half-pay date' as 5 working days from their 125th day of absence, to bring them up to 130 days. I'm not sure if this makes sense outside of my own brain.
Basically the bit i'm stuck on is, if an employee works 3 days, monday, wednesday, thursday, meaning they are entitled to 78 days absent, and they've been absent 58 days, HOW can i get excel to count forward those 20 days using only the days the employee works i.e monday, wednesday, thursday! I have tried using my ExcludeDaysOfWeek range from earlier, but it just won't work!
Basically I just need a function that will let me (end_date of latest absence, days left at full pay, (excluding days they don't work)
If anyone can assist me I will give you all the internets.
Long time lurker, please don't let me down Lords of the world wide web
