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:
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!!
Solved! Go to Solution.
Yes all perfectly possibly. Some points/notes
Hoep this helps ๐
Simon@1minManager.com
Yes all perfectly possibly. Some points/notes
Hoep this helps ๐
Simon@1minManager.com
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!
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 ๐
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!!
User | Count |
---|---|
37 | |
30 | |
29 | |
22 | |
18 |