Spread hours/min over 15 min time intervals

[chrono2483]

Hello,

I am looking to see if it is possible to take a value representing hours/min and break it up into 15 min interval. From here, add each 15 min interval to the corresponding section (and continue to tally if there are multiples for that section).

Ex.

1) Start = 19:00 End = 22:00 (3 hrs)
2) Start = 19:00 End = 20:30 (1.5 hrs)
3) Start = 20:00 End = 23:00 (3 hrs)

I use a formula to convert into a # (3, 1, 3 respectively). What I then want to do is break these hrs into 15 min intervals and tally them against a time chart:


[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[/TR]
[TR]
[TD="align: center"]18:30
[/TD]
[TD="align: center"]0
[/TD]
[/TR]
[TR]
[TD="align: center"]18:45
[/TD]
[TD="align: center"]0
[/TD]
[/TR]
[TR]
[TD="align: center"]19:00
[/TD]
[TD="align: center"]2
[/TD]
[/TR]
[TR]
[TD="align: center"]19:15
[/TD]
[TD="align: center"]2
[/TD]
[/TR]
[TR]
[TD="align: center"]19:30
[/TD]
[TD="align: center"]2
[/TD]
[/TR]
[TR]
[TD="align: center"]19:45
[/TD]
[TD="align: center"]2
[/TD]
[/TR]
[TR]
[TD="align: center"]20:00
[/TD]
[TD="align: center"]3
[/TD]
[/TR]
[TR]
[TD="align: center"]20:15
[/TD]
[TD="align: center"]3
[/TD]
[/TR]
[TR]
[TD="align: center"]20:30
[/TD]
[TD="align: center"]2
[/TD]
[/TR]
[TR]
[TD="align: center"]20:45
[/TD]
[TD="align: center"]2
[/TD]
[/TR]
[TR]
[TD="align: center"]21:00
[/TD]
[TD="align: center"]2
[/TD]
[/TR]
[TR]
[TD="align: center"]21:15
[/TD]
[TD="align: center"]2
[/TD]
[/TR]
[TR]
[TD="align: center"]21:30
[/TD]
[TD="align: center"]2
[/TD]
[/TR]
[TR]
[TD="align: center"]21:45
[/TD]
[TD="align: center"]2
[/TD]
[/TR]
[TR]
[TD="align: center"]22:00
[/TD]
[TD="align: center"]1
[/TD]
[/TR]
[TR]
[TD="align: center"]22:15
[/TD]
[TD="align: center"]1
[/TD]
[/TR]
[TR]
[TD="align: center"]22:30
[/TD]
[TD="align: center"]1
[/TD]
[/TR]
[TR]
[TD="align: center"]22:45
[/TD]
[TD="align: center"]1
[/TD]
[/TR]
[TR]
[TD="align: center"]23:00
[/TD]
[TD="align: center"]0
[/TD]
[/TR]
[TR]
[TD="align: center"]23:15
[/TD]
[TD="align: center"]0
[/TD]
[/TR]
[TR]
[TD="align: center"]23:30
[/TD]
[TD="align: center"]0
[/TD]
[/TR]
</tbody>[/TABLE]

Is there a way to do this? I'm thinking a combination of index/match, to match the times with the hours?

Thanks!

 

[Anthony47]

Sorry, I didn't understand your request, and found the table hard to relate to the initial phrase.
So if you don't receive useful suggestions I should recommend that you rewrite your need, specifying which area your initial data and which results you are looking for.

Bye

 

[shg]

Maybe ...

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][/tr][tr][td]

1​

[/td][td]

Start​

[/td][td]

19:00​

[/td][td]

19:00​

[/td][td]

20:00​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

2​

[/td][td]

End​

[/td][td]

22:00​

[/td][td]

20:30​

[/td][td]

23:00​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

3​

[/td][td]

Time​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

4​

[/td][td]

18:30​

[/td][td]

00:00​

[/td][td]

00:00​

[/td][td]

00:00​

[/td][td]

00:00​

[/td][td][/td][td]B4 and across and down: =MAX(0, MIN(B$2, $A4 + "0:15") - MAX(B$1, $A4))[/td][/tr]
[tr][td]

5​

[/td][td]

18:45​

[/td][td]

00:00​

[/td][td]

00:00​

[/td][td]

00:00​

[/td][td]

00:00​

[/td][td][/td][td]E4 and down: =SUM(B4:D4)[/td][/tr]
[tr][td]

6​

[/td][td]

19:00​

[/td][td]

00:15​

[/td][td]

00:15​

[/td][td]

00:00​

[/td][td]

00:30​

[/td][td][/td][td][/td][/tr]
[tr][td]

7​

[/td][td]

19:15​

[/td][td]

00:15​

[/td][td]

00:15​

[/td][td]

00:00​

[/td][td]

00:30​

[/td][td][/td][td][/td][/tr]
[tr][td]

8​

[/td][td]

19:30​

[/td][td]

00:15​

[/td][td]

00:15​

[/td][td]

00:00​

[/td][td]

00:30​

[/td][td][/td][td][/td][/tr]
[tr][td]

9​

[/td][td]

19:45​

[/td][td]

00:15​

[/td][td]

00:15​

[/td][td]

