I love Unix, and more importantly, the humble (and soon to be outdated) Unix Timestamp. The Unix timestamp (or Unix Epoch) is the number of seconds that have elapsed since January 1, 1970 (midnight UTC/GMT), not counting leap seconds. Most computer systems store epoch dates as a signed 32-bit integer, which will cause problems on January 19, 2038 (known as the Year 2038 problem - I've got 14 years to figure out a replacement!). Unix timestamps can be in seconds (10-digit), milliseconds (13-digit) and microseconds (16-digit) based on how accurate your clock and timekeeping needs are.

Why I like using Unix Timestamps

I'm no Amazon, so my small business can maybe hope for a few orders a day at most. In order to quickly classify and sort incoming orders, I add a key to my spreadsheet database, which I use to make all updates to and from it. In this case, using the unix timestamp as key is perfect:

1. it sorts in chronological order
2. it's granular enough to accept up to 86400 unique orders a day
3. it's long enough to be not easily remembered (10 digits!)
4. it's short enough that you could read it to someone over the phone if you had to
5. the actual data is innocuous enough to be worthless outside my organization
6. and has no customer info attached to it (initials, phone numbers) for hacker security
7. it might be recognized by another Unix geek, but to what end?

Since every order has a date (and time to the second) associated with it's capture time, and I use that unique date/time to turn it into a Unix Timestamp, and that becomes the official 'Order Number' that I use to refer to that job. I expect theres a infinitesimal chance of getting two online orders at the same exact second, but I seriously doubt it.

Doing It Under The (Google) Sheets

Before, I was using a Date Converter on my (paid-for) Automating Service (Zapier) that easily converted any date to Unix Timestamp, and then just inserted it when I add the new row to the database. Now, I wanna use it in my lowbrow CRM made out of a pair of Google Sheets and enough App Scripts to scare you. After some struggle to find an accurate (and complete) answer anywhere, and after some tweaking and experimentation, I came up with the Google Sheet formula for converting a field with a normal date/time into a Unix Timestamp:

=($N1-DATE(1970,1,1))*86400+(-5/24)

• The Dollar Sign ($) before the column letter means that when you copy the formula to another row, it won't increment when pasted. You can add it to the row number to achieve the same goal ($N$1).
• Field N1 has the sample date format 8/2/2024 16:25:09
• 1970,1,1 is Unix Epoch (Midnight Jan 1, 1970), so start here!
• 86400 is seconds in a day
• (-5/24) is my GMT Offset, in my case EST is -5 hours from GMT.

How to convert Unix Timestamp to Regular Date?

I hear ya. I can't tell what year is what year, either. Google Sheets has already figured this out, as they have a built-in function to convert Unix Timestamps back to plain Dates. Get our your copy-and-paste again:

=EPOCHTODATE($B1,1)

• Field B1 has the unix date value 1722615909
• The second variable (,1) signifies Seconds, you can also get millisecond (,2), or microseconds (,3).

I'm working a lot in Google Sheets and using the free SpreadAPI to make my own automated CRM. For now, it handles incoming orders and automated outbound emails. I'll write more as it comes along, stay tuned and subscribe to our RSS feed!