Percentage of on time customers in a list by the hour

Hello everyone,

I need to make a chart listing customer collections by the hour.

Column A would have varying amounts of customers each day.

Column B would have their booked collection times.

Column C would be used for the phrase "on time" to be entered if the collection is on time.

Column D would have a list of times e.g 09:00-10:00hrs, 10:00hrs-11:00hrs and so on.

Column E would need a formula to show percentage of "on time" collections by the hour relevant to the times in column D.

Hope some one can point me in the right direction please.

how would we know if the collection time says 11:00 and does not say "on time"
what slot that should have been in to work out the % of all collections booked

for example
if they where due to collect between 9 and 10 and turned up at 11:00 that would NOT have on time
if we know it was due to be collected between 9 and 10 - we can then use %
Total ONTIME which time between 9 and 10 / total of Ontime and NOt ONTIME for the timeslot of 9 to 10
but from your list , unless the BOOKED is the time and you do not record the actual collection time

countifs( B1:B1000 , ">="&TIMEVALUE("09:00"), B1:B1000, "<="&TIMEVALUE("10:00"))

That will give the total number of booked times between 9 and 10 ( although you need to decide which time you want to include for =
then
countifs( B1:B1000 , ">="&TIMEVALUE("09:00"), B1:B1000, "<="&TIMEVALUE("10:00"),C1:c1000, "On Time")

Now you can just divide to get a %

Please upload a small clean sample of your data / workbook (not a picture) to the forum, Make sure you have removed any private information, remember this is a public forum and so available to anyone
We would like to see an example of your data and also a manual mock up of the expected results you want to achieve.

To attach a file to your post,
click "Go advanced" (next to quick post),
scroll down until you see "manage Attachments",
click that and select "Choose File" (top Left corner).
Find your file, click "Open" click "upload" click 'close windows" Top Right. click "Submit Reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

Thank you Wayne, exactly what I was looking for.

you are welcome