Jul 02 2020 03:04 AM
I am trying to sort out a booking spreadsheet for our sports club with the following data:
name start time duration
name | start time | duration |
Alf | 14:00 | 02:00 |
Bert | 14:00 | 02:00 |
Chas | 14:30 | 01:00 |
Dave | 14:30 | 02:30 |
Evie | 16:00 | 02:00 |
Frank | 16:00 | 02:00 |
Gina | 16:30 | 03:00 |
Harriet | 16:30 | 03:00 |
The attached sheet shows the time chart I have created but I would like it to show the number of people booked in at any particular time.
Jul 02 2020 03:23 AM
SolutionI used a simple formula to calculate this:
=SUM((F1>B2:B9)*(F1<B2:B9+C2:C9))
Is this what you are looking for?
Jul 02 2020 03:44 AM
Thats great Antony. Is there any way this could be modified to show the #people booked in column L ie against the times - I could then chart number booked against time. Its all a covid thing that we have to limit numbers to under 30 and have a record of them. Thanks for your help it is much appreciated as it took me an age to get this far (technophobe)!!!!
Jul 02 2020 04:18 AM
Jul 02 2020 03:23 AM
SolutionI used a simple formula to calculate this:
=SUM((F1>B2:B9)*(F1<B2:B9+C2:C9))
Is this what you are looking for?