Counting time from 2 cells if it is between a range

uwaisrehman

New Member
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

• 149.4 KB Views: 6

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
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
Welcome to the MrExcel board!

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

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

MrExcel MVP, Moderator
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
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?