Trying to convert a Discord snowflake (64-bit number) to a unix timestamp with Google Sheets.

Trying to convert a Discord snowflake (64-bit number) to a unix timestamp with Google Sheets.

Sheets, naturally, freak out because the number fed into BITRSHIFT is above 2^48.

Function BITRSHIFT parameter 1 value is 2.87134e+17. It should be less than 281474976710656.

Any workaround?

• google-sheets
• google-sheets-formula

• 1 Shifting right by 22 bit positions is the same as dividing by 2^22, so try replacing bitrshift with int(A2 / 2 ^ 22). – doubleunary Commented Jan 31 at 16:07
• 1 "Freaks out" is a vague description. Instead describe what is the expected output and what ylis the actual output. Quote the exact text of the error message. – TheMaster Commented Jan 31 at 16:11
• 1 22 bit positions is the same as dividing by 2^22 I believe this is only true given that the bits to be removed are all zeroes (which is insignificant for very large numbers due to rounding off). But this logic (if my assumption is correct) does not apply for small numbers especially when removing bits of 1. – PatrickdC Commented Jan 31 at 16:14
• 2 @PatrickdC note the int(). Right shifting discards LSBs, so it doesn't matter whether the rightmost bits are zeroes or ones. Are you thinking of circular shift? – doubleunary Commented Jan 31 at 16:50
• @doubleunary my bad, i missed the int() part. It does round off the decimal part when dividing by 2^n. i.e. 10001 in decimal is 17. Applying it with BITRSHIFT(17,2) becomes 100 which is 4 in decimal. 17/2^2 is 4.25. But with the help of int(), it will round down to 4. – PatrickdC Commented Jan 31 at 17:30

2 Answers 2

Use division by a power a two, like this:

=int(A2 / 2 ^ 22) + 1420070400000

See Arithmetic shift.

To convert a Unix timestamp to a human-readable Google Sheets datetime, use epochtodate(). To get millisecond precision, format the result as Format > Number > Custom number format > yyyy-MM-dd hh:mm:ss.000, which gives:

2017-03-03 08:08:03.550

For additional background, see Working with date and time values in Google Sheets.

287134013666099000 in binary is (according to this website)

1111111100000110101101110100110111011111111111111100111000

If we discard the rightmost 22 bits we get

111111110000011010110111010011011101

and converting back to decimal we get

68458083549

=int(A2 / 2 ^ 22) gives

68458083550

whereas this (which takes the original number as a string)

function myFunction(s) {
  const bigNumber = BigInt(s);
  //console.log(bigNumber.toString()); 
  //console.log((bigNumber>>BigInt(22)).toString());
  return (bigNumber>>BigInt(22)).toString();
}

gives

68458083549