よねさんのWordとExcelの小部屋Excel(エクセル)講座の総目次Excel(エクセル)講座 Q&A(Tips)|時刻・時間関連
よねさんのWordとExcelの小部屋

Excel(エクセル)講座 Q&A(Tips):時刻・時間関連


スポンサードリンク

時刻の計算       Topへ

  1. 出社時刻 8:00 退社時刻 18:30 の場合
  2. 出社時刻がB3セル、退社時刻がC3セルに入力されている場合、D3セルに勤務時間を求めるには
    「=C3-B3」 となります。 セルの表示形式は(1)と同様に時刻に変更します。
    B C D
    2 出社時刻 退社時刻 勤務時間
    3 8:00 18:30 =C3-B3
  3. 退社時刻が 24:00 をすぎる場合
    出社時刻が 18:00 で、退社時刻が 2:00 の時は
    ="2:00"-"18:00" とすると -0.6667 といった値が表示され、表示形式を時刻にすると ##### となります。マイナスになる時刻の計算はできないということです。
    1. =1+"2:00"-"18:00" と、 「1」を加えると 8:00 と計算できます。
      この場合、IF関数と組み合わせて、退社時刻が出社時刻より小さい時は「1」を加えて計算するようにします。
      =IF(C4<B4,1+C4-B4,C4-B4)
      • =(C4-B4<0)+C4-B4 と書くこともできます。TRUE=1,FALSE=0となるのを利用しています。
      • =MOD(C4-B4,1) と書くこともできます。時刻は小数値になるので計算結果の正の小数値を取り出しています。
    2. ="26:00"-"18:00" と、 2:00 を 24:00+2:00=26:00 としても計算できます。
      このとき 26:00 と入力して、表示形式が 時刻の『13:30』 としてあると、2:00 と表示されます。
      26:00 と表示するには、表示形式を『ユーザー定義』で種類に『[h]:mm』 と入力します。
      B C D
      2 出社時刻 退社時刻 勤務時間
      3 18:00 2:00 =1+C3-B3
      4 18:00 2:00 =IF(C4-B4<0,1+C4-B4,C4-B4)
      5 18:00 26:00 =C5-B5
【関係のない余談:どうしてもマイナスの計算をしたい】
【ツール】→【オプション】の【計算方法】タブ『1904年から計算する』に
チェックを付けると、 -16:00 とマイナスの値を表示できます)

ただし、使う前に Microsoft サポート技術情報 ↓を参照願います。
[XL] 1900 年から計算する日付システムと 1904 年から計算する日付システム
http://support.microsoft.com/default.aspx?scid=kb;ja;180162
【おまけ:その他の数式の書き方】
="3:00"+"2:00"
=TIMEVALUE("3:00")+TIMEVALUE("2:00")
="3時00分"+"2時00分"
=TIMEVALUE(3時00分)+TIMEVALUE(2時00分)
=3/24+2/24
などと書くこともできます。
【おまけ:マイナスの時間を表示する】
文字列で表示する例です。
=IF(A1-B1<0,TEXT(ABS(A1-B1),"-"&"h:mm"),TEXT(A1-B1,"h:mm"))
負になる場合は絶対値に「-」を付け加えて文字列にしています。

時刻のシリアル値      Topへ

  1. セルに「24:00」と入力すると、数式バーには「1900/1/1 0:00:00」と表示されます。
    "24:00"= 1 ですので "1:00"= 1/24 となります。
  2. では、"18:30"-"8:00"="10:30"を10.5と表示したい時はどうすればよいでしょうか?
    ="18:30"-"8:00"=0.4375 とシリアル値が表示されます。(セルの表示形式は標準とします)
    =("18:30"-"8:00")*24 = 10.5 とします。
  3. 逆に、10.5を、10:30 としたい時は
    =10.5/24 = 0.4375 として、セルの表示形式を「時刻」の「13:30」とします。

勤務時間の計算      Topへ

パートの人の勤務時間計算で、定時が 9:00〜17:00 となっている場合の計算方法を考えてみましょう。
     9:00        17:00
 (A)
 <---+-----------------+----->
 (B)   
   <-----------+----->  
 (C)   
   <------->

【計算誤差(小数誤差)の防止】
時刻のシリアル値は1未満の小数値になっています。上記のような計算によって誤差を生じることがあります。
計算誤差を防ぐ方法の一つとして、TEXT関数を使う方法を書いてみます。
=MIN(C3,"22:00")-MAX(B3,"17:00")

=TEXT(MIN(C3,"22:00")-MAX(B3,"17:00"),"h:mm")
とします。これは文字列「2:10」となり計算に使えないので、
=VALUE(TEXT(MIN(C3,"22:00")-MAX(B3,"17:00"),"h:mm"))
 として、シリアル値に戻します。
【30分単位で丸める】
時間の計算で「30分単位で丸める」ことが必要な時は
=FLOOR(VALUE(TEXT(MIN(C3,"22:00")-MAX(B3,"17:00"),"h:mm")),"0:30")
とします。
注意:毎日の残業時間の端数を切り捨てることは、労働基準法違反となり認められないようです。
あくまでも計算の例としてご覧ください。

   以上をまとめると、下記のように長〜い式になります。
B C D E F G
2 出社時間 退社時間 定時の時間 残業の時間 定時 残業
3 7:50 19:10 =IF(OR(COUNT(B3:C3)<2,B3-"17:00">0),"",
FLOOR(VALUE(TEXT(MIN(C3,"17:00")-
MAX(B3,"9:00"),"h:mm")),"0:30"))
=IF(OR(COUNT(B3:C3)<2,C3-"17:00"<0),"",
FLOOR(VALUE(TEXT(MIN(C3,"22:00")-
MAX(B3,"17:00"),"h:mm")),"0:30"))
8:00 2:00

テンキーで時刻を簡単に入力する 「:」を「..」で入力する(オートコレクト)

  1. 【ツール】→【オートコレクトのオプション】を選択します。
  2. 【オートコレクト】ダイアログの【オートコレクト】タブを選択します。
  3. 「入力中に自動修正する」にチェックを入れます。
  4. 『修正文字列』に「..」 『修正後の文字列』に「:」を入力します。
  5. 【追加】ボタンをクリックします。

スポンサードリンク


よねさんのWordとExcelの小部屋Excel(エクセル)講座の総目次Excel(エクセル)講座 Q&A(Tips)|時刻・時間関連

 PageViewCounter
 Counter
 Since2006/2/27