記録された日時をタイムブロックで色分け表現したい【スプレッドシート】

Question

前提・実現したいこと

A~C列にある記録時間とタスク内容をG～K列に日ごとのタイムブロックで表現したい。
※その日の出来事を一列にまとめて記入、タスク別に列ごとに記載ではない

シートの共有

●画像の見方
0. F列に１０分ごとのタイムライン、F2行から右へ日付別
0. A2からC列まで一行ごとにタスクの作業時間を①のタイムラインに色付けする。
0. 二行以降下へセルを読み込みタイムラインに記載する。

発生している問題・エラーメッセージ

G3:K146までのタイムラインに色付けする為、
条件付き書式設定（カスタム数式）：該当セルは緑色

=and($B2=G$2,TIME(HOUR($B2),MINUTE($B2),SECOND($B2))<=$F3,TIME(HOUR($C2),MINUTE($C2),SECOND($C2))>=$F3)
【解説】
=and(G2の日付と開始時間の日付が同一,B2のhh:mm:ss >=タイムラインが該当するセルを色付け,C2のhh:mm:ss>=タイムラインが該当するセルを色付け)

試したこと

補足情報

ネットで検索しても列ごとの期間の色付けはありました
|初め|終わり|３時|４時|５時|６時|
|:--|:--:|--:|
|午後３時|午後４時|●|●||
|午後５時|午後６時|||○|○|

こういうのではなく、リストから日ごとに縦１列に全てのタスクを記す方法です。
おわかりの方、教えてください。よろしくお願いいたします。

Answer 1

一応下記でできました。

前提
各セルの位置関係は、画像の通りとします。

また、書式設定で、
・B列、C列が日付時刻形式、
・F列3行目以降が時刻形式(0:00)
・2行目G列以降の列が日付形式、（かつ、内部的に時刻が0:00となっていること）
となっているとします。
（この前提を満たさない場合、正しく計算されません）

---

・エクセル（Excel for Microsoft 365 MSO (バージョン 2109)にて動作確認）：

G3セルに対して、条件付き書式 ->「数式を利用して書式を設定」で下記数式を入れて、色を設定。
残りのセルは書式コピー。

=IF(INDEX(SORT(FILTER($A$1:$A$1000, ((($B$1:$B$1000<=(VALUE(G$2)+VALUE($F3)))*($C$1:$C$1000>(VALUE(G$2)+VALUE($F3)))) +  (($B$1:$B$1000<(VALUE(G$2)+VALUE($F4)))*($C$1:$C$1000>(VALUE(G$2)+VALUE($F4))))),""),1,-1),1,1)="とりあえず時間計測",TRUE)

　

・Google スプレッドシート
条件付き書式のカスタム数式に下記の数式を設定。

=if(query($A$2:$C,"select A where (B <= datetime '"&text(G$2,"yyyy-MM-dd") & text($F3," hh:mm:ss")&"' and C > datetime '"&text(G$2,"yyyy-MM-dd")&text($F3," hh:mm:ss") &"') or (B < datetime '"&text(G$2,"yyyy-MM-dd") & text($F4," hh:mm:ss")&"' and C > datetime '"&text(G$2,"yyyy-MM-dd")&text($F4," hh:mm:ss") &"') limit 1")="とりあえず時間計測",TRUE)

※「食事」・「入浴」等他の項目についても同様に設定し色を変えてください。

Comments

[mapleleaf]

迅速なご回答ありがとうございます。想像通りのタイムラインを作成できました。また機会がありましたら、ご教授お願いいたします。