0

I am working with a sheet having mongo IDs like:

621624b65241f6bddcaed7ad
63f72a1256d9a7b339d80a38
638b29fa7498254fca1d4e3c

I want to get date from these. after reading from Getting timestamp from mongodb id, I have tried using Decimal, INT and Base functions from which Decimal was the only one that made some sense to me in the end.

Since only 1st 8 characters need to be considered, I have manually picked the first 8 by deleting the rest, and set the format in the sheet to date. It does not really work! I am getting a number value that I do not understand.

To illustrate, from the first ID, I have picked 621624b6. The result that I get is 1645618358.

Can someone please help me do this if possible to do in Google Sheets?

Daniel
  • 10,641
  • 12
  • 47
  • 85
Asad
  • 31
  • 4

1 Answers1

0

The number you get when converting the base16 number 621624b6 to decimal (1645618358) is a UNIX epoch timestamp

Unix time is a date and time representation widely used in computing. It measures time by the number of seconds that have elapsed since 00:00:00 UTC on 1 January 1970, the beginning of the Unix epoch...

To convert UNIX Epoch timestamps to a date in Google Sheets (given the timestamp is in cell A2) we can use the formula

=TO_DATE(A2/86400+DATE(1970,1,1))
Daniel
  • 10,641
  • 12
  • 47
  • 85