google-sheetstimegoogle-sheets-formulaarray-formulasdigital

digital TIX clock in Google Sheets


a quick search brings zero results so I wonder if this could be done in google sheets or if someone ever even attempted it...

background: basically it's a clock divided into 4 fields, where every field represents 1 digit based on the sum of randomly lit squares

enter image description here

which reads as 15:35


Solution

  • set time zone if needed
    set recalculation on change and every minute

    enter image description here


    conditional formatting is set that font and background color are same for range B2:M4
    custom formulae are simple for each of four colors:

    =1=B2
    
    =2=B2
    
    =3=B2
    
    =4=B2
    

    and the main arrayformula in B2 is:

    =INDEX(IFERROR(1/(1/{
     VLOOKUP(SEQUENCE(3, 1), {SEQUENCE(3), SORT(QUERY({SEQUENCE(MID(TEXT(NOW(), "hhmm"), 1, 1), 1, 1, ); 
     SEQUENCE(3-MID(TEXT(NOW(), "hhmm"), 1, 1), 1,,)}, "where Col1 matches '\d'", ), RANDARRAY(3, 1), )}, 2, ), {0;0;0}, 
     VLOOKUP(SEQUENCE(3, 3), {SEQUENCE(9), SORT(QUERY({SEQUENCE(MID(TEXT(NOW(), "hhmm"), 2, 1), 1, 2, ); 
     SEQUENCE(9-MID(TEXT(NOW(), "hhmm"), 2, 1), 1,,)}, "where Col1 matches '\d'", ), RANDARRAY(9, 1), )}, 2, ), {0;0;0}, 
     VLOOKUP(SEQUENCE(3, 2), {SEQUENCE(6), SORT(QUERY({SEQUENCE(MID(TEXT(NOW(), "hhmm"), 3, 1), 1, 3, ); 
     SEQUENCE(6-MID(TEXT(NOW(), "hhmm"), 3, 1), 1,,)}, "where Col1 matches '\d'", ), RANDARRAY(6, 1), )}, 2, ), {0;0;0}, 
     VLOOKUP(SEQUENCE(3, 3), {SEQUENCE(9), SORT(QUERY({SEQUENCE(MID(TEXT(NOW(), "hhmm"), 4, 1), 1, 4, ); 
     SEQUENCE(9-MID(TEXT(NOW(), "hhmm"), 4, 1), 1,,)}, "where Col1 matches '\d'", ), RANDARRAY(9, 1), )}, 2, )})))
    

    enter image description here



    for vertical tix clock:

    =INDEX(IFERROR(1/(1/{
     VLOOKUP(SEQUENCE(1, 3), {SEQUENCE(3), SORT(QUERY({SEQUENCE(MID(TEXT(NOW(), "hhmm"), 1, 1), 1, 1, ); 
     SEQUENCE(3-MID(TEXT(NOW(), "hhmm"), 1, 1), 1,,)}, "where Col1 matches '\d'", ), RANDARRAY(3, 1), )}, 2, ); {0,0,0}; 
     VLOOKUP(SEQUENCE(3, 3), {SEQUENCE(9), SORT(QUERY({SEQUENCE(MID(TEXT(NOW(), "hhmm"), 2, 1), 1, 2, ); 
     SEQUENCE(9-MID(TEXT(NOW(), "hhmm"), 2, 1), 1,,)}, "where Col1 matches '\d'", ), RANDARRAY(9, 1), )}, 2, ); {0,0,0}; 
     VLOOKUP(SEQUENCE(2, 3), {SEQUENCE(6), SORT(QUERY({SEQUENCE(MID(TEXT(NOW(), "hhmm"), 3, 1), 1, 3, ); 
     SEQUENCE(6-MID(TEXT(NOW(), "hhmm"), 3, 1), 1,,)}, "where Col1 matches '\d'", ), RANDARRAY(6, 1), )}, 2, ); {0,0,0}; 
     VLOOKUP(SEQUENCE(3, 3), {SEQUENCE(9), SORT(QUERY({SEQUENCE(MID(TEXT(NOW(), "hhmm"), 4, 1), 1, 4, ); 
     SEQUENCE(9-MID(TEXT(NOW(), "hhmm"), 4, 1), 1,,)}, "where Col1 matches '\d'", ), RANDARRAY(9, 1), )}, 2, )})))
    

    enter image description here



    or rounded design:

    =INDEX(IF(""=IFERROR(1/(1/{
     VLOOKUP(SEQUENCE(3, 1), {SEQUENCE(3), SORT(QUERY({SEQUENCE(MID(TEXT(NOW(), "hhmm"), 1, 1), 1, 1, ); 
     SEQUENCE(3-MID(TEXT(NOW(), "hhmm"), 1, 1), 1,,)}, "where Col1 matches '\d'", ), RANDARRAY(3, 1), )}, 2, ), {0;0;0}, 
     VLOOKUP(SEQUENCE(3, 3), {SEQUENCE(9), SORT(QUERY({SEQUENCE(MID(TEXT(NOW(), "hhmm"), 2, 1), 1, 2, ); 
     SEQUENCE(9-MID(TEXT(NOW(), "hhmm"), 2, 1), 1,,)}, "where Col1 matches '\d'", ), RANDARRAY(9, 1), )}, 2, ), {0;0;0}, 
     VLOOKUP(SEQUENCE(3, 2), {SEQUENCE(6), SORT(QUERY({SEQUENCE(MID(TEXT(NOW(), "hhmm"), 3, 1), 1, 3, ); 
     SEQUENCE(6-MID(TEXT(NOW(), "hhmm"), 3, 1), 1,,)}, "where Col1 matches '\d'", ), RANDARRAY(6, 1), )}, 2, ), {0;0;0}, 
     VLOOKUP(SEQUENCE(3, 3), {SEQUENCE(9), SORT(QUERY({SEQUENCE(MID(TEXT(NOW(), "hhmm"), 4, 1), 1, 4, ); 
     SEQUENCE(9-MID(TEXT(NOW(), "hhmm"), 4, 1), 1,,)}, "where Col1 matches '\d'", ), RANDARRAY(9, 1), )}, 2, )})),,"⚫"))
    

    enter image description here



    demo spreadsheet