5 ms·
Making a digital clock in Google Sheets
- hawktheslayer 6y agoThanks, 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.
- ed25519FUUU 6y agoSuper cool, even though it’s showing 33:40 at the moment :-)
- helsinkiandrew 6y agoAnd 44:43 to me an hour later (but in Helsinki time) perhaps a slight tweak is needed
- deleted 6y ago[deleted]
- MrsHippy 6y agoI got 66:06 while now() == 06:06
- walty 6y agoTo 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.
- 082349872349872 6y agoSo GSheets are turing complete, for all your 1 Hz computing needs?
- mattlondon 6y agoThey'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.
- NiceWayToDoIT 6y agoReally cool, but in my case it shows 77:27, has someone edited original?
- mattlondon 6y agoLikewise 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)
- lbatchelor 6y agoHa! 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/1T3bv8ThluHc18ghI8u9mrJKy_wB7ddxyFlBqGsqM74U/ https://docs.google.com/spreadsheets/d/1T3bv8ThluHc18ghI8u9m... Thank you for the inspiration!
- heinrichhartman 6y agoThanks 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!
- ss64 6y agoThe 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.
- amelius 6y agoCan't you get a better, higher resolution clock simply by setting the time in a cell and setting the font size very large?