Leave tracker with different leave year start dates for different employees

Hi,

I'm currently working on an attendance tracker/leave tracker for my manager, and I've put one together (at least the attendance part of it). Where I'm stuck though is on adding a leave tracker to it. I know there are loads of leave tracker templates available online, but I can't find anything that fits my needs.

I can count the number of days annual leave an employee takes, which I can pick up from the attendance tracker worksheet, and pick up the leave allocation for each employee from a configuration page I've built into it, but I have 3 problems which are complicating matters:

• - as we're on flexi-time our work year isn't divided into months, it's divided into 13 'flexi-periods' (each of 4 weeks) for each year, which rarely fall in line with the months the rest of the world uses! The attendance tracker uses these 'flexi-periods', in line with our timesheets, but annual leave years work from the beginning or end of months
• - in addition to an annual leave entitlement, staff can also take up to 2 'flexi-days' off per flexi-period (pro-rata'd for part-time employees) provided they've accrued enough hours to still be within the permitted range of hours, positive or negative, against their contracted hours (again, pro-rata'd for part-time employees). Ideally, as well as annual leave, I'd like to incorporate the number of flexi-days an employee's taken, though it would only be a case of counting how many they've taken as timesheet details are stored on a corporate system which I can't access employees' timesheet data from, so I wouldn't be able to check their eligibility to take flexi-leave, so that bit is probably a non-starter
• The largest problem though is that each employee can have a different start date for their 'leave year' (as apparently it makes life easier for the admin support staff as they don't have to set-up and send out 1000+ leave cards all in the same
• month), so now your leave year starts at the beginning of the month in which you started working for the company! At any point in the flexi-year, which runs usually February - February, an employee could have just started their new 'leave year', be just ending their 'leave year', or be at any point in the middle of it. We're currently in flexi-period 8, and realistically by the time I've finished putting the spreadsheet together and properly tested it, it's going to be flexi-period 9 before it's ready for use, so I have to account for an employee's current leave balance, possibly any leave carried over from their previous 'leave year', and also having to probably add in a new 'leave year's' worth of leave at any point in the year. The idea of the leave tracker really is so that the manager can make sure staff are using leave throughout the year and not just taking large blocks of leave at the beginning or end of the leave year, and to warn the manager if an employee's still got a large proportion of their leave entitlement left as it gets closer to the end of their leave year.

I can count the number of leave days taken per flexi-period, and the number of flexi-days taken per flexi-period, but what I'm struggling to figure out is how to deal with calculating and showing how much leave an employee's taken against their entitlement, especially with having to add a new year's entitlement in at some point in the year. It would be easier if I hard-coded it, but I'm trying to keep it as dynamic as possible, with all the required set-up information entered on a single configuration page, which populates the other sheets and tables as needed.

At this point I'd really just appreciate any suggestions as to how you'd tackle this. I've attached an anonymized copy of
the spreadsheet as it is currently, obviously without any leave-tracking component. I'm either missing something blindingly obvious, or I'm missing some sort of gigantic stumbling block, but I just can't get my head round the problem of the mis-match between months and flexi-periods and adding in a new leave entitlement potentially in the middle of the year. Any suggestions as to how you'd tackle it (or not!) appreciated.

Many thanks for your response, and my apologies for not responding for so long (personal issues have dragged me away from Excel). I think I've seen an example of that, where it was formatted Date | Employee | Event. I'll have a look at that more detail.

My manager's happy with just the attendance tracker part of it at the moment, so I have the leave calculator part of it on hold for now. I'm hoping to get that set up for the new flexi year, which starts in February, so I'll start looking at this again in earnest in the new year.