午前0時を挟む時間計算の方法：Excel実用編

作成:2012/6/24

午前0時を挟む時間計算の方法

• 午前0時を挟む時間の計算はちょっとややこしくなります。
• 勤務時間で、出社時刻が20：00、退社時刻が2:00のケースを例にすると、
  勤務時間=退社時刻-出社時刻で求めると、-18:00　となります。
  • (注)Excelのオプションで「1904年から計算する」にチェックを入れるとマイナスの値となる計算が可能になります。
• 対処法
  1. 入力する退社時刻を「2:00」ではなく「26:00」と入力する。
     この26:00は出社時刻の翌日の2:00ということになります。
     • セルの表示形式を「[h]:mm」とすると「26:00」と表示されます。
       表示形式が「h:mm」となっていると「2:00」と表示されます。
     • 26:00　と入力すると、勤務時間=退社時刻-出社時刻で求めると、6:00　と求めることができます。
     • 計算例
       
  2. 勤務時間が24時間を超えることがない・・・という条件であるとき
     • 退社時刻が24時を超えるときは出社時刻より小さくなります。
       (出社時刻>退社時刻)という条件が成り立つということです。
     • ("20:00">"2:00")　という論理式は　TRUEとなり、数式の計算では1として計算されます。
       逆に、24時より前の23:00の場合は("20:00">"23:00")　False　で0となります。
       • 24:00のシリアル値は1であるので、TRUEの1と同じでありちょうど都合がよいことになっています。
         2:00を2:00+24:00として計算できるようになっています。
     • よって、勤務時間=退社時刻+(出社時刻>退社時刻)-出社時刻　といった数式で計算できます。
       ="2:00"+("20:00">"2:00")-"20:00"
       ="2:00"+1-"20:00"　　　(1="24:00"と直して考えればわかりやすいでしょう)
       ="6:00"
       となります。
     • 計算例
       
     • 論理式ではなく、IF関数でも数式を書くことができます。
       =退社時刻+IF(出社時刻>退社時刻,1,0))-出社時刻
       ="2:00"+IF("20:00">"2:00",1,0))-"20:00"
       ="6:00"
       となります。

時間帯を分けて計算する　　　　　　Topへ

• 時間帯によって時給が違う・・・といったようなときには、時給に応じた時間帯ごとの時間を計算するのが簡単と考えます。
  • 24:00までは1000円、24:00を超えると1500円といったケースを想定してみます。
  • 数式の基本形は=MIN(退社時刻,計算期間の終了時刻)-MAX(出社時刻,計算期間の開始時刻))
• 下記の計算例は12:00〜24:00と24:00〜36:00の時間帯で計算してみます。
  

1. 計算例A：24時以降の入力を26:00の形式としているケース
   • 12:00〜24:00と24:00〜36:00の時間帯で計算することにしました。
     • 12:00〜24:00の計算は=TEXT(MAX(0,MIN($C3,"24:00")-MAX($B3,"12:00")),"h:mm")*1
     • 24:00〜36:00の計算は=TEXT(MAX(0,MIN($C3,"36:00")-MAX($B3,"24:00")),"h:mm")*1
     
2. 計算例B：24時以降の入力を2:00の形式としているケース
   • 計算例Aに(出社時刻>退社時刻)　の条件判定を付け加えました。
     • 12:00〜24:00の計算は=TEXT(MAX(0,MIN($C3+(B3>C3),"24:00")-MAX($B3,"12:00")),"h:mm")*1
     • 24:00〜36:00の計算は=TEXT(MAX(0,MIN($C3+(B3>C3),"36:00")-MAX($B3,"24:00")),"h:mm")*1
     
3. 時給計算を付け加えると下図のようになります。
   • 時刻のシリアル値は"24:00"=1なので、支払額を計算するには　*24　が必要になります。
     =D3*24*1000+E3*24*1500
   

スポンサードリンク

PageViewCounter
　
　Since2006/2/27