Problem formatting dates in pivot chart.

**greg7468** · 02-07-2008, 07:20 PM

Hello, I am hoping someone can help with my problem.

I am trying to create a pivot chart with data supplied to me weekly.

The date format comes in custom dd/mm/yyyy/hh:mm:ss i.e 08/02/2008/11:12:22

I want to show the amount of pieces of data from each day.

I therefore only need the data to be in the dd/mm format. i.e 08/02

I have tried a helper column and formatted it to dd/mm.

The column looks as I want it too i.e 08/02

When I use the date on the X axis of my pivot chart I cannot stop it reverting back to the original format. So instead of having say a count for each particular day I am getting multiple entries for the same day.

Thanks for any help.

**steven1001** · 02-07-2008, 08:34 PM

try
=DATEVALUE(LEFT(A1,10))
where A1 contains your long string.

**steven1001** · 02-07-2008, 08:39 PM

"I therefore only need the data to be in the dd/mm format. i.e 08/02"
...and then use the group-by month function in the pivot table based on the proper date value.

or a simple fix is a helper column
=text(<date>,"yyyy-mm")
its not a date but it sorts ok and is easily recognisable as a year and month.

Problem formatting dates in pivot chart.

LinkBack

Thread Tools

Rate This Thread

Display

Problem formatting dates in pivot chart.

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions