3 Replies Latest reply on Nov 4, 2019 11:41 AM by Joe Oppelt

    Count Free Time Slots Using Busy Time Data

    Mai Hoang

      I'm looking to count the number of free 30 minute time slots there are across a team for any given day. However, one of the challenges I'm running into is the data I have shows the times they are busy, not the times they are free.

      Originally, the data was structured to one row for each event with a separate start and end datetime column. After reading this: Utilization, Queues, Census, Throughput for Blocks, Floors, Units, Rooms, Offices, etc. - What do you do? , I restructured the data so that there are two rows for each event, with a single time column and a new "type" with values Start and End. I think structuring the data this way will help me find what I'mm looking for but I'm still quite unclear how to do that.


      Any suggestions on where I could start to calculate the "free" time for each user, assuming they are working 9:00 - 5:00PM hours? Bonus if I can then take this solution and apply it to team members who don't work standard 9 - 5 hours.


      I attached a sample of the data I have.


      Thank you all in advance!!


        • 1. Re: Count Free Time Slots Using Busy Time Data
          Joe Oppelt

          I would have start and end in one row, not two.  (And I would have the time and date values combined into one date-time field.)


          You can use lookup -1 to see the delta between the prior row end date-time and the current row start date (assuming you are sorted by start date), and if 30 minutes or more, you have you 30-minute gap identified.

          • 2. Re: Count Free Time Slots Using Busy Time Data
            Mai Hoang

            Hi Joe,


            Thanks so much for your response. I did what you suggested and re-restructured my data and I think it's a great start in calculating open times between meetings. However, I'm running into the challenge of counting time slots before the first meeting of the day. For instance, if someone's first meeting is at 10:00A, I wouldn't be able to count the 2 time slots before that. A similar thing happens for the end of the day. Do you have any ideas of how I would incorporate this? I was thinking something along the lines of creating a dummy data set with this 9AM and 5PM start and end time for the day and blending it somehow.

            • 3. Re: Count Free Time Slots Using Busy Time Data
              Joe Oppelt

              A calc like this:


              { FIXED [User Name], DATE([Start Date-Time Field]) MIN([Start Date-Time Field]) }


              That will tell you the first start date-time for each day.  Don't try to look backwards if the date-time is equal to that calc.


              Doing the MAX([End Date-Time Field]) the same way will tell you the last end-time for each day.


              These calcs can be dimensions.  The value will be set on every row, so it's an easy comparison at the row level.


              The only problem you might encounter is if someone's shift spans across midnight.