Forum Discussion
Sorting attendance data in a table
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!
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
Sure, I know everything is based on good will here, and I appreciate the time some of you expert is willing to use.
Imagine something as follows, in column B and C there's an endless list of dates and hours.
Every one of this is an instance of 1 person that needs to be registered in a table a bit like the one from H4:CT11.
So for example B13:C13 should give a +1 for every 15 minutes between In and Out on day 7. This is the easy part to make happen.
But B5:C5 need to give a +1 for the hours both in day 7 and in day 1. And so on.
It could easily just sum up all the occurrences in the table H4:CT11 as I can use countif to establish how many weeks are involved and thus make an average afterwards (this data cover severals years unfortunately, so there's a lot to process).
I'm working with Excel 19 for Windows by the wayYou can use the COUNTIFS function to count cells between two dates and times.
Here’s an example formula that counts cells between 2 dates and times in columns B and C:
=COUNTIFS(B:B,">="&DATE(2023,3,1)+TIME(0,0,0),B:B,"<"&DATE(2023,3,8)+TIME(0,0,0),C:C,">="&TIME(7,0,0),C:C,"<"&TIME(19,0,0))
This formula counts cells between March 1st 2023 at 00:00:00 and March 7th 2023 at 23:59:59 that have a time value between 7:00:00 AM and 7:00:00 PM.
You can adjust the dates and times to fit your needs.
If you want to count cells between two dates only (without considering hours), you can use this formula:
=COUNTIFS(B:B,">="&DATE(2023,3,1),B:B,"<"&DATE(2023,3,8))
This formula counts cells between March 1st 2023 at 00:00:00 and March 7th 2023 at 23:59:59. You can adjust the dates to fit your needs
I hope this helps!