« Back

Data Data Everywhere! #1 - Another Method to Take the 'T' out of Date/Time


Data Data Everywhere! #1: How to get the 'T' out!

You may have noticed that the time format when you retrieve data from the GLOBE web site recently changed. The new format is YYYY-MM-DDTHH:MM:SS.

 

We would like to show a quick and easy way to go back to the time format MM/DD/YYYY HH:MM:SS.

1) Select the column that has the time format you want to change.

2) Copy the column to a new location on your spreadsheet; you do not want to manipulate the original column directly, to avoid losing data.

3) Make sure there is a blank column next to the column that you just copied.

 

 

4) While your data is still selected, go to DataText to Columns and the Text Wizard will open. 

5) On Step 1 of 3 of the Text Wizard, keep the option “Delimited” selected and click Next.

 

 

5) On Step 2 of 3 of the Text Wizard, uncheck the option “Tab”, check the option “Other” and enter a capital T on the space next to “Other”.

6) On Step 3 of 3 of the Text Wizard, make sure that the data format for Date is “MDY” and click “Finish”.

 

 

Your data should now appear in two columns with the format MM/DD/YYYY HH:MM:SS.

 

Now add a column to the right of the HH:MM:SS column and enter an equal sign “=”. Then select the first cell of the M/D/YYYY column followed by a plus sign “+” and select the first cell of the HH:MM:SS column:

 

 

Once you hit <Enter>, the M/D/YYYY and the HH:MM:SS will appear together in your new column:

 

 

Tip: If after adding the M/D/YYYY and the HH:MM:SS columns on a third column, you don’t have the format M/D/YYYY HH:MM:SS, you can select the cell, go to Format > Format Cell… and type the format yourself: “m/d/yyyy h:mm:ss”.

 

Congratulations!!

You have successfully removed the 'T' from Date/Time and can now move on to the fun of analyzing the data!

 

Next
Comments
Please log-in to post comments