• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1197
  • Last Modified:

Excel not converting Date/Time format properly from perfmon output

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
ukznmcl
Asked:
ukznmcl
  • 4
  • 4
1 Solution
 
Martin LissOlder than dirtCommented:
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
 
Martin LissOlder than dirtCommented:
If you format the column this way is it okay?

Date format
0
 
ukznmclAuthor Commented:
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
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
ukznmclAuthor Commented:
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
 
Martin LissOlder than dirtCommented:
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
 
ukznmclAuthor Commented:
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
 
Martin LissOlder than dirtCommented:
Does 2010 supply the same format I used? If so….
0
 
barry houdiniCommented:
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
 
ukznmclAuthor Commented:
That worked a treat! Thanks Barry!!
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now