日付、時間計算関数／日付・時間の比較、足し算、引き算

日付、時間、分の足し算、引き算、今昔比較をする

ここでは、エクセル表にてよく利用する、「日付」や「時間」の足し算・引き算について学んでいきましょう。

 

日付の計算方法）足し算・引き算

下の表は、あるレンタルビデオショップの会員情報をまとめた表です。レンタル会員の期限は1年間で、更新のお願いの為に2週間前にはお手紙を送るようにしています。

そこで、E列に更新時期の2週間前（期限日から2週間を引いた日付）が分かるように計算式を入れてみましょう。

①　期限日から2週間引いた日付を計算する。

計算式は、期限日からそのまま「14」を引き算する形となります。

=D4-14

このように、足し算も同じく通常の数式のようにそのまま日数を引くことで計算が出来ます。

 

時間の計算方法）足し算・引き算

下の表は、ある学生4人が、学校まで急いで通学した場合の到着タイムを記録した表です。

そこで、①晴天時が雨天時よりどれだけ速いのか、また、②踏切の待ち時間2分を引いた場合の晴天時到着時間を求めてみましょう。

①　雨天時の時間から晴天時の時間を引いた計算をする。

計算式は、時間の大きい「雨天時」から「晴天時」をそのまま引き算する形となります。
（※小さいほうからの引き算は不可）

=C4-D4

②　晴天時の時間から踏切待ち時間（2分）を引いた計算をする。

時間に対して「～時間、足したい」や「～分、引きたい」などの場合は、エクセルでは次のような入力の表現をします。

1時間　→　1:00
10分　→　0:10

つまり「2分引く」計算式としては、2分「0:02」を「”（ダブルクォーテーション）」で囲み、

=D4-“0:02”

となります。

 

今昔比較をする）本日からの予定状況を見る

下の表は、ある会社の業務予定表です。すでに完了した予定についてはD列に「完了」と入力してあります。

そこで「本日の日付」と「完了予定日」を比較して、E列に、

完了していれば、「予定通り」
本日の日付を過ぎていれば、「遅延」
まだ予定日前であれば、「空白」

とする計算式を入力してみましょう。

①　本日の日付との比較により、到達度の表示を切り替える。

（1）「本日の日付」のセルE2が、「完了予定日」のセルC5より大きい場合は（2）の条件、違っていれば空白とします。

=IF(E2>C5, （2） ,””)

（2）「完了状況」のセルD5が『完了』でなければ遅延、そうでなければ『予定通り』とします。

IF(D5<>”完了”,”遅延”,”予定通り”)

（3）（1）・（2）をつなげて次のような数式になります。

=IF(E2>C5,IF(D5<>”完了”,”遅延”,”予定通り”),””)

 

 

月末までの日付を自動入力する

月末までの日数を数えるためには、まず月末の日付を求めてから引き算をするようにしましょう。

=EOMONTH(開始日,　経過月)

[開始日] = 日付の入力されたセルを指定します。
[経過月] = 開始日から起算した月数を数値で指定します。（※今月は「0」とする）

関数の説明	[開始日]から数えて[経過月]後の月末日を表示する
EXCELバージョン	Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

使い方例）EOMONTH関数

上と同じ表にて、セルC14に「月末の日付」、セルE14に「月末までの日数」を求めてみましょう。

①　支払日（月末）を求める。

EOMONTH関数にて、[開始日]としてセルE2、[経過月]として今月を意味する「0」を指定します。

=EOMONTH(E2,0)

②　支払日までの日数を引き算にて求める。

月末のセルC14から、本日の日付のセルE2をそのまま引き算します。

=C14-E2

 

日付の自動入力をする／NOW・TODAY、YEAR・MONTH・DAY・DATE関数

本日の日付を、その作業毎に毎回入力するのは大変骨が折れる作業となります。
そこで、エクセルが開かれる度に自動的に「日付」や「時間」を更新してくれる関数と、それに関係する、その他の日付に関わる関数も一緒に覚えておくことにしましょう。

=NOW()

※引数は指定の必要がありません。

関数の説明	現在の日付と時刻を求める
EXCELバージョン	Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

=TODAY()

※引数は指定の必要がありません。

関数の説明	現在の日付を求める
EXCELバージョン	Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

=YEAR(シリアル値)

[シリアル値] = 日付の入力されたセルを指定します。（※日付の表示形式が設定されているセル）

関数の説明	シリアル値を年に変換する
EXCELバージョン	Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

=MONTH(シリアル値)

[シリアル値] = 日付の入力されたセルを指定します。（※日付の表示形式が設定されているセル）

関数の説明	シリアル値を月に変換する
EXCELバージョン	Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

=DAY(シリアル値)

[シリアル値] = 日付の入力されたセルを指定します。（※日付の表示形式が設定されているセル）

