Time Bifurcation based on Time Slots

[scorpio_libra]

Hi All,

Below Table represents the daily task Monitoring for my Team. The first Column contains the received date and time of that task. 2nd Column is the total time taken from received time to complete the task. Typically work starts from morning 9:30AM, so have created 24 more columns from 9:30AM to next Day 8:30AM. Now I am looking for the bifurcation of how much time invested in which slot for each request. for e.g. per below table 1st request received 1:15 PM and time invested to execute that is 1Hr 30 Min, so ideally the first thing, formula should lookup in which slot the request should fall and then add the total Minutes worked in that particular slot. for 1st e.g it should give 15Min in 12:30PM slot and 60Min in 1:30PM slot and remaining 15Min in 2:30Pm slot.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]Request Received
(MM/DD/YY HH:MM)[/TD]
[TD="align: center"]Time Taken to process (HH:MM)[/TD]
[TD="align: center"]9:30
AM[/TD]
[TD="align: center"]10:30
AM[/TD]
[TD="align: center"]11:30
AM[/TD]
[TD="align: center"]12:30
PM[/TD]
[TD="align: center"]1:30
PM[/TD]
[TD="align: center"]2:30
PM[/TD]
[TD="align: center"]3:30
PM[/TD]
[TD="align: center"]4:30
PM[/TD]
[TD="align: center"]5:30
PM[/TD]
[TD="align: center"]6:30
PM[/TD]
[TD="align: center"]7:30
PM[/TD]
[TD="align: center"]8:30
PM[/TD]
[TD="align: center"]9:30
PM[/TD]
[TD="align: center"]10:30
PM[/TD]
[TD="align: center"]11:30
PM[/TD]
[TD="align: center"]12:30
AM[/TD]
[TD="align: center"]1:30
AM[/TD]
[TD="align: center"]2:30
AM[/TD]
[TD="align: center"]3:30
AM[/TD]
[TD="align: center"]4:30
AM[/TD]
[TD="align: center"]5:30
AM[/TD]
[TD="align: center"]6:30
AM[/TD]
[TD="align: center"]7:30
AM[/TD]
[TD="align: center"]8:30
AM[/TD]
[/TR]
[TR]
[TD]01-01-2013 13:15:00[/TD]
[TD="align: center"]01:30:00[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]60[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD]1-01-2013 14:30:00[/TD]
[TD="align: center"]07:40:00[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]60[/TD]
[TD="align: center"]60[/TD]
[TD="align: center"]60[/TD]
[TD="align: center"]60[/TD]
[TD="align: center"]60[/TD]
[TD="align: center"]60[/TD]
[TD="align: center"]60[/TD]
[TD="align: center"]40[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD]01-02-2013 23:38:00[/TD]
[TD="align: center"]00:55:00[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]52[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]

I tried all the possible option I can, however still struggling to get the desired functions. Any help/advise will be highly appreciated

 

[scorpio_libra]

Hi Everyone, is there any solution to this?

 

[scorpio_libra]

is it that tough?