I have start and end date time for various employees in the data. The time difference between these slots can be 1 hour, 2 hours, or 6 hours, for example. I need to write logic that will automatically divide these time slots into one-hour segments if the time difference between start and end is greater than one hour.
For example:
Start Time: 20:00
End Time:22:00
Then slots should be divided as 20:00-21:00 and 21:00-22:00
You could probably do it with a 'scaffold' of just hours and their related buckets. It' a simple MS-Excel sheet of consecutive hours and the buckets. Like below and attached:
I've brought the data set down to something more easily readable/presentable to help with understanding of the steps/concept. So just using one person who has a two-hour duration from 20:00-22:00 for example:
Then join on the 'hour' in the scaffold to the 'hour' in the Start and End times:
Note the join operators - that's important! Everything in RED is being exclude and only the items in BLACK are being considered a match. Then a Clean Step to show how it looks for just one date 4/26/23. Note the new column for Hour shows the respective hour in (new row) for each date. So, 20:00, 21:00, 22:00 for each date:
Give it a go. The screenshots and attached should be enough to work off of. Best to learn by doing it on your own.
Best, Don
(Please, don't forget to click Select as Best or Upvote !)