関数の説明	シリアル値を日に変換する
EXCELバージョン	Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

=DATE(年,　月,　日)

[年] = 年の指定されたセルを指定します。
[月] = 月の指定されたセルを指定します。
[日] = 日の指定されたセルを指定します。

関数の説明	指定された[年]・[月]・[日]を1つにまとめて、日付形式に変換する
EXCELバージョン	Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

使い方例）日付・時刻の関数

①　現在の日付を表示する（自動更新）。

引数の指定は必要ありません。表示された内容は、エクセルを開くたびに更新されます。

=TODAY()

②　現在の日付と時刻を表示する（自動更新）。

引数の指定は必要ありません。表示された内容は、エクセルを開くたびに更新されます。

=NOW()

③　日付が入力されているセルから、「年」だけを表示する。

日付の指定されているセルE4を指定します。

=YEAR(E4)

④　日付が入力されているセルから、「月」だけを表示する。

日付の指定されているセルE4を指定します。

=MONTH(E4)

⑤　日付が入力されているセルから、「日」だけを表示する。

日付の指定されているセルE4を指定します。

=DAY(E4)

⑥　「年」・「月」・「日」がそれぞれ入力されているセルから日付形式へ変換する。

「年」のセルD8、「月」のセルF8、「日」のセルH8を指定します。

=DATE(D8,F8,H8)

 

今日の日付・時間を表示するショートカット

関数を使わずに、単純に日付や時刻を入力するには、「ショートカットキー」を使用する方法もあります。
→　ショートカットの詳細についてはこちら

 

24時間以上を時間で計算する

エクセルにて、時間を入力して管理する中では、ひと月の勤務時間のように24時間を超えて計算する場合も多くあります。そのような時には少し注意が必要です。

下の表は、ある会社の従業員の、ひと月の勤務時間をまとめて計算しようとしたものです。

セルC12には、SUM関数を使用してセル範囲[C4:C11]を合計する数式が入力されています。

=SUM(C4:C11)

しかし実際に表示されている合計値を見ると、「14:00」、つまり14時間と表示されており、違っていることが分かります。実は、エクセルの時間表示は、そのままでは24時間以上が表示されないように表示形式が設定されてしまうことに問題があるのです。

 

表示形式の設定方法）24時間以上の表示が出来るようにする

では、24時間以上の表示が出来るように「表示形式」の設定をしてみましょう。

 

（1）　表示形式を変更するセルを右クリックして、表示されるメニューから「セルの書式設定」を選択します。

 

（2）　新しく表示される「セルの書式設定」ダイアログボックスより、次の手順で設定します。

①[分類]の一覧から「ユーザー定義」を選択します。
②[種類]の入力欄に「[h]:mm」と入力します。
③OKボタンを押して設定を完了します。

 

（3）　表示形式の設定修正後の結果

 

日付を文字列に変換、文字列を日付に変換する方法

最後に、表示形式に関係することとして、エクセルにて作業中稀に『この「日付」は日付ではなくて普通の文字として認識させたい』や、反対に『この文字は日付の形式として認識させたい』場合も発生します。

このような場合は、「セルの書式設定」を利用する他に次のような関数を使用した方法もありますので覚えておきましょう。

 

日付を文字列に、文字列を日付に変換する）TEXT・DATEVALUE関数

=TEXT(数値,　表示形式)

[数値] = 数値（日付）が入力されたセルを指定します。
[表示形式] = 「セルの書式設定」の[種類]欄にある、”yy/mm/dd” や “#,##0” などの表示書式を指定します。

関数の説明	数値を書式設定した文字列に変換する
EXCELバージョン	Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

=DATEVALUE(日付文字列)

[日付文字列] = 文字列（日付）が入力されたセルを指定します。

関数の説明	文字列をシリアル値（日付形式）に変換する
EXCELバージョン	Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

①　日付が入力されているセルを、文字列として表示させる。

[数値]に日付（日付形式）の入力されているセルB4を指定し、[表示形式]として「yyyy年m月d日」を「”（ダブルクォーテーション）」で囲んで指定します。

=TEXT(B4,”yyyy年m月d日”)

※結果は、文字列としてエクセルに認識出来ます。

②　文字列（日付）が入力されているセルを、日付（日付形式）として表示させる。

（1）TEXT関数にて、日付（文字列）の入力されているセルD4を指定し、[表示形式]として「0000!/00!/00」を「”（ダブルクォーテーション）」で囲んで指定することによって、一度現在の表示を「/」を使用した形に変更します。

TEXT(D4,”0000!/00!/00″)

（2）（1）の内容をDATEVALUE関数にて指定し、日付形式として変換します。

=DATEVALUE(TEXT(D4,”0000!/00!/00″))

※結果は、日付形式としてエクセルに認識されます。