Like so?
See attached.

Thanks for your reply Riny.

That’s close (and closer than I’ve been able to get!) but is it possible to get the rows to display in dd/mm/YYYY format?  I want the auditor to be able to copy and paste the date in this format into a form.  Cell format seems to have no effect when I tried it.

All the best,

Richard.

Hi Richard,

Dates in the row/column labels cannot be formatted as you want. However, if you also put the Completion Time field into the Vaues area and set the aggregation to max, then set the format to number format dd/mm/yyyy, you'll have what you want, BUT you must also have the Completion Time field in the row labels.

Mynda 

If its not possible to getting the dd/mm/YYYY format I can live with the date of the entry in day month form - which you have got but I haven't. 

The only difference I can see between your pivot and mine is that you have 'months' in your row field which Excel hasn't expanded out in my pivot.  Do you think that could be the cause of me not getting the individual dates?

Thanks Mynda!  - I think I'm close your suggestion got me the 'highest' date for each month  in the values column so now I think I 'just' need to work out why my months aren't expanding to give all the instances of an entry within a month.  

It's because of this:

"...BUT you must also have the Completion Time field in the row labels."

i.e. the Completion Time field must be in the row labels AND the values fields.

Mynda

So I think I do have the completion field in the row labels and the value fields if I understand you correctly.  (see attached)  Where my pivot seems to differ from Riny's is that Excel hasn't expanded out the months to appear in the row labels field.  Perhaps that is what is preventing all the dates from appearing? Is there something I need to do to achieve this? 

Many thanks,

Richard.

Perhaps like in the attached. I removed the automatic grouping and then manually grouped the Completion Time in Years, Qtr, Mth. That kept the individual dates for each month in the row headers. Then I custom formatted these as ;;; to hide them from view.

Not sure if this is the best or prettiest way of achieving it, but it seems to do what you want.

Hi Riny,

Bizzarely I have just found the same solution.  I think it was due to my lack of understanding of grouping.  When I saw month wasn’t appearing I opened the group dialog and saw that year, quarter and month were selected automatically.  But not day.  I had assumed that day was the ‘completion time’ in my row field was the equivalent of ‘day’.   Anyway like you I deselected and manually selected year, quarter, month and day.  Months then appeared in the row field and the dates appeared on my pivot table!  Success! With Mynda’s solution for the date formats I now have exactly what I was looking for.

Thank you both!