Forum Discussion
Excel Annual Leave calculation
Had you intended to attach a copy (or a mockup with names changed) of the spreadsheet? If so, it's not here. If not, please do anyway. Use OneDrive or GoogleDrive and post a link here that grants access.
Then, if I might be so bold, don't try to write the formula for us. Instead, describe in words what you want done. You can refer to the column heads (if there's also an attached spreadsheet), but don't try to write the formula for us--that's what you're coming to ask for help on. Describing it in words is the best way to start off.
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,
Oli
