Mar 30 2023 01:56 AM
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.
Mar 30 2023 02:32 AM
You can calculate the average number of guests in a house without using a pivot table.
Here’s how you can do it:
=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.
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!
Mar 30 2023 03:06 AM
Mar 30 2023 03:26 AM
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
Mar 30 2023 03:40 AM
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 way
Mar 30 2023 04:17 AM
You 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!