Google Sheets股市开盘/收盘实时倒计时制作求助

完整公式 #

直接复制以下公式到Google Sheets单元格即可使用：

=IF(WEEKDAY(TODAY())=1, "市场休市", 
  IF(NOW() < TODAY()+TIME(10,0,0), 
    "Market opens in "&TEXT(TODAY()+TIME(10,0,0)-NOW(),"h")&" "&IF(HOUR(TODAY()+TIME(10,0,0)-NOW())>1,"Hours","Hour")&", "&TEXT(TODAY()+TIME(10,0,0)-NOW(),"m")&" "&IF(MINUTE(TODAY()+TIME(10,0,0)-NOW())>1,"Minutes","Minute"),
    IF(NOW() > TODAY()+TIME(16,0,0), 
      "Market opens in "&TEXT(TODAY()+IF(WEEKDAY(TODAY())=7,2,1)+TIME(10,0,0)-NOW(),"h")&" "&IF(HOUR(TODAY()+IF(WEEKDAY(TODAY())=7,2,1)+TIME(10,0,0)-NOW())>1,"Hours","Hour")&", "&TEXT(TODAY()+IF(WEEKDAY(TODAY())=7,2,1)+TIME(10,0,0)-NOW(),"m")&" "&IF(MINUTE(TODAY()+IF(WEEKDAY(TODAY())=7,2,1)+TIME(10,0,0)-NOW())>1,"Minutes","Minute"),
      "Market closes in "&TEXT(TODAY()+TIME(16,0,0)-NOW(),"h")&" "&IF(HOUR(TODAY()+TIME(16,0,0)-NOW())>1,"Hours","Hour")&", "&TEXT(TODAY()+TIME(16,0,0)-NOW(),"m")&" "&IF(MINUTE(TODAY()+TIME(16,0,0)-NOW())>1,"Minutes","Minute")
    )
  )
)

公式逻辑拆解 #

• 周日判断：通过WEEKDAY(TODAY())=1识别周日，直接返回「市场休市」（Google Sheets默认周日为一周第1天）。
• 开盘前状态：当当前时间早于当日10:00时，计算距离开盘的小时/分钟数，自动判断单复数（如1 Hour/2 Hours，1 Minute/2 Minutes）。
• 交易中状态：当前时间在10:00-16:00之间时，计算距离收盘的时长，同样处理单复数。
• 收盘后状态：当前时间晚于当日16:00时，自动计算下一个开盘日的时长——若当天是周六，则跳至周一开盘；其他非周日则次日开盘。

注意事项 #

• NOW()函数会自动实时刷新，无需手动更新，刷新频率可通过Google Sheets设置调整。
• 务必确保表格时区与股市所在时区一致，避免时间偏差，可通过「文件>设置>区域设置」修改。

内容的提问来源于stack exchange，提问作者asianpanda