CODEWITHSUNDEEP
excelexcel-formulamaxpivot-tabledatetime-format
AngieM
AngieM

Reputation: 745

Max of DateTime (custom) field not recognized in PivotTable

I have some fairly simple raw data where the format of column LastUsageTime is custom (yyyy-mm-dd hh:mm:ss):

Raw Data

And a PivotTable:

Pivot Table.

The LastUsageTime column is a MAX function (I simply want to display the dates, not count them). The problem is Excel doesn't understand the custom DateTime format of the raw data LastUsageTime for some reason. It puts 1900-01-00 everywhere. However, if I copy the text of LastUsageTime, paste it to NotePad, then Copy/Paste it back to Excel, it works fine!

However, the raw data is sourced from an external XML file, so when I click refresh, it gets messed up again.

How can I fix this?

Upvotes: 1

Views: 1546

Answers (1)

pnuts
pnuts

Reputation: 59485

I think you need to convert the raw data LastUsageTime from string to a date/time value (as Excel does for you by coercion with the copy/paste). Perhaps: 

 =DATEVALUE(E2)+TIMEVALUE(E2)

Upvotes: 1

Related Questions