Counting time from 2 cells if it is between a range

uwaisrehman

New Member
Joined
Dec 10, 2019
Messages
3
Office Version
365, 2019
Platform
Windows
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
 

Attachments

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
43,526
Office Version
365
Platform
Windows
Welcome to the MrExcel board!

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

uwaisrehman

New Member
Joined
Dec 10, 2019
Messages
3
Office Version
365, 2019
Platform
Windows
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

Table.png
Dataset.png
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
43,526
Office Version
365
Platform
Windows
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

New Member
Joined
Dec 10, 2019
Messages
3
Office Version
365, 2019
Platform
Windows
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 RoomServiceBooking TypeDateDayStartDurationMonthMeeting StartMeeting EndMeeting Duration
Room 6Service 1Ad-hoc
16/04/2019​
Tuesday
11​
1​
April
11:30:00​
12:30:00​
1:00​
Room 6Service 1Ad-hoc
16/09/2019​
Monday
10​
2​
September
10:30:00​
12:00:00​
1:30​
Room 6Service 1Ad-hoc
06/09/2019​
Friday
10​
2​
September
10:30:00​
12:00:00​
1:30​
Room 6Service 1Ad-hoc
14/08/2019​
Wednesday
10​
2​
August
10:30:00​
12:00:00​
1:30​
Room 6Service 1Ad-hoc
24/04/2019​
Wednesday
11​
1​
April
11:00:00​
12:00:00​
1:00​
Room 6Service 1Ad-hoc
16/04/2019​
Tuesday
14​
1​
April
14:00:00​
15:00:00​
1:00​
Room 6Service 1Ad-hoc
17/04/2019​
Wednesday
14​
1​
April
14:00:00​
15:00:00​
1:00​
Room 6Service 1Ad-hoc
10/04/2019​
Wednesday
12​
3​
April
12:00:00​
14:30:00​
2:30​
Room 6Service 1Ad-hoc
03/04/2019​
Wednesday
13​
1​
April
13:00:00​
14:00:00​
1:00​
Room 6Service 1Ad-hoc
02/04/2019​
Tuesday
13​
1​
April
13:00:00​
14:00:00​
1:00​
Room 6Service 1Ad-hoc
25/09/2019​
Wednesday
12​
1​
September
12:00:00​
13:00:00​
1:00​
Room 6Service 1Ad-hoc
30/09/2019​
Monday
10​
1​
September
10:00:00​
11:00:00​
1:00​
Room 6Service 1Ad-hoc
19/09/2019​
Thursday
11​
1​
September
11:30:00​
12:30:00​
1:00​
Room 6Service 1Ad-hoc
17/09/2019​
Tuesday
11​
1​
September
11:00:00​
12:00:00​
1:00​
Room 6Service 1Ad-hoc
24/09/2019​
Tuesday
11​
1​
September
11:00:00​
12:00:00​
1:00​
Room 6Service 1Ad-hoc
24/09/2019​
Tuesday
9​
1​
September
09:30:00​
10:30:00​
1:00​
Room 6Service 1Ad-hoc
24/09/2019​
Tuesday
16​
1​
September
16:00:00​
17:00:00​
1:00​
Room 6Service 1Ad-hoc
24/09/2019​
Tuesday
13​
1​
September
13:00:00​
14:00:00​
1:00​
Room 6Service 1Ad-hoc
17/09/2019​
Tuesday
15​
1​
September
15:30:00​
16:30:00​
1:00​
Room 6Service 1Ad-hoc
16/09/2019​
Monday
13​
1​
September
13:30:00​
14:30:00​
1:00​
Room 6Service 1Ad-hoc
17/09/2019​
Tuesday
14​
1​
September
14:30:00​
15:30:00​
1:00​
Room 6Service 1Ad-hoc
10/09/2019​
Tuesday
15​
1​
September
15:00:00​
16:00:00​
1:00​
Room 6Service 1Ad-hoc
16/09/2019​
Monday
12​
1​
September
12:30:00​
13:30:00​
1:00​
Room 6Service 1Ad-hoc
16/09/2019​
Monday
10​
1​
September
10:00:00​
11:00:00​
1:00​
Room 6Service 1Ad-hoc
10/09/2019​
Tuesday
16​
1​
September
16:00:00​
17:00:00​
1:00​
Room 6Service 1Ad-hoc
22/08/2019​
Thursday
15​
1​
August
15:00:00​
16:00:00​
1:00​
Room 6Service 1Ad-hoc
22/08/2019​
Thursday
16​
1​
August
16:00:00​
17:00:00​
1:00​
Room 6Service 1Ad-hoc
22/08/2019​
Thursday
14​
1​
August
14:00:00​
15:00:00​
1:00​
Room 6Service 1Ad-hoc
08/08/2019​
Thursday
13​
1​
August
13:00:00​
14:00:00​
1:00​
 

Forum statistics

Threads
1,089,636
Messages
5,409,460
Members
403,264
Latest member
naturally_data

This Week's Hot Topics

Top