Now that you have downloaded your data it is time to make some sense of it. The first step after loading  the file into your spreadsheet is to convert the unfamiliar Unix timestamp, which encodes the date, time, and day of each record in the series, into a recognizable format that you can use. This tutorial will help you to accomplish this task. I use the LibreOffice Calc spreadsheet program which is similar to Excel and I think these instructions will work with most spreadsheets similar to Excel.

To convert the Unix timestamp to a recognizable date, time, day format follow the steps below  the spreadsheet example and refer to the example as needed. Columns A through E contain original Speck data and the data in G and H columns in the example  are the results of Unix timestamp conversions according to the desired context. Information and labels for I, J, and K were also added. The data may be in different columns/rows in your spreadsheet and you can situate the conversions in any column(s)/rows  you like so be sure to change the  designations in the given formulas (below)  as needed to reflect the appropriate locations in your spreadsheet.

Locate the row and column of the timestamp for the first data sample which is probably A2 by default, as seen in the spreadsheet above, unless you have changed it.   Now select the column in which you would like the date and time to show – in the example it is G. You can enter the header now if you want one i.e. Date and Time. Then click the cell in G that is across from the first timestamp to be converted in column A. In the example A2 is the where the first timestamp is stored so G2 would be the cell to store the conversion product in this case. Format the cell for the date and time option you want and make sure the column is wide enough for the chosen format.

In order to get the correct date and time for your time zone there is one variable, –n, in the formula in red below, that you need to determine…this is the difference in the number of hours between your time zone and Greenwich Mean Time either for standard time or daylight savings time depending on which was in use at the time of the sample.  For example, -n would be -6 for CST (Central Standard Time) and -5 for CDT (Central Daylight Time) or -8 for PST (Pacific Standard time) and -7 for PDT (Pacific Daylight Time) etc.  Because the samples were recorded in May in the Central time zone -n is equal to -5. Daylight Savings Time in different time zones  is generally used from the second Sunday in March to the second Sunday in November. You can look up values you need for your state here (pdf version) or here  (web version).

Now that you know your value for -n enter the conversion formula  =(A2/86400)+25569+(-n/24)  in the Formula bar replacing -n with the numeric value and hit Enter; the result will appear in the designated cell. Copy this cell, then highlight all of the cells below where you want the date and time that corresponds with the timestamp in the respective cell in column A and then paste to them. All of the cells into which you entered the conversion formula should now display the selected date and time format for each associated timestamp.

Next, click in the cell of the column and row you would like the first corresponding day of the week to appear; in the example it is H2. Because the cells are already formatted to include text by default they do not need to be reformatted so just click on the cell to start in (i.e H2) and then click in the Formula bar and enter  =Text(G2,”dddd”) and hit enter and the day of the week should appear in  the chosen cell of the column. Now as you did for the date and time, copy the converted cell and then highlight all cells below that you want to include and paste the formula into them. The day of week will appear in all selected cells  for which there is corresponding  date and time data  in the same row in column G. If you prefer the abbreviated form of the name of day i.e. Fri instead of Friday just enter 3 d’s in the formula above instead of 4.

You can use this calculator to convert from a Unix timestamp to a date and time for your time zone or vice versa so you can confirm that your spreadsheet is providing accurate results. After entering the Speck data Unix timestamp in the conversion window be sure to change the UTC (GMT +0:00) window below the timestamp window to the correct zone time for your area (or Speck data area if it is different) before converting; just click  and pick a city that is in your zone to change it.

Also, it is  a good idea to add labels to your spreadsheet, as needed, to specify more about where and under what circumstances certain data was recorded or whether any suspected or known adverse reactions may have occurred. And, it may be helpful to keep a tablet and pencil at hand to  log  fine particulate levels of interest along with date, time, place and any other pertinent information so you can label your data later when you download it.  There are many other ways you can organize and manipulate your data in your spreadsheet to better visualize patterns and other informative features in the data that might otherwise be missed or less obvious. Personalize your spreadsheet to meet your needs for the best results.

You might want to consider making a template spreadsheet so that when you load your Speck data the date, time, and day conversions all occur automatically.

If you have any questions, comments, or suggestions let me know.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s