Forum Discussion
Sorting attendance data in a table
Hi,
I'd like to get some help with a tricky issue.
I have a worksheet with several thousands lines, where I've got a check-in and check-out date and time, in the format dd.mm.yyyy HH.mm.
I need to make a statistic out of it showing at which time and which weekdays on average there are more guests in house.
I've done something similar before, but it was easier as the data never went over midnight and into a new day, where's this dataset can span over several days, where I need to account for the guests, but I can't predict if it's going to be less than one or 3/4/5 days.
Thanks in advance for your help, it will be much appreciated.
5 Replies
You can calculate the average number of guests in a house without using a pivot table.
Here’s how you can do it:
- Add a column to your data that contains the day of the week for each check-in date.
- Create a table that lists all of the days of the week and their corresponding dates.
- Use the COUNTIFS function to count the number of guests that were in the house on each day of the week.
- Here’s an example formula:
=COUNTIFS(checkin_dates,"<="&date,checkout_dates,">="&date,day_of_week,"Monday")
This formula counts all rows where the check-in date is less than or equal to the date you want to calculate for, where the check-out date is greater than or equal to that date, and where the day of the week matches “Monday”.This gives you the number of guests that were in the house on each Monday.
- Repeat step 3 for each day of the week.
- Calculate the average number of guests for each day of the week.
To calculate this for each day of the week, you can use a pivot table too.
Add a column to your data that contains the day of the week for each check-in date.
Then create a pivot table with this column as rows and your calculated field as values.
This will give you an average number of guests for each day of the week.
I hope this helps!
- Thanks for the help,
it's not completely what I'm looking for, but it's in the right direction.
The issue is that I need to summarize how many guests there are on average for each 15 minutes of a day. The part I do not know how to deal with is the change of day.
For example if someoone checks in at 22:00 on day 1 I need to count them for the 2 last hours of day 1, and then continue counting them in the whole day 2 until check out at 04:00 in day 3.An example file (without sensitive data) with step by step (cell by cell) description or photos would help.
It would also help if the digital environment was known, such as Excel version, operating system, storage medium, Excel file extension, etc.
Please keep in mind that all users here offer their help on a free basis and do not always have the time to guess or guess what is meant.Please keep in mind that nobody here can see what you see on your monitor.
Here is some information about it:
Please include the following info to help others answer your question:
Welcome to your Excel discussion space!
Thank you for your patience and understanding
