Extract time chunks from time range

[th081]

Hi,

I have a start date and time in C1 e.g 01/07/2017 16:25:00 (date is in DD.mm.yyyy format ). In D1 I have an end date and time e.g 01/07/2017 22:15:00. The day is not relevant and is always the same in both cells.

I then have the day broken down into half hour chunks eg A2 has 00:00 and B2 has 00:30, then A3 has 00:30 and B3 01:00 etc. So 48 rows with a start half hour and end.

I then want in C2 and C3 etc how many minutes in the half hour are covered by the time range. So in my example it would be zero mins until 16:00 to 16:30 which would show 25 mins then the half hours would show 30 mins until 22:00 to 22:30 which would show 15 mins.

Some times the time range can be small eg 01/07/2017 15:02 to 01/07/2017 15:07 so this would just show 5 mins in the 15:00 to 15:07 slot.

I hope the above is clear if not please let me know.

Regards

 

[Scott R]

Put in cell C2 and copy down through your 48 rows:
=MAX(0, MIN(D$1 - INT(D$1), $B2) - MAX(C$1 - INT(C$1), $A2))

 

[Marcelo Branco]

I think the result in 16:00 - 16:30 should be 5, not 25 (16:30- 16:25)
If i'm right try this formula in C2 copied down
=MAX(0,MIN(B2,MOD(D$1,1))-MAX(A2,MOD(C$1,1)))*24*60

M.

 

[th081]

Thank you both, much appreciated.

Scott your formula works a treat.

Marcelo yours does not return the info but thank you for replying.

 

[Marcelo Branco]

Thank you both, much appreciated.

Scott your formula works a treat.

Marcelo yours does not return the info but thank you for replying.

Glad you have a working formula, but i have to say the formula worked perfectly for me. Actually its logic is identical to that of Scott's formula.

M.

 

[th081]

Your right Marcelo it does work. I had the formatting set to HH:mm for those cells when i changed to number it showed the correct data.

 

[Marcelo Branco]

Your right Marcelo it does work. I had the formatting set to HH:mm for those cells when i changed to number it showed the correct data.

Good to know
Thanks for the feedback.

M.

 

[th081]

Hi Guys,

The previous formula works however i thought the day would aways be the same but unfortunately it is not. Because the formula only looks at the time it gives an incorrect result when the the range is longer than a day. e.g if the start day and time was 01/07/17 16:25 and the end date 02/07/17 22:15 then the formula only returns data between the times 16:25 and 22:15 on both days. The max days will be four so i have created 48*4 half hour rows but the formula only return data between 16:00 and 22:00. Outside of this it is showing zero as the mins.

Can anybody help to make it work with more than one day range?

Thanks.

 

[Marcelo Branco]

Maybe something like this

Say
C1 contains start date/time: 01/07/2017 16:25
D1 contains end date/time: 02/07/2017 22:15

Type in A1:B2

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Lower​

[/td][td]

Higher​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

01/07/2017 00:00​

[/td][td]

01/07/2017 00:30​

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

Headers in A1:B1

Formula in A2
=INT(C1)

Formula in B2
=INT(C1)+1/48

In A3 type this formula
=A2+1/48

copy across to B3

Select A3:B3 and copy (drag) down till row 193 where you get

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

193​

[/td][td]

04/07/2017 23:30​

[/td][td]

05/07/2017 00:00​

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

Put this formula in C2 and copy (drag) down till row 193
=ROUND(MAX(0,MIN(D$1,B2)-MAX(C$1,A2))*24*60,0)

Hope this helps

M.

 

[th081]

Thanks once again M.

Regards