I have data exported from a defect management tool (that reports all the defects that have been recorded in the defect
management system). This data has unique columns for name of originator, defect ID, date originated, priority, etc.
The date originated column has the date and time. I want to create a report that tells me how many High, Medium and Low
priority defects were created on each date - and I am using a pivot table to do this.
However, because the date
column is actually the date and time the defect was reported/recorded, the time element of this is making all the dates
unique eg, I have three defects recorded on 29/01/2009, a High priority defect (reported at 29/01/2009 12:23) and two Medium
priority defects (reported at 29/01/2009 13:02 and 29/01/2009 13:32). My pivot table is showing each date as a unique date -
with a count of 1 against each, whereas I want to see 1 date entry (for 29/01/2009) with a count of 1 against High and 2
I have tried custom formatting the date column to dd/mm/yyyy - which appears to work in the data
(but doesn't in the pivot table, because the time stamp is still there, just not visible).
I have tried copy and
paste special [Values] (on the custom formatted date column) - but the time stamp is still there.
I have tried
formatting the date column to "General", but this changes the dates to the number format - eg 29/01/2009 12:23
I have tried inserting a new column and using the =Left function for the first 10
characters of the cell (ie the 29/01/2009), but that gives the first 10 characters of the number form of the date, ie
Basically, without going into every date cell (and I have thousands) and manually deleting all the
times, how do I quickly change the date column into just having the date in it (ie how do I strip off the time element)?