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
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