Ha! That is so cool. I was super interested in the idea so tried doing it myself without looking at your solution and came up with a quite an elegant solution using only 8 formulas and some named ranges (could be 4 if you inlined them more but I kept them separate for clarity).

https://docs.google.com/spreadsheets/d/1T3bv8ThluHc18ghI8u9m...

Thank you for the inspiration!

Thanks for sharing. Really surprised how straight forward your solution is!

Learned about REGEXEXTRACT and parametrized Strings.

Powerfull stuff.

As my job involves more and more Google Sheets Engineering lately. I really appreciate this kind of hacks!

Thanks, I really enjoyed this. It brings me back to my Digital Logic Design class where we needed to construct the truth tables for a digital clock for our final exam.

Super cool, even though it’s showing 33:40 at the moment :-)

To fix it, you can change cell U1 from

  =iferror(regexextract(text(HOUR(A1),"##"),"^\d"),"0")

to

  =iferror(regexextract(text(HOUR(A1),"00"),"^\d"),"0")

The ## text format uses only as many digits as needed, while the 00 format pads to 0 digits.

And 44:43 to me an hour later (but in Helsinki time) perhaps a slight tweak is needed

I got 66:06 while now() == 06:06

The time is now 99:51 What I learned from this is that formatting date/time as text will lead to all kinds of edge case bugs.

Really cool, but in my case it shows 77:27, has someone edited original?

Likewise 77:15 here (local 08:45 GMT 26th Dec)

I wonder if this could be some sort of timezone/locale issue screwing up his regexes ... Although there is no obvious 7 in my current time)

So GSheets are turing complete, for all your 1 Hz computing needs?

They've always (?) had a script IDE you can use to create your own functions for use in cells so I guess it has always been Turing complete.

I've used the script IDE a lot where it just made sense to do something in about 10 lines/a for-loop of normal procedural code rather than an unreadable chain of about 50 spreadsheet formulas in one cells.