Pivot Table - Date grouped into Days

[ColinEssex]

Hi All

I've had a search but no joy - I wonder if anyone can help me please - its pivot tables.

I have a listing of records in Excel, one column is the "receivedDate". I can group / ungroup ok into months. The user needs to have the date in days 'monday', 'tuesday' etc. If you group a Pivot in "Days", it comes out in dd-mmm format.

Any clues please? I'd like the data to look like this in the pivot table -

Monday ........25
Tuesday ......40
Wednesday ...17
Thursday .....28
Friday .....30
Saturday .....16
Sunday .....37

Thanks

Col

 

[shades]

Can you set the custom format to "dddd"?
________
HONDA CB SERIES HISTORY

 

[DreamGenius]

This is an old, possibly dead, thread but here goes anyway.

You need to add a column to your data before you pivot it, containing the weekday name. You can do this using the following formula:

=vlookup(weekday([datecell],2),Sheet2!A:B,2,false)

where Sheet2 contains two columns:

1,Mon
2,Tue
3,Wed
4,Thu
5,Fri
6,Sat
7,Sun

You then pivot on your new column, not your original date column. Formatting the column to just show day names doesn't change the data behind it, hence you're not getting the grouping you want.