00:00​

[/td][td]

00:30​

[/td][td][/td][td][/td][/tr]
[tr][td]

10​

[/td][td]

20:00​

[/td][td]

00:15​

[/td][td]

00:15​

[/td][td]

00:15​

[/td][td]

00:45​

[/td][td][/td][td][/td][/tr]
[tr][td]

11​

[/td][td]

20:15​

[/td][td]

00:15​

[/td][td]

00:15​

[/td][td]

00:15​

[/td][td]

00:45​

[/td][td][/td][td][/td][/tr]
[tr][td]

12​

[/td][td]

20:30​

[/td][td]

00:15​

[/td][td]

00:00​

[/td][td]

00:15​

[/td][td]

00:30​

[/td][td][/td][td][/td][/tr]
[tr][td]

13​

[/td][td]

20:45​

[/td][td]

00:15​

[/td][td]

00:00​

[/td][td]

00:15​

[/td][td]

00:30​

[/td][td][/td][td][/td][/tr]
[tr][td]

14​

[/td][td]

21:00​

[/td][td]

00:15​

[/td][td]

00:00​

[/td][td]

00:15​

[/td][td]

00:30​

[/td][td][/td][td][/td][/tr]
[tr][td]

15​

[/td][td]

21:15​

[/td][td]

00:15​

[/td][td]

00:00​

[/td][td]

00:15​

[/td][td]

00:30​

[/td][td][/td][td][/td][/tr]
[tr][td]

16​

[/td][td]

21:30​

[/td][td]

00:15​

[/td][td]

00:00​

[/td][td]

00:15​

[/td][td]

00:30​

[/td][td][/td][td][/td][/tr]
[tr][td]

17​

[/td][td]

21:45​

[/td][td]

00:15​

[/td][td]

00:00​

[/td][td]

00:15​

[/td][td]

00:30​

[/td][td][/td][td][/td][/tr]
[tr][td]

18​

[/td][td]

22:00​

[/td][td]

00:00​

[/td][td]

00:00​

[/td][td]

00:15​

[/td][td]

00:15​

[/td][td][/td][td][/td][/tr]
[tr][td]

19​

[/td][td]

22:15​

[/td][td]

00:00​

[/td][td]

00:00​

[/td][td]

00:15​

[/td][td]

00:15​

[/td][td][/td][td][/td][/tr]
[tr][td]

20​

[/td][td]

22:30​

[/td][td]

00:00​

[/td][td]

00:00​

[/td][td]

00:15​

[/td][td]

00:15​

[/td][td][/td][td][/td][/tr]
[tr][td]

21​

[/td][td]

22:45​

[/td][td]

00:00​

[/td][td]

00:00​

[/td][td]

00:15​

[/td][td]

00:15​

[/td][td][/td][td][/td][/tr]
[tr][td]

22​

[/td][td]

23:00​

[/td][td]

00:00​

[/td][td]

00:00​

[/td][td]

00:00​

[/td][td]

00:00​

[/td][td][/td][td][/td][/tr]
[tr][td]

23​

[/td][td]

23:15​

[/td][td]

00:00​

[/td][td]

00:00​

[/td][td]

00:00​

[/td][td]

00:00​

[/td][td][/td][td][/td][/tr]
[tr][td]

24​

[/td][td]

23:30​

[/td][td]

00:00​

[/td][td]

00:00​

[/td][td]

00:00​

[/td][td]

00:00​

[/td][td][/td][td][/td][/tr]
[/table]

 

[chrono2483]

Sorry - I hope this helps clarify (including a snapshot of the actual spreadsheet). To explain the image below:

- Col A will remain the same, with the time in 15 min intervals
- Col O & P are taken from another spreadsheet. The values will vary based on the data being pulled, but each row in O/P will have a start and end time
- Col Q is the number of hours between O & P, in number form (instead of time)
- Col B is what I want to do/retrieve. The example below is only focusing on the time interval 1600-1645. Based on the data pulled in O & P, and knowing the number of intervals using the data in Q, I want to be able to sum/tally in Col B. So, with O3/P3 there is 3.5 hrs (14 intervals). Therefore, I want each interval in Col B to keep track of that (a running tally, adding 1 for each occurrence) starting in B38 to B51. It will scroll through the data in O/P and tally each occurrence in B.

I hope that helps explain better? I like the idea of MAX/MIN, and will play with that to see if I can accomplish my goal - but with the info above, any additional suggestions?

 

[chrono2483]

Giving Countif and Sumproduct a try, but seems tedious to have to do using formulas for each time interval

 

[Anthony47]

I think the below formula should do the job:

=SUMPRODUCT(--(A2>=$O$2:$O$70),--(A3<=$P$2:$P$70))

Put it in B2 and copy down.
You need also to insert in A70 the end period for the slot starting in A69, ie 24:00 (probably this will display as 00:00, don't get confused).
For the formula to work, information in col A, O & P must be in pure hours; to check if this is true, format those columns as Numbers with 3 Decimals: they should display as numbers from 0.000 (00:00) to 1.000 (24:00).

Bye

 

[gaz_chops]

Try

=COUNTIFS($O$2:$O$69,"<="&A2,$P$2:$P$69,">"&A2)