# Formula to Turn Number into Date

✭✭

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?

Tags:

• ✭✭✭✭✭✭

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)

• ✭✭✭✭✭✭

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

• ✭✭✭✭✭✭

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?

• ✭✭

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

• ✭✭✭✭✭✭

Ok. Give this a try:

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

• ✭✭

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

• ✭✭✭✭✭✭

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)

• ✭✭

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?

• ✭✭✭✭✭✭

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

• ✭✭

Huge thank you! That worked.

• ✭✭✭✭✭✭

Happy to help. 👍️

• edited 03/18/24

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!