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

PeggyLang

@Paul Newcome

I am using a power automate to;

- take excel file attached to email and file on Sharepoint

- dynamically create a table in the new excel file on sharepoint

- list rows present in the table

- and finally Insert row in my Smartsheet.

When the row from excel (screenshot below) comes into Smartsheet Date Time is '45796.78253'.

My Smartsheet actually needs to reflect the Date that number represents. In the specific above example it would be 05/19/25.

I have located a 'Date-time converter' on the internet and plugged in '45796.78253'. It seems the 'RFC 7231' format translates to the correct date and time.

The formula you provided returns 01/01/70.

So . . . what I did was take the Excel Date/Time (45796.78253) * 38161641.8019, divided the sum by 86400000 and added DATE(1970, 1, 1).

I don't understand it, but it works.

Can you explain it? Was there an easier way?