in addition to PeterOz request
PeterOz may have a better solution, so its worth also answering PeterOz and seeing what answer you get
I maybe overcomplicating this or not fully understanding and PeterOz may know the solution
As its a decimal and not hours and mins , then it will be just numbers and NOT Hrs Mins as i first thought
so negative should be fine.
i'm assuming you will manually enter the starting Base at the beginning of the year for each person , as they maybe all different
say 20D 0H
if there's a row that reports 19D in the hours cell and 6.50H in the hours cell and someone used 7.75 hours, I could then enter -1.25 into the hours cell.
How does someone show they have used 7.75 hours in the spreadsheet
perhaps some manually entered examples would help here with a note explaining how arrived
We have a very flexible vacation policy and employees who work more than 40 hours can either take overtime pay or comp their overage for more vacation time. Additionally, vacation hours roll over from the prior year, so the 20-day starting point is just a sample for reference. Everyone will have different "base" starting points from year to year. And because of the comping, vacation hours can be both subtracted and added.
These are just theoretical questions , and in the real spreadsheet may not happen - but just to get an understanding of what you require
1) Just work a Normal 40H week all week - what happens
if someone just works 40H a week all year - then I assume its just the base time - or as PeterOz mentiones do they earn holiday , as when someone first starts a job, some companies do not give the full 20D or prorater it depending on start date - BUT give so many days/hours worked each month
2) working more than 40H
If someone works an extra 5 hours every week, then they will get an additional 5 hours to their holiday
Each week it May/would go up by 5 assuming no vacation taken and 45 hours worked every week.
by the end of the year (in theory, i know would not happen ) , assuming every week was 45 hours , they would have worked 52 * and extra 5 = 260H - a DAY is classed as 8 hours
260 Hours = 260/8 32.5 (the .5 is half a day - so 4 hours ) 32D and 4H
how do you enter into the spreadsheet when they have worked the 45 hours and that they want some or all of that comped - added to the vacation or maybe a proportion of it - say they worked 45H , and want 2H holiday and 3H paid - how is that shown
3) working less than 40H
Now every week they only work 35 hours ....... what happens ????? and again how shown that they want it comped and not paid
4) Planned holiday
they take a holiday of 2 weeks - how is the 10 days entered into the spreadsheet to take the 10days - off the vacation time.
Similar if they take half a day 4H - how is that entered
As i say , i maybe overcomplicating and miss-understanding - so sorry about that