Hours in time slots

[esar]

Hi,

I basically have Start time and End time.

I need to calculate how many hours belong to specific time slot 06-18, 18-22 and 22-06.

Example:

Start        End

06:27:00 14:28:00
06:27:00 15:28:00
06:29:00 14:28:00
10:58:00 19:03:00
10:58:00 19:03:00
13:30:00 21:33:00
13:30:00 22:33:00
21:55:00 05:55:00
06:22:00 14:31:00
06:22:00 14:31:00

Do you have any idea how to calculate this?

Any help is very appreciated.

[david_fetters]

Wow, this is such an elegantly simple problem but it has me stumped at the moment.  I will be thinking about this all evening unless somebody solves it first

[BenMoss]

Completely miss read the objective of the post! Thought it was a bit simple! Give me a few more minutes :)

[david_fetters]

Right!  Its deceptively simply at first.  To add to the discussion, I think there are three edge cases to anticipate:

1. The time wraps around overnnight (you have one case of this) so your start time is later than your end time.
2. The time crosses two time groups, so the total hours needs to be subdivided into two buckets.
3. The time crosses into three groups, so one group receives all of its possible hours, and both other groups receive some portion less.

I think handling the first one will be easier if you break 'wraparound' times (and your bucket that wraps around) into separate groups.  E.g. your bucket from 22-6 should be two buckets from 22-24 and 0-6.  We can handle combining them after we address the allocation problem.  Same thing with your start/end time that wraps around.  Maybe give each row an ID, and initially process those wraparound times into two rows with the same ID.

Solving the rest of the solution can be done with a lot of conditional logic (e.g. if both the start and end time are each greater than the bucket's start and less than the bucket's end, we know that the total hours in that bucket is start - end) but it seems really unwieldy.  I'm still holding out for something more elegant.  If I can't get it by tonight, i'll probably break and just write out the conditional logic.

[BenMoss · Accepted solution]

Here's my attempt. Looks to work, but as the poster mentioned above there has to be some logic in regards to 'what is the next day'.

[esar · Accepted solution]

I ended up using lot of logic.

But I really would like to make or get macro or new tool for that. It would be very useful for salary calculations.

====18-22====

IF [Start] >= '06:00:00' and [Start] <= '18:00:00' and [End] > '06:00:00' and [End] <= '18:00:00' THEN 0
ELSEIF [Start] >= '18:00:00' and [Start] < '22:00:00' and [End] > '18:00:00' and [End] <= '22:00:00' THEN DateTimeDiff([Start],[End],'minute')/60
ELSEIF [Start] >= '06:00:00' and [Start] <= '18:00:00' and [End] > '18:00:00' and [End] <= '22:00:00' THEN DateTimeDiff([End],'18:00:00','minute')/60
ELSEIF [Start] >= '06:00:00' and [Start] <= '18:00:00' THEN 3
ELSEIF [Start] >= '18:00:00' and [Start] < '22:00:00' THEN DateTimeDiff('22:00:00',[Start],'minute')/60
ELSE 0 ENDIF

====22-06====
IF [Start] >= '06:00:00' and [Start] < '22:00:00' and [End] > '06:00:00' and [End] <= '22:00:00' THEN 0
ELSEIF [Start] >= '22:00:00' and [Start] < '23:59:59' and [End] > '22:00:00' and [End] <= '23:59:59' THEN DateTimeDiff([Start],[End],'minute')/60
ELSEIF [Start] >= '06:00:00' and [Start] < '22:00:00' and [End] > '00:00:00' and [End] <= '06:00:00' THEN 2 + DateTimeDiff([End],'00:00:00','minute')/60
ELSEIF [Start] >= '22:00:00' and [Start] < '23:59:59' and [End] > '00:00:00' and [End] <= '06:00:00' THEN (DateTimeDiff('23:59:00',[Start],'minute')+1)/60 + DateTimeDiff([End],'00:00:00','minute')/60
ELSEIF [Start] >= '22:00:00' and [Start] < '23:59:59' and [End] > '06:00:00' and [End] <= '22:00:00' THEN (DateTimeDiff([Start],'23:59:00','minute')+1)/60 + 6
ELSEIF [Start] >= '00:00:00' and [Start] < '06:00:00' and [End] > '22:00:00' and [End] <= '23:59:59' THEN DateTimeDiff([Start],'06:00:00','minute')/60 + DateTimeDiff('22:00:00',[End],'minute')/60
ELSEIF [End] > '22:00:00' and [End] <= '23:59:59' THEN DateTimeDiff([End],'22:00:00','minute')/60
ELSE 0 ENDIF

[esar]

---

@BenMoss wrote:

Here's my attempt. Looks to work, but as the poster mentioned above there has to be some logic in regards to 'what is the next day'.

---

This looks much simpler and generic than what I did with multiple IF functions.

Thanks.