Counting time from 2 cells if it is between a range

[uwaisrehman]

Hi,

i'm a newbie in need of urgent help.

I have a data set that records meeting occurrences. The start time and end time of the meeting are in a cell each (Rows I and J in the attached image).

I am trying to count meeting occurrences looking at half hour intervals.

For example if a meeting started at 9:00 and finished at 10:30 then my formula would have a count of 1 for 9:00, 9:30, 10:00 and 10:30. So far i have only been able to count the time the meeting starts and finishes. I have been able to do this using a pivot table and count if. As there is not a different row of data for every half hour i'm only able to count the times shown. For example a meeting that started at 9:00 and finished at 16:00 should have a count of 1 for all the hours between 9:00 and 16:00, not just a count of 1 for 9:00 and 16:00.

Any ideas about a formula that may work or another solution.

Thanks for all the help in advance.

Uwais

 

[Peter_SSs]

Welcome to the MrExcel board!

I cannot read (or copy from) the images What about using XL2BB? See my signature block below.

 

[uwaisrehman]

Welcome to the MrExcel board!

I cannot read (or copy from) the images What about using XL2BB? See my signature block below.

Thanks for the reply.

I'm not actually familiar with XL2BB. I was hoping there would be a variation of a countif formula i could use. I had a play with countif (to count times between see below) and pivot tables but can not get a count for every hour when the meeting starts at 9 and finishes at 5.

=COUNTIFS($A:$A,$U$9,$F:$F,">=9:00",$G:$G,"<=17:00")

Sorry for the inital poor image quality, i have re uploaded.

Thanks

Uwaiws

 

[Peter_SSs]

Sorry for the inital poor image quality, i have re uploaded.

But we still cannot copy from that to test. Perhaps you could investigate and become familiar with XL2BB? If you want help, you should be prepared to 'help the helpers' by not expecting them to manually type out all that sample data.

 

[uwaisrehman]

But we still cannot copy from that to test. Perhaps you could investigate and become familiar with XL2BB? If you want help, you should be prepared to 'help the helpers' by not expecting them to manually type out all that sample data.

My apologies I never realised you were looking for a copy of the data, see below for a sample. Can i upload an Excel file to the thread?

Meeting Room	Service	Booking Type	Date	Day	Start	Duration	Month	Meeting Start	Meeting End	Meeting Duration
Room 6	Service 1	Ad-hoc	16/04/2019​	Tuesday	11​	1​	April	11:30:00​	12:30:00​	1:00​
Room 6	Service 1	Ad-hoc	16/09/2019​	Monday	10​	2​	September	10:30:00​	12:00:00​	1:30​
Room 6	Service 1	Ad-hoc	06/09/2019​	Friday	10​	2​	September	10:30:00​	12:00:00​	1:30​
Room 6	Service 1	Ad-hoc	14/08/2019​	Wednesday	10​	2​	August	10:30:00​	12:00:00​	1:30​
Room 6	Service 1	Ad-hoc	24/04/2019​	Wednesday	11​	1​	April	11:00:00​	12:00:00​	1:00​
Room 6	Service 1	Ad-hoc	16/04/2019​	Tuesday	14​	1​	April	14:00:00​	15:00:00​	1:00​
Room 6	Service 1	Ad-hoc	17/04/2019​	Wednesday	14​	1​	April	14:00:00​	15:00:00​	1:00​
Room 6	Service 1	Ad-hoc	10/04/2019​	Wednesday	12​	3​	April	12:00:00​	14:30:00​	2:30​
Room 6	Service 1	Ad-hoc	03/04/2019​	Wednesday	13​	1​	April	13:00:00​	14:00:00​	1:00​
Room 6	Service 1	Ad-hoc	02/04/2019​	Tuesday	13​	1​	April	13:00:00​	14:00:00​	1:00​
Room 6	Service 1	Ad-hoc	25/09/2019​	Wednesday	12​	1​	September	12:00:00​	13:00:00​	1:00​
Room 6	Service 1	Ad-hoc	30/09/2019​	Monday	10​	1​	September	10:00:00​	11:00:00​	1:00​
Room 6	Service 1	Ad-hoc	19/09/2019​	Thursday	11​	1​	September	11:30:00​	12:30:00​	1:00​
Room 6	Service 1	Ad-hoc	17/09/2019​	Tuesday	11​	1​	September	11:00:00​	12:00:00​	1:00​
Room 6	Service 1	Ad-hoc	24/09/2019​	Tuesday	11​	1​	September	11:00:00​	12:00:00​	1:00​
Room 6	Service 1	Ad-hoc	24/09/2019​	Tuesday	9​	1​	September	09:30:00​	10:30:00​	1:00​
Room 6	Service 1	Ad-hoc	24/09/2019​	Tuesday	16​	1​	September	16:00:00​	17:00:00​	1:00​
Room 6	Service 1	Ad-hoc	24/09/2019​	Tuesday	13​	1​	September	13:00:00​	14:00:00​	1:00​
Room 6	Service 1	Ad-hoc	17/09/2019​	Tuesday	15​	1​	September	15:30:00​	16:30:00​	1:00​
Room 6	Service 1	Ad-hoc	16/09/2019​	Monday	13​	1​	September	13:30:00​	14:30:00​	1:00​
Room 6	Service 1	Ad-hoc	17/09/2019​	Tuesday	14​	1​	September	14:30:00​	15:30:00​	1:00​
Room 6	Service 1	Ad-hoc	10/09/2019​	Tuesday	15​	1​	September	15:00:00​	16:00:00​	1:00​
Room 6	Service 1	Ad-hoc	16/09/2019​	Monday	12​	1​	September	12:30:00​	13:30:00​	1:00​
Room 6	Service 1	Ad-hoc	16/09/2019​	Monday	10​	1​	September	10:00:00​	11:00:00​	1:00​
Room 6	Service 1	Ad-hoc	10/09/2019​	Tuesday	16​	1​	September	16:00:00​	17:00:00​	1:00​
Room 6	Service 1	Ad-hoc	22/08/2019​	Thursday	15​	1​	August	15:00:00​	16:00:00​	1:00​
Room 6	Service 1	Ad-hoc	22/08/2019​	Thursday	16​	1​	August	16:00:00​	17:00:00​	1:00​
Room 6	Service 1	Ad-hoc	22/08/2019​	Thursday	14​	1​	August	14:00:00​	15:00:00​	1:00​
Room 6	Service 1	Ad-hoc	08/08/2019​	Thursday	13​	1​	August	13:00:00​	14:00:00​	1:00​