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 Data> Text 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!
