- Joined
- Dec 13, 2010
- Messages
- 1
- Reaction score
- 0
Hello
I'm a complete newbie here! I've managed (to my amazement) to work out how to get Excel to do basic calculations but I'm stuck on this one.
I have several part-time staff working different hours on different days of the week. Because of this, annual leave is calculated by hours instead of days. I want to create a simple spreadsheet which will calculate the holidays and remaining holiday for each staff member.
I want to say something like "If the date in this cell is a Monday then this cell's value should be 7" for example.
If I need a different sheet for each member, that's fine. I just can't seem to work out the right formula.
I envisage:
Column A would contain the date on which leave is taken
Column B would calculate (from the date) how many hours the employee normally works on that day and, therefore, how many hours leave.
Column C would be used for irregular numbers of hours leave taken on a particular day (for example: taking 2 hours off from a 7 hour day)
Column D would be for extra holiday hours earned by doing additional shifts - this would be an irregular amount
Column E would have a running total of how many hours leave have already been taken
Column F would tell us how many hours leave remain outstanding for that employee.
I will give you a scenario:
Janet works for 7.5 hours on Mondays, 7 hours on Thursdays and 4.5 hours on Fridays. Including extra holiday allowance for length of service, she is entitled to 83.5 hours annual leave per year. She has worked 2 extra 7.5 hour shifts which she wants to "bank" for annual leave instead of getting paid.
Any help gratefully received!
Best wishes
Suzie
I'm a complete newbie here! I've managed (to my amazement) to work out how to get Excel to do basic calculations but I'm stuck on this one.
I have several part-time staff working different hours on different days of the week. Because of this, annual leave is calculated by hours instead of days. I want to create a simple spreadsheet which will calculate the holidays and remaining holiday for each staff member.
I want to say something like "If the date in this cell is a Monday then this cell's value should be 7" for example.
If I need a different sheet for each member, that's fine. I just can't seem to work out the right formula.
I envisage:
Column A would contain the date on which leave is taken
Column B would calculate (from the date) how many hours the employee normally works on that day and, therefore, how many hours leave.
Column C would be used for irregular numbers of hours leave taken on a particular day (for example: taking 2 hours off from a 7 hour day)
Column D would be for extra holiday hours earned by doing additional shifts - this would be an irregular amount
Column E would have a running total of how many hours leave have already been taken
Column F would tell us how many hours leave remain outstanding for that employee.
I will give you a scenario:
Janet works for 7.5 hours on Mondays, 7 hours on Thursdays and 4.5 hours on Fridays. Including extra holiday allowance for length of service, she is entitled to 83.5 hours annual leave per year. She has worked 2 extra 7.5 hour shifts which she wants to "bank" for annual leave instead of getting paid.
Any help gratefully received!
Best wishes
Suzie