Solved

Excel not converting Date/Time format properly from perfmon output

Posted on 2012-03-19
9
1,097 Views
Last Modified: 2012-03-19
Hi,

I had logging some performance stats on a server and for some strange reason on the output, the date/time format goes horribly wrong on the csv output as soon as the date/time reaches more than 03/12/2012  23:56:36. The next format that it goes in is 03/13/2012 00:01:35.683 and i am unable to change any of the settings.

Any idea why this is happening?! I want to get all the date/time values to a standard format. Example attached.
test.csv
0
Comment
Question by:ukznmcl
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
9 Comments
 
LVL 47

Expert Comment

by:Martin Liss
ID: 37739776
These are the two dates you're talking about, right?

03/12/2012 23:56
03/13/2012 00:01:35.683

Where do they come from?
Where do they go?
What do you want them to appear as when they get there?
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 37739803
If you format the column this way is it okay?

Date format
0
 
LVL 5

Author Comment

by:ukznmcl
ID: 37739812
This is the problem. If i format the colum as DD/MM/YY MM:SS it always shows up as 03/13/2012 00:01:35.683 rather than 03/13/2012 00:01.  No matter what i change in the date/format field it just stays the same.
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 5

Author Comment

by:ukznmcl
ID: 37739818
Where do they come from?  - perfmon CSV output running off a 2003 server
Where do they go? - output csv file
What do you want them to appear as when they get there? DD/MM/YYYY MM:SS
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 37739862
I used that format on the CSV file after importing it into Excel 2003 and it looks okay.

Did you actually use the standard format from the list or did you use a custom format?
0
 
LVL 5

Author Comment

by:ukznmcl
ID: 37739883
Strange!! I did a custom format. Here is that i see. It only affects rows 310 onwards. Here is what i see (Excel 2010). screenshot of issue
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 37739891
Does 2010 supply the same format I used? If so….
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 500 total points
ID: 37739910
The reason for that is that Excel is treating your dates as dd/mm/yy dates so 03/12 is OK but 03/13 is not because there's no month 13.

That also means your dates in the top 300 rows are incorrect (because it's converts 03/11 to November 3rd instead of March 11th etc)

You could try changing your regional settings to US temporarily before importing or try this formula in B2 to convert

=IF(ISNUMBER(A2),DATE(YEAR(A2),DAY(A2),MONTH(A2))+MOD(A2,1),REPLACE(A2,1,6,MID(A2,4,3)&LEFT(A2,3))+0)

copy that down column and format in required date format

regards, barry
0
 
LVL 5

Author Comment

by:ukznmcl
ID: 37739919
That worked a treat! Thanks Barry!!
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

On July 14th 2015, Windows Server 2003 will become End of Support, leaving hundreds of thousands of servers around the world that still run this 12 year old operating system vulnerable and potentially out of compliance in many organisations around t…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question