How to use ProtecTIER long term stats and maintain USA date format

Technote (troubleshooting)


Problem(Abstract)

When opening the Comma Seperated Value output from long term stats, Microsoft Excel incorrectly reads USA date format, and will make Dates with DAYS larger then 12 as TEXT. This is one way to convert that date to USA formatted date.

Symptom

Time stamp field in Excel is not logical.


Cause

The Report output has the date like this:

05-07-2011 10:30:00
or
19-07-2011 14:15:00

In some Languages, this means DD-MM-YYYY, but when Excel opens the file, it wants to use format MM-DD-YYYY, and when you go to "format" cells, you will be able to change how its displayed, BUT you cant manipulate it as a "Date" or, the value is actually wrong.

Also, numbers that are illogical in MM-DD-YYYY format, - example: 22-06-2011, will just be "text" format in Excel.

This makes "processing" the field as DATE values very difficult. But for these Statistics, DATE is probably the most important field.

Example of the Problem:

At first , it incorrectly makes the Value December 6th 2010
Then on June 13th, it changes it to a TEXT value - loosing all DATE functionality

Environment

Microsoft Windows - Excel

Resolving the problem

The steps to convert first to "text" then back to "date " are as follow.

1.) To Prevent Excel from loading some cells as dates, create a NEW blank spreadsheet.

2.) Use "Data" to read in the "text" file, which lets you control how the .csv is imported

3.) Using the Wizard, Import the date as TEXT format

4.) Use Text to columns to split your text field up by "-" and " "

5.) Insert 2 new rows after the above are split

6.) Add Headers to the split and new roles like : Day Month Year Time USA Date USA Date-Time

7.) Put this formula in the USA Date column : =DATE(E3,D3,C3)

where E3 is the YEAR, D3 is the Month and C3 is the Day.

The cell should look like this now:

8.) Put this formula in the USA Date Time column =G3+F3

where G3 is the USA Date and F3 is the Time field.

The cell should look like this now: (you may need to use "Format Cells" on that new column, but the "value now should be a consistant date, so you can display it however you like)

9.) Copy those 2 formulas to all cells in those columns.

Now, when you plot charts or pivot tables, you can use the Date in the "USA Date-Time" column

-------------------------------------------------------

Step by Step Screen shots:

1.)

(new Blank Spreadsheet)

2.)

3.)

4.)

Now select your column and click

5&6 .)

(notice how we have C-H fields now for our date)

7 )

Use DATE() function to assign which column is Year/Month/Day

8.)

Add the TIME column to the "date" column - and Format the Cell for the Date presentation you like.

9.)

Copy to ALL cells in those columns.

Note: If you see that your long term stats are saving in a different format then this technote, then you may not need this tip/trick, OR, you may have to make some adjustments.

(some versions may have different format for the date)

Cross reference information
Segment Product Component Platform Version Edition
Tape Storage TS7650 with ProtecTIER Open Systems 2.4, 2.5, 3.1 Enterprise, N/A
Tape Storage TS7610 ProtecTIER Appliance Open Systems 2.5, 3.1 N/A, Standard
Tape Storage TS7680 ProtecTIER Deduplication Gateway for System z Linux 1.2.0.0, 3.1 Enterprise, N/A

Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

TS7650G with ProtecTIER

Version:

2.3.3.0, 2.4, 2.5, 3.1

Operating system(s):

Open Systems

Software edition:

Enterprise

Reference #:

S1003851

Modified date:

2011-07-07

Translate my page

Machine Translation

Content navigation