Calendar Vacation Rules

[marsbar]

Hi All,

Im looking at a Calendar for booking annual leave for a small business. My thoughts are...

A table with Name, Email, Line Manager, Leave Allowance, Leave Taken, Leave Remaining and also a "Buddy" Column.

Then I would like to have 3 main rules:

• No more than 2 people on leave at the same time (2 is fine but, no more than that)
• You cannot be on leave at the same time as your Buddy
• Leave needs to be verified by your line manager before it is accepted

is this possible, I have read a few articles on the calendar function but not found anything that helps me with the above sadly?

Thanks In Advance!!

[1minManager · Accepted solution]

Yes all perfectly possibly.  Some points/notes

• Your going to need a Employee table and a subtable called Vacation
• To use a calendar view the Holiday table will need Start Date, Start Time, End Date & End Time
• Employee[Leave Taken] and Employee[Leave Remaining] will need to be virtual columns
• "Leave needs to be verified by your line manager before it is accepted" When I've done this the process is
  • Employee submits request
  • App emails manager
  • Manager goes into a view of requests
  • They either authorise the vacation, or not
  • If not then they have to explain why
  • After saving the employee gets and email with the result and if its a no then the reason as well
• "No more than 2 people on leave at the same time (2 is fine but, no more than that)" will need to be done as a Valid_IF statement on both Start Date & End Date that checks for overlapping vacations
• Leave Taken should be a fairly simple formula to do but you'll need a Vacation[Total Days] column as you can't both find that employees records and SUM() the total days in one formula
• Vacation[Leave Remaining] is going to be potentially the most complex depending on what the rules for vacation are e.g. employee works Sat/Sun?, does it have to take account of christmas/thanksgiving or similar, can they carry over leave from one year to the next?, is leave pro-rata-ed from when thet start to the end of the year?

Hoep this helps 🙂

Simon@1minManager.com

[marsbar]

Sorry - only just seen this for some reason! 

Thanks very much for this - Really appreciated... could you elaborate a little on this point please:

No more than 2 people on leave at the same time (2 is fine but, no more than that)" will need to be done as a Valid_IF statement on both Start Date & End Date that checks for overlapping vacations

Thanks Again! 

[1minManager]

So you need a valid_IF formula that basically stops people creating a record.  So if I put in a request for 09:00 1/3/2023 to 17:00 7/3/23 (using ddmyyyy format) then it will not let me save that record.

To check for no more than 2 other requests the formula is going to have to be a count along the lines of COUNT(Select(TimesheetTable[TableID],AND(...   )))<=2.  You're goign to have to also put in something like [TableID]<>[_ThisRow].[TableID] so that the count doesn't count the current record.

Now the overlap equation is a PITA.  You're basically writting a formula that says:

"Go into to the timesheet table and find me the ID of any record which matches these rules"

But there are lots of ways dates can overlap.  Checkout the image below:

So A & B are the Start & End dates in the current row.  The other lines are Start & End dates in other records your going to have to check if they exist.  These others can start before, between, on the same date as, or after A/B.

So the top line is looking for other records which start and end before A.  Which is actually ok, you don't need this rule, its just for illustration. 

But the 2nd line shows you need to check for records which end on the start date of A & B. 

The 3rd line needs to look for records which end between A & B.

One I've missed is where other records start and end between A & B, so add that one yourself 😕

And so on for all 10 checks.  Now by cunning use of >= and <= and some logical reasons you can reduce these 10 down to about maybe 4 of 5.  But I'll leave that up to you.

If you're letting someone else use this app, you might also want to put into these rules an element of idiot proofing such as saying that the Start Date has to be after or equal to the end date.

Hope this helps 🙂

[marsbar]

Wow - Im going to have to re read that a few times to try and get my head around it I think haha! Thanks very much for this - Its already got me baffled!!