google-sheetsgoogle-sheets-formulabitwise-operatorsbit-shiftlargenumber

Is there any way to do a bitwise shift right on a very large number in Google Sheets?

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

Message ID Timestamp
287134013666099000 =BITRSHIFT(A2,22)+1420070400000

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

Error: Function BITSHIFT parameter 1 is too large. It should be less than 2^48.

Error: Function BITSHIFT parameter 1 is too large. It should be less than 2^48.

Any workaround?

Solution

  • 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.