Google Sheets, Live Countdown to display stock market open/close hours

Question

I am trying to create a live timer. I want to show how many hours till closing THEN show how many hours till opening.

1. WHEN the date is on a SUNDAY, I want to show that "market is closed".
2. THEN if its any day other than Sunday it shows "Market opens in 00 hours, 00 minutes" or "Market closes in 00 hours, 00 minutes" with having the "hours" and "Minutes" changing from plural to singular WHEN Hours > 1 and Minutes > 1.
3. So, the market starts at 10am (10:00) and closing is at 4pm (16:00).

I had a live timer, however you need to type the date and time.

=iferror(int(E11-now())&" "&if(days(E11,now())>1,"Days","Day")&" : "&hour(E11-now())&" "&if(hour(E11-now())>1,"Hours","Hour")&" : "&MINUTE(E11-now())&" "&if(MINUTE(E11-now())>1,"Minutes","Minute"),"0 Day : 0 Hour : 0 Minute")

With this code, it is giving the stats of if the market is closed or open but how many till closing or open. Attempted to use the code below and adjusting it to get a live count down but I can't :(.

=if(OR((weekday(H4))=1,(weekday(H4))=7),"Market Closed", if(time(hour(now()),minute(now()),second(now()))>TIME(16,0,0),"Market Closed",if((time(hour(now()),minute(now()),second(now()))>TIME(10,0,0)),"Market Open","Market Closed")))

Thanks

Answer 1

One way, perhaps a little verbose:

=LET(
    datetime, NOW(),
    date, TODAY(),
    clk, MOD(datetime, 1),
    open, TIME(10, 0, 0),
    close, TIME(16, 0, 0),
    showTime, LAMBDA(t,
        TEXT(HOUR(t), "00") &
            " hour" &
            IF(
                HOUR(t) <> 1,
                "s ",
                " "
            ) &
            TEXT(
                MINUTE(t),
                "00"
            ) & " minute" &
            IF(
                MINUTE(t) <> 1,
                "s",
                ""
            )
    ),
    IFS(
        WEEKDAY(date) = 7,
        "Closed today",
        clk < open,
        "Market opens in " &
            showTime(
                open - clk
            ),
        clk > close,
        "Closed now",
        TRUE,
        "Market closes in " &
            showTime(
                close - clk
            )
    )
)

In File|Settings|Calculation, I chose update on change and every minute.