Real dates in Pivot Table

[Don2415]

I have multiple years of hourly data and I average it to daily in the pivot table. The table has one line with the year followed by lines for each day-month in the year, then another line with the year followed by day-month, etc. I would like to have a day-month-year format. is that possible? (A newbie here)

 

[Dave Patton]

You can make a column with real dates.
N.B. The system that generated this example has regional settings of dd-mmm-yy
Format the dates to your preference.

T202112a.xlsm
	A	B	C	D
1		Text	Dates
2		2007	Option a	Option b
3		16-Mar	16-Mar-07	16-Mar-07
4		17-Mar	17-Mar-07	17-Mar-07
5		18-Mar	18-Mar-07	18-Mar-07
6		5-Apr	5-Apr-07	5-Apr-07
7		22-Apr	22-Apr-07	22-Apr-07
8
9
6a

Cell Formulas
Range	Formula
C3:C7	C3	=DATE($B$2,MONTH(RIGHT(B3,3)&1),MID(B3,1,FIND("-",B3)-1))
D3:D7	D3	=--(B3&"-"&$B$2)

 

[shift-del]

Hi Don

Ungroup the date field in the pivot table.

 

[Don2415]

Thanks!