Epoch date without time in Pivot

[Orange_Guy]

I have an Excel spread sheet (Excel 2002, Windows XP) and have been able to convert epoch to human date/time. When I format the column only the date will appear, which is what I want. When I create a pivot the human date appears in the Date column but both the date and time appear in the cell. The pivot table treats the Date column as a date/time entry.

How do I get the pivot to treat the Date column as date only and not date/time? I have the same issue when I cut and paste this data to another worksheet.

thanks

 

[Jerry Sullivan]

Welcome to the Board!

If you just want the value in the PivotTable to display as a Date instead of Date/Time, you can format the column just as you did for your source data.

More likely you are wanting to "remove" the Time from the PivotTable values.

Excel stores Dates as real numbers in which 1= 1 day and fractions of 1 = fractions of a day. So you can't really remove the Time from the value, but you can set all the values to whole numbers which sets all Date/Time values to Midnight.

To do this, add a column to your source data that truncates the decimal values from the Date/Time. All records that share the same Date will in essence, be set to Midnight of that date.

If Cell A2 has a Date/Time value, then in Cell B2 enter...
=TRUNC(A2)
and copy down.

 

[Orange_Guy]

Jerry,
Yes I wanted to remove the time from the cell.

Thanks! your suggestion worked great and did just want I wanted.

I appreciate the help.

Bill