Counting time from 2 cells if it is between a range

uwaisrehman

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

  • Capture.PNG
    Capture.PNG
    149.4 KB · Views: 15

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Welcome to the MrExcel board!

I cannot read (or copy from) the images What about using XL2BB? See my signature block below.
 
Upvote 0
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
 
Upvote 0
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. ;)
 
Upvote 0
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​
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,972
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top