When exporting views with date/time stamp fields, you may notice a string of numbers as a value in the field in Excel. ShotFlow uses a UTC timestamp which is the most logical format as the time zone must be interpreted by the browser or whatever is interpreting the data.
If you'd like to convert the UTC timestamp to a more human readable format in Excel, please follow the steps below:
Unix time is the number of seconds since January 1, 1970.
*Excel doesn't contain built-in functions for working with Unix dates so they must be derived.
*Excel allows you to add a number of days to a date by using the "+" operator. Let's make use of that.
First convert the number of seconds to number of days (by dividing by 60*60*24) and then add the result to the date "1/1/1970".
The formula will look like
=CELL/(60*60*24)+"1/1/1970"
Where cell is the location of the UNIX timestamp (for example, A1 or D3)
The quotes around the date are required. If they are not present, Excel will treat 1/1/1970 as an expression.
*Change the format of the calculated cells to a Date/Time stamp in the format you desire.
To convert Shot Duration:
- Use the formula (Shot Duration cell)/36400 (e.g. "=R2/36400")
- Format the cell for mm:ss
