Converting a Text Timestamp into a Numeric Timestamp Value

Kelly Moore

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)

Peggy Parchert

@Kelly Moore

Thank you for posting this solution. Very helpful for a new project I just started.

I do have a question. Is there a way to rank the order? My sheet is setup very similar to yours except that in my Order column, I want to show who submitted the form 1st, 2nd, 3rd, etc. up to 126 entries. Then I want the count to stop. I do have an auto-number column that I thought I could work into a formula but the column doesn't start over for every project - it just keeps going. For example, my 1st entry for Project XYZ could be 211. Is something like that possible via formula?

Thanks Peggy

Kelly Moore

Hey @Peggy Parchert

I'm not sure I follow what you want to rank. Are there multiple sheets where the ranges exist? If yes, I wondered if a JOIN across all the would work. If you only wanted the list, you could perhaps pull the sheets into a report and sort.

Without seeing data, it's hard to brainstorm

Kelly