# Refer date from 2 date ranges **URL:** \ **Category:** Ask for Help\ **Created:** [June 24, 2022, 2:42am UTC](https://community.glideapps.com/t/refer-date-from-2-date-ranges/43433 "2022-06-24T02:42:51Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![biha](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/biha/32/84110_2.png) [@biha](https://community.glideapps.com/u/biha)\ **Post date:** [June 24, 2022, 2:42am UTC](https://community.glideapps.com/t/refer-date-from-2-date-ranges/43433/1 "2022-06-24T02:42:51Z")
Continuing the discussion from [Can I get a list of all dates between 2 date columns?](https://community.glideapps.com/t/can-i-get-a-list-of-all-dates-between-2-date-columns/38481/2): 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. ---
**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\ **Post date:** [June 24, 2022, 2:58am UTC](https://community.glideapps.com/t/refer-date-from-2-date-ranges/43433/2 "2022-06-24T02:58:37Z")
> [@biha](#): > > Mine was, I have Leave sheet that have “Start Date” and “End Date” and also got have number of days. 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? ---
**Author:** ![biha](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/biha/32/84110_2.png) [@biha](https://community.glideapps.com/u/biha)\ **Post date:** [June 24, 2022, 3:09am UTC](https://community.glideapps.com/t/refer-date-from-2-date-ranges/43433/3 "2022-06-24T03:09:08Z")
Ooo wait… hmmm ---
**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\ **Post date:** [June 24, 2022, 3:25am UTC](https://community.glideapps.com/t/refer-date-from-2-date-ranges/43433/4 "2022-06-24T03:25:11Z")
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. ---
**Author:** ![biha](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/biha/32/84110_2.png) [@biha](https://community.glideapps.com/u/biha)\ **Post date:** [June 24, 2022, 3:35am UTC](https://community.glideapps.com/t/refer-date-from-2-date-ranges/43433/5 "2022-06-24T03:35:03Z")
Your brain was… ![Conexion GIFs - Get the best GIF on GIPHY](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/f/6/f6c9cf3e8e12fee5080aef1b33e4336aff086f61.gif) Its work! Thanks! ---
**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\ **Post date:** [June 24, 2022, 3:35am UTC](https://community.glideapps.com/t/refer-date-from-2-date-ranges/43433/6 "2022-06-24T03:35:54Z")
hehe, it’s just logic 😉 And, you’re welcome ---
**Author:** ![system](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/system/32/53398_2.png) [@system](https://community.glideapps.com/u/system)\ **Post date:** [June 25, 2022, 3:36am UTC](https://community.glideapps.com/t/refer-date-from-2-date-ranges/43433/7 "2022-06-25T03:36:52Z")
This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.