Converting Perfmon timestamps to a readable format in Excel


I have been using Microsoft’s Performance Monitor (perfmon) to capture data that hopefully allow me to understand the I/O bottleneck my Windows 2003 R2 server is experiencing only occasionally.

I’ve saved this data as a .csv file, and import it into Excel so I can analyze the raw data.  The problem is, the timestamp column is in some indecipherable format that doesn’t map to actual time.
 
I did some playing around with the cell formating in Excel, and this is how I was able to get the Perfmon timestamp displayed in a usable format. I’m using Excel 2002 in this example, so you may need to adapt the instructions to your environment. 
 
1.  Import the Perfmon .csv file into Excel
 
2.  Right click the timestamp column, which is column A for me
 
3.  Select Format Cells
 
4.  On the Number tab, select Custom Category
 
5.  In the Type box, enter the following:
   
     MM/dd/yyyy hh:mm:ss
 
Note there is a space between the yyyy and hh
 
Press OK, and your time stamp column should now be in a human readable format!

4 Responses to “Converting Perfmon timestamps to a readable format in Excel”

  1. Dom Says:

    Great – worked a treat – thank you

    • Scott Myers Says:

      Take a look at SmartMon (www.perfmonanalysis.com). It might be more effective at analyzing the Perfmon data than Excel.

  2. Carl Herder Says:

    My problem with Perfmon timestamps in Excel is not human readability. For some reason, when you use them as the X-axis in a graph, Excell doesn’t read them correctly. It seems to see only a small number of separate values, and chooses one Y value for each. This results is a line graph that looks like several telephone poles or a widely spaced picket fence.

    Anyone else seen this? Any ideas?

  3. Sysadmin Therapy - Making perfmon date/time column readable Says:

    [...] from: http://thebackroomtech.com/2009/08/06/converting-perfmon-timestamps-to-a-readable-format-in-excel/   1.  Import the Perfmon .csv file into Excel   2.  Right click the [...]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 32 other followers

%d bloggers like this: