Forum Discussion
Excel Annual Leave calculation
Good Morning,
Apologies for the hubris! I shall use Google Drive, but you shall have to bear with me as I need to recreate the spreadsheet with no names 🙂
EDIT: link removed as newer one available
I hope you're planning on returning to flesh this out some more. You'll also need to give some assistance in navigating this maze, connecting it to your original questions.
Good Morning mathetes
I'm only able to get back to you whilst at work hence the sporadic replies, but yes I do intend to!
So each Ward has a relevant capture column for staffing levels which is found in the 'Daily Totals' sheet.
Each Ward also has it's own "dashboard", which is the purple boxes across the top, where all primary data can be entered to populate the rota below.
Some of the staff have established A/L Entitlements (see the relevant section in all Ward sheets) and for the most part, this is decided by their role; ie: all 'F1' have 9 days A/L per rota period.
As somewhat explained in my original message the way that I am hoping the formula will work is by:
[per Ward] (cells for the formula to be in 'Daily Totals' sheet around row 132
- Summing all the A/L Taken
- Subtracting any rows value, if the corresponding cell determining the staff members role meets a pre-defined criteria (TBD by range within the 'Data Admin' sheet.
- And/Or subtracting any rows value if there is no entry for A/L Entitlement
The aim is to achieve a 'True' value of how much leave has been taken by Ward, that should be monitored, as some staff are responsible for tracking their own A/L which is why I would like the formula to only capture certain roles in it's output.
As I write this, I can see this needing two separate formulas, but I can work with whatever solutions you suggest!EDIT: If you agreeable, please email me for quicker and potentially(?) easier communication (I will put it in 'Daily Totals'!A1)
Kind Regards,
OliYou really need to realize that just understanding what each of the individual sheets is about is a task in itself.
It's clear to you, but really isn't to somebody from the outside, and it's frankly not realistic to expect us to take the time to figure it out. If we were sitting down face-to-face we could probably come, pretty quickly, to an understanding of what all those entries represent, what each row and column are for, etc., etc. But we're not.
It would make more sense for you to provide a clear explanation here, not in a private communication, so that any of the other regular Excel-answer people looking at this can follow it, in part because there are others far more expert in some aspects of Excel than I, in part because I don't have all the time in the world, especially at this season of the year.
So I'd suggest you start with a clear explanation of what is being captured--and HOW it's being captured--on each sheet. What all your abbreviations mean, etc., and what is you're trying to accomplish. Don't assume it's clear. Assume, though, that you're speaking (writing) to an intelligent person but one who's never seen this layout before.
Good Afternoon
I apologise for my lack of context, and have now written up a "cheatsheet" for the Rota workbook:
https://1drv.ms/w/s!AtA1R9ZNuMwemwrCfOdQ6ebDmuQx?e=UTBIA9
EDIT: Navigation Pane should have easy bookmarks for you to jump to the desired section
Please let me know if anything is missed out or I have not made anything clear enough to enable you to navigate the sheet effectively.
Please also see a new link, on OneDrive for the spreadsheet as Google Drive has messed with some formatting:
https://1drv.ms/x/s!AtA1R9ZNuMwemwldZDm6ulemZWVS?e=BCqIEk
Goal:
- Summing all the A/L Taken
- Subtracting any rows value, if the corresponding cell determining the staff members role is equal to 'Reg' or 'SpR' as they manage their own (mentioned within cheatsheet)
- Subtracting any rows value if there is no entry for A/L Entitlement, because I would like a seperate formula for anyone that has got 'A/L Taken' values without an 'A/L Entitlement' value.
The goal is to be able to have an outputted figure that represents a "true" value of how much leave has been used (per Ward) [those with A/L Entitlements, that are not Grade: Reg/SpR]