Formula to Turn Number into Date

JennaH

Hi Smartsheeters! We currently have another system feeding dates into Smartsheet in a number form. For instace, this number (1695686400000) is sent to Smartsheet represents 9/26/2023. How can we convert that number into a date value in Smartsheet?

Thank you for your help!

Paul Newcome

It worked for me (snippet below).

By chance does your number coming in have a hidden apostrophe at the start? If so, you may need to wrap your cell reference in a VALUE function.

=VALUE([UNIX Timestamp Column]@row) / 86400000 + DATE(1970, 1, 1)

Paul Newcome

Ok. Let's try this addition:

=IF([UNIX Timestamp Column]@row <> "", VALUE([UNIX Timestamp Column]@row) / 86400000 + DATE(1970, 1, 1))

Paul Newcome

There is no direct function for that, so would you be able to describe the logic as to how that specific number represents that specific date?

JennaH

Not entirely sure but it's a UNIX timestamp and it seems that it is the ISO 8601 format

Paul Newcome

Ok. Give this a try:

=[UNIX Timestamp Column]@row / 86400000 + DATE(1970, 1, 1)

JennaH

Thanks for the suggestion. Unfortunately, that led to a date in the year 70.

Here's some more info on this format: https://developers.hubspot.com/docs/api/faq#:~:text=UNIX%2Dformatted%20timestamps%20in%20milliseconds

Paul Newcome

It worked for me (snippet below).

By chance does your number coming in have a hidden apostrophe at the start? If so, you may need to wrap your cell reference in a VALUE function.

=VALUE([UNIX Timestamp Column]@row) / 86400000 + DATE(1970, 1, 1)

JennaH

Amazing that worked! The formula needs to be a column formula and some of the source columns will be missing data... When I try to convert the column to the formula, Smartsheet sends an error that says that the column formula syntax isn't quite right. Any idea what is going wrong?

Paul Newcome

Ok. Let's try this addition:

=IF([UNIX Timestamp Column]@row <> "", VALUE([UNIX Timestamp Column]@row) / 86400000 + DATE(1970, 1, 1))

JennaH

Huge thank you! That worked.

Paul Newcome

Happy to help. 👍️

StefanPize