Excel & Date/Time Conversion

[stlrain95]

I have a spreadsheet that I have pulled from an Access table. The Spreadsheet is hardcoded with a "General Date". Which is mm/dd/yy hh:mm:ss PM. I am trying to format this date to just mm/dd/yy, so that I can do a pivot table on Date.

Formatting this field does nothing for the pivot table, it still reads the time. I have also tried to use Replace() and Left() functions with no luck.

Any suggestions on how I can pull the time out??

Thanks,

 

[rdodge]

Actual data of the date still contains the date/time value. All you doing is changing the format of how it looks. If you need to change the actual values to just it's truncated values, try using an extendable column with a formula of:

DT = Date/Time Value

=INT(ABS(<DT>))*(<DT>/ABS(<DT>))

Bear in mind, it will still have a time value, but the time value will be 00:00:00.

In case you wondering why I mentioned truncated, it's cause the whole number represents the date, thus a -0 is the same date as a value of 0.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[Bowers74]

You can also change the number format for specific fiends in the Pivot Table. Set the number format for the Date field (in the Pivot Table) to mm/dd/yyyy, and then it should work.

;-)



Peace!!

Mike

Didn't get the answers that you wanted? Take a look at FAQ219-2884