Hi,

I am pulling data from MS Forms into Excel and then creating a pivot table to analyse the data.

One of the key fields I need is the date that the form is completed.  This comes through from forms as a custom date field in the format m/d/yy h:mm:ss.  When I run a test to see if this is a 'proper' date in excel by converting the date to a 'general' format l I get a number like 45020.8650115741.  Which seems to me to be correct.

Outside of the pivot table I can convert this figure to a uk dd/mm/yyyy format, which is what I am after.  But if I create a pivot table using this field it is displayed in the pivot table as 04-Apr. Whilst this can be expanded to get the more details time information, (h:mm:ss)  all my attempts to reformat it to dd/mm/yyyy have failed.

Does anyone know if this is a limitation of custom fields?  Or is there a way to work around this that I am missing please?

I have attached a workbook with a 'data' tab and a 'pivot' tab.  Showing the example I am working with.

Many thanks,

Richard.