Excel Help - Calls Per Hour

[Ridealong]

Can anyone suggest the quickest way to produce/provide data for calls per hour, per day spreadsheet over a period of a week?
There will be 2 sets data (the date & the time of call).
Is it a simply =COUNTIFS for the calls per hour (9 to 5) and days (Mon to Fri) in a table, then a Pivot Table for the person to view afterwards?

[FunkyGibbon]

You could do it all in a pivot table by grouping the date/time by year/quarter/month/day/hour/

https://support.microsoft.com/en-us/office/group-o...

[Ridealong]

FunkyGibbon said:

You could do it all in a pivot table by grouping the date/time by year/quarter/month/day/hour/

https://support.microsoft.com/en-us/office/group-o...

Thanks

I come to the conclusion that there is not enough columns/fields to benefit from using a Pivot table.

[mmm-five]

Ridealong said:

Thanks

I come to the conclusion that there is not enough columns/fields to benefit from using a Pivot table.

You only need 2 columns to make it worthwhile, especially for the 30 seconds it takes to do it (if you are handy with pivottables of course)

[paulrockliffe]

Ridealong said:

Thanks

I come to the conclusion that there is not enough columns/fields to benefit from using a Pivot table.

There are almost no circumstances where a Pivot Table isn't the best way to display your data.

If you're using Excel 2016 onwards if you format your data as a Table, format your date/time column properly as Date/Time and then reference that Table in a Pivot Table it will automatically sort out the Date and Time hierarchy out for you, and the Pivot Table will automatically group your calls into Days, Hours, Minutes, whatever you need. Set the field to Count, though it should default to that anyway.

Where's the data coming from? If it's likely to continue being updated then get it dropped out of the source as .csv files into a folder, then use Power Query to slurp up everything in that folder - which will then include new data over time - load it into the Data Model and you then have the entire DAX language available to create whatever analysis you want. This may be over-kill, but it's the best way to deal with any data in Excel where you're analysing a static(ish) dataset.