Converting a Text Timestamp into a Numeric Timestamp Value
Problem: When timestamps are imported into smartsheet, the timestamp must be imported as a text field to retain its time aspect. Although the timestamp can be separated into a Date field and a Time field, this separation of Date and Time makes arranging the sheet into timestamp chronological order difficult.
Solution: Converting the data into a numeric value would allow chronological sorting. The formula below in the ‘Julian Timestamp’ column converts Date and Time into a numeric value that can be used for chronological ranking, or finding the MIN, MAX, etc. Note that these numeric values cannot be used to directly determine durations between times since time is based on a 1/60th scale, not based in tenths or hundredths of the decimal scale.
Below is a screenshot of my sheet. Data is imported into this sheet every 15min from an external data source. That data source generates a timestamp. In my process, I need to be able to always chart the 10 most recent data points in any rolling 24hr period.
These are my formulas
Timestamp Date: (Note my dates are US formatted (MM,DD,YYYY)
=DATE(VALUE(LEFT(timestamp@row, 4)), VALUE(MID(timestamp@row, 6, 2)), VALUE(RIGHT(timestamp@row, 2)))
Timestamp Time:
=TIME(MID(id@row, 13, 5))
Information on the TIME function can be found here
Julian Timestamp:
=VALUE(RIGHT([Timestamp Date]@row, 2) + YEARDAY([Timestamp Date]@row) + "." + LEFT([Timestamp Time]@row, 2) + RIGHT([Timestamp Time]@row, 2))
The Right function is parsing the 2 digit Year number from the timestamp field. The YEARDAY function returns a number representing the day in the year, 1 through 365, where 1 is the first day of the year. In the Julian timestamp formula this represents the digits to the left of the decimal place. In the screenshot the formula output yields year 24, day number 52, or 2452. The formula adds the decimal, then enters the time value as a 4 digit decimal value, as shown in the green row time 17:12 is converted to decimal 1712. The VALUE function that envelopes the entire formula converts the textstring that visually looks like a number into an actual numeric value. Note: The column header is only named ‘Julian Timestamp’ and is not a true Julian date.
Finally, my use case needs to rank the timestamps so that the 10 most recent timestamps are always identifiable.
Order:
=RANKAVG([Julian Timestamp]@row, [Julian Timestamp]:[Julian Timestamp], 0)
