I have read this issue but the situation could be different.
Mine was, I have Leave sheet that have “Start Date” and “End Date” and also got have number of days.
Another sheet we have is timesheet which some employee need to List what they do for the particular Date.
Eg : Employee 1 Date : 1 Feb 2022
Activity in Timesheet
1 Feb 2022 : Troubleshoot : 4 hours
1 Feb 2022 : Repairing : 2 hours
1 Feb 2022 : Travelling : 2 hours
Employee shall enter more than 8 hours in total (I’m using Roll up) , cannot be less. More than 8 hours consider OT. If less, Error Message will show up.
My question is , let say, the Employee 1 take a leave for half day or time-off, how to reflect at the “Timesheet” that the particular Date can be entered less than 8 hours.
Let’s say an employee has a leave record with a start date of Jun 22 and an end date of Jun 24, and the number of days is 2.5. This means they took 2 full days and 1 half day. How would you know which day is the half day?
I suppose what you could do is if they enter hours worked on any day that overlaps with their leave dates, then allow them to enter less than 8 hours. This would be the simplest thing to do.
So if that’s good enough, then something like this should work:
In your leave table, add a Single Value column that takes the timesheet date and applies it to all rows.
Then create an if-then-else column:
– If Leave Record user is not signed in user, then blank
– If Timesheet date is after Leave End date, then blank
– If Timesheet date is before Leave Start date, then blank
– Else true
Now back in your Timesheet table, do a rollup on that if-then-else column, counting the number of true values. If it’s greater than zero, then that means they are on leave on that date and you can allow them to enter less than 8 hours.
