上の例をさらに進めて、「通常時間」と「深夜時間」に分けて見ましょう。「深夜時間」とは一般に午後10時から午前5時を指しますので、ここでもその時間帯を「深夜」とします。

• 実際に計算したものを表示します。関数は上のサンプルと同じ「FLOOR関数(切り捨て)」、「CEILING関数(切り上げ)」の他、「AND関数」「MIN関数」「MAX関数」「SUMIF関数」を使います。
  
  青枠が元の開始・終了時刻、赤枠の部分が算出結果です。Excelで起動できる方は青枠の時刻をいろいろ変えて見て下さい。これで見ると「SUMIF関数」を単純に使っているだけですが、H列から右側に中間計算をしている部分がたくさんあります。
• まず、「開始時刻」と「終了時刻」をそれぞれ「まるめ」たもの(時刻)を用意します。
  
  上のサンプル(前項)同様にNETの時刻のままのものと、15分単位、30分単位に切り捨てたものを用意します。上のサンプル(前項)では、いきなり差し引きの時間数を算出してしまいましたが、ここでは「まるめ」た時刻にとどめます。開始時刻側はCEILING関数で15分か30分単位に切り上げし、終了時刻側はFLOOR関数で切り捨てます。
  ※R1C1参照形式の場合は
  

  
   =SUMIF(R2C12:R10C12,0,R2C14:R10C14)
  

  となります。
  
  
• さらに上記の画面の右側で、通常時間、深夜時間の各時間帯に「どれだけ掛かっているのか」を個別に計算します。
  
  (画像をクリックすると、このサンプルがダウンロードできます)
  少々、長い計算式となります。
  赤枠の左側の青字部分が「各時間帯」を表わしています。例えば選択されている「開始5:00、終了22:00」はその時間帯となります。その隣の「0」「1」は、「0」だったら通常時間帯、「1」だったら深夜時間帯となります。
  左の黒字の時間数は、左からNET、15分単位、30分単位で青字の各時間帯に掛かっている時間数を表わしています。
  ※R1C1参照形式の場合は
  

  
   =IF(AND(R2C8RC10),MIN(R2C9,RC11)-MAX(R2C8,RC10),0)
  

  となります。
  
  
• 計算式は、まず該当の時間帯に開始／終了時刻の範囲が「掛かっているか」を判断します。実際に入力された開始時刻がその時間帯の終了時刻より「前」で、かつ(AND)、入力された終了時刻がその時間帯の開始時刻より「後」であれば、その時間帯に掛かっていることになります。
• 掛かっているならば、入力値か時間帯開始の大きい方から、入力値か時間帯終了の小さい方までの時間数を算出し、「時間数(時間帯ごと)」にセットします。MIN関数を使っている方が終了時刻側で、まるめた元の「終了時刻」とその時間帯行の「終了時刻」の小さい方、MAX関数を使っている方が開始時刻側で、まるめた元の「開始時刻」とその時間帯行の「開始時刻」の大きい方、これで算出された「終了時刻」から「開始時刻」を差し引いてその時間帯行あたりの時間数が算出されます。
• 最後に、各時間帯にセットされた各時間数を、「深夜」の値が「0」か「1」かで判断してそれぞれの時間数のを集計しているわけです。このような複雑な計算では、答えを収容するセルに単に計算式を用意すれば済むものではなく、欄外(デザイン的には行列を隠して見えなくする)に各種の中間計算を必要とします。
• ここではお見せしませんが、私がやっている例では、「深夜」以外に会社規定休憩時間帯、勤務予定時間帯の「0」「1」を用意して、もっと細かい時間帯に分けて中間計算しており、「通常」「深夜」の時間数からは休憩時間帯を除外し、さらに勤務予定時間帯に掛かる時間数から「遅刻・早退」時間も算出します。全体の時間は「深夜」か「通常」かに分けるのはこのサンプルと同じですが、このサンプルでは休憩控除がありません。私の勤務先では24時間の中に休憩時間帯が6カ所定められているので、実就業の時間帯でそれぞれの休憩時間帯に掛かっている分を差し引きます。
  これとは別に勤務予定時間帯で休憩時間帯でない分の各時間帯の時間数も割り出します。これは遅刻・早退の時間数を把握するためです。
  これらの集計にはSUMIF関数ではなく、SUMPRODUCT関数を使用します。