Extract timestamp from MongoDB ObjectID using Google Sheets

Other Questions Google Sheets
1

I have a Google Sheets document with MongoDB ObjectIDs and need to convert them to readable dates. Here are some sample IDs I’m working with:

507f1f77bcf86cd799439011
5a934e000102030405000001
5f5e100b8cb6ce77e8a00001

I found some information about extracting timestamps from MongoDB identifiers, so I tried different approaches using Google Sheets functions like DECIMAL, INT, and BASE. The DECIMAL function seemed most promising.

I know the timestamp is stored in the first 8 characters, so I extracted those manually. For example, from 507f1f77bcf86cd799439011, I took 507f1f77. When I convert this using DECIMAL function and set the cell format to date, I get a numeric value like 1350415223 instead of a proper date.

Is there a way to properly convert these MongoDB ObjectID timestamps to actual dates in Google Sheets? What’s the correct formula or process to make this work?

2

Had this exact same problem migrating MongoDB data to Google Sheets for reporting. Timezone handling got me at first - ObjectID timestamps are always UTC, so you’ll see dates that look off by hours depending on your timezone. After using the HEX2DEC formula, I added timezone correction: =(HEX2DEC(LEFT(A1,8))/86400)+DATE(1970,1,1)+(5/24) for EST. Heads up - this conversion kills performance with large datasets since it’s doing hex conversion on every row. Process in batches or use Google Apps Script if you’re dealing with thousands of ObjectIDs.

3

You’re on the right track with extracting those first 8 characters and using DECIMAL. Here’s the catch - MongoDB ObjectIDs store timestamps as seconds since Unix epoch, but Google Sheets wants days since January 1, 1900. After you convert your hex to decimal with =HEX2DEC(LEFT(A1,8)), divide by 86400 to get days, then add the Unix epoch start date. Your complete formula: =(HEX2DEC(LEFT(A1,8))/86400)+DATE(1970,1,1). I’ve done this tons of times when pulling MongoDB data into spreadsheets. Just make sure your ObjectID cell is clean - no extra spaces or weird characters that’ll break the HEX2DEC function.

4

that’s a unix timestamp in seconds! try this: =(INDIRECT("A1")/86400)+DATE(1970,1,1) where A1 has your decimal. the 86400 converts seconds to days and adds to the unix epoch date. worked for me with mongodb IDs last month.