[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 250
  • Last Modified:

How to create a Pivot Table in Excel that is a matrix showing Day of the week matched with with Hour of the day

I am not sure if this can even be done but this is what I am trying to do.  I have a huge set of data where I am attempting to analyze day of the week and hour (24hr clock) of the day to determine if any patrterns exist (basically I am looking at creating a way to automate an intelligence matrix).  I need a table that has the day of the week on the left rows(where they are unique sun-sat with no repeats) and the time in the 24 hour clock on the top colums (again unique).  I have tried this and all it does is show 100s of records with repeating days of the week and hours (versus grouping them and totalling them).  I am attachinga spreadsheet file ...goal worksheet that shows what I am trying to do...with a probs worksheet that shows what I am getting.  If this is super difficulct with many steps i will break this into a two part que so that appropriate points are awarded.

Thanks
Groovymonkey
ugh.xls
0
groovymonkey
Asked:
groovymonkey
1 Solution
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

change column B to return a true text value instead of a date formatted to show only the text. Use in B2

=TEXT(A2,"ddd")

and copy down. Likewise, change column D to have only the hour value, not a time value that is formatted to show the hour. Use in D2 and copy down

=HOUR(C2)

Format column B with General format.

Now create a pivot table with Day in the rows and Hour in the columns, any field in the Values (set to count)

cheers, teylyn
0
 
groovymonkeyAuthor Commented:
Perfect solution!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now