Complicated problem!
Thanks to Mr. Bill Jelen for authoring the extraordinary book; “PowerPivot for the Data Analyst”. However, I remain stumped on a difficult problem; Any assistance is greatly appreciated!!!
The problem would be similar to restaurant occupancy. I need to calculate how many people are in the restaurant at each 15 minute interval, 24 hours a day, 7 days a week and 365 a year. From a report, I am able to see each individuals (stamped with customer #) date and time in, along with the date and time out for every customer. As an example, customer A arrives on 03/29/2017, 11:44pm and leaves on 03/30/2017 at 12:31am. He spent 47 minutes and was present at 4 different time segments; 11:45pm, midnight, 12:15am and 12:30am. If customer B was also present from 03/30/2017, 12:01am to 03/30/2017, 12:29am, then I need to show 1 customer at 12:00am, 2 customers at 12:15am and back to 1 customer at 12:30am. I need to analyze by the table number they sat at. I would like to show the results on an excel sheet with 15 minutes time segments as rows and table numbers as columns. E.g. I should be able to know how many people were seated at table #4 at 15:45, and separately at table #7 at the same time; and all subsequent 15 minute time segments throughout the day.
To compound the problem, anything up to 3:59am is counted as occurring on the previous date, then the cash register is changed and begins a new date at 4:00am.
I am using Excel 2010 with PowerPivot add-on, 32 bit installation.
Thank you in advance!
Thanks to Mr. Bill Jelen for authoring the extraordinary book; “PowerPivot for the Data Analyst”. However, I remain stumped on a difficult problem; Any assistance is greatly appreciated!!!
The problem would be similar to restaurant occupancy. I need to calculate how many people are in the restaurant at each 15 minute interval, 24 hours a day, 7 days a week and 365 a year. From a report, I am able to see each individuals (stamped with customer #) date and time in, along with the date and time out for every customer. As an example, customer A arrives on 03/29/2017, 11:44pm and leaves on 03/30/2017 at 12:31am. He spent 47 minutes and was present at 4 different time segments; 11:45pm, midnight, 12:15am and 12:30am. If customer B was also present from 03/30/2017, 12:01am to 03/30/2017, 12:29am, then I need to show 1 customer at 12:00am, 2 customers at 12:15am and back to 1 customer at 12:30am. I need to analyze by the table number they sat at. I would like to show the results on an excel sheet with 15 minutes time segments as rows and table numbers as columns. E.g. I should be able to know how many people were seated at table #4 at 15:45, and separately at table #7 at the same time; and all subsequent 15 minute time segments throughout the day.
To compound the problem, anything up to 3:59am is counted as occurring on the previous date, then the cash register is changed and begins a new date at 4:00am.
I am using Excel 2010 with PowerPivot add-on, 32 bit installation.
Thank you in advance!