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

 

[Marcelo Branco]

You are welcome.

M.