Excelで扱う日付・時間についてまとめ

2017.06.22基礎・入門関数その他

Excelでは日付と時間のデータを扱うことができますが、実際の中身がどうなっているかという概念についてなかなか把握しにくいものです。
今回は、Excelの日付・時間をどのように考えればわかりやすいか、原理から詳しく解説します。
理解を深めるためのブックも用意しています。こちらからダウンロードして、ぜひ操作してみてください。

基本の知識

Excelでは日時の正体はただの数字なのです。
では、どんな数字なのでしょう。
西暦1900年1月1日午前0時ちょうどを1としています。
また、1日24時間を1としています。
この数値のことを「日付シリアル値」と呼びます。

では、実際にExcelで確認してみましょう。
ダウンロードしたファイルの「日時の正体と計算」シートをご覧ください。

日時の正体と計算

 

セルA2には西暦1900年1月1日午前0時ということで、「1900/1/1 0:00」と入力されています。
この正体は1という数値なのですが、日時表示の数値の正体を確認するには、次のいずれかの方法を使います。

  • 確認するセルをクリックした後、「Ctrl+1」のショートカットキーでセルの書式設定にて表示形式を標準に変更します。
  • 確認するセルをクリックした後、書式設定を標準にするショートカットキー「Ctrl+Shift+^」を押すことでも標準形式の表示形式になります。

では、セルA2をクリックして、上記のいずれかの方法で標準形式の表示形式に変更してみましょう。

西暦1900年1月1日午前0時

 

セルA5には24時間ということで、「24:00:00」と入力されています。
この正体は1です。標準形式の表示形式に変更し確認しましょう。

24時間

 

セルA8には西暦1900年1月3日午前0時ということで「1900/1/3 0:00」と入力されています。
西暦1900年1月3日午前0時は西暦1900年1月1日午前0時から数えて3日目なので3なのです。
標準形式の表示形式に変更し確認しましょう。

西暦1900年1月3日午前0時

 

セルA11には午前6時ということで、「6:00:00」と入力されています。
日付のない午前6時は1日の四分の一ですので、0.25となります。
標準形式の表示形式に変更し確認しましょう。

午前6時

 

セルA11には西暦1900年1月5日の18時ということで、「1900/1/5 6:00」と入力されています。
1900年1月5日の18時は、西暦1900年1月1日午前0時から数えて5日と四分の三ですので、5.75となります。
標準形式の表示形式に変更し確認しましょう。

西暦1900年1月5日の18時

日時の計算

日時はただの数値ということは、四則計算もできます。
D列をご覧ください。

セルD1には2017年6月1日の午前0時ということで、「2017/6/1 0:00」と入力されています。
2017年6月1日から数字の1を引けば、前日の2017年5月31日となります。
セルE2にD1から1を引く数式、

=D2-1

を入力して、2017年5月31日になることを確認しましょう。

前日

 

セルD3には午前1時ということで、「1:00」と入力されています。
午前1時に0.5を足すと、0.5は一日24時間の半分ですから12時間後の13:00になります。

セルE3に、D3の値と0.5を足す数式、

=D3+0.5

を入力して、13時になることを確認しましょう。

12時間後

 

セルD5には8時間ということで、「8:00」と入力されています。
8時間に5を乗算すれば40時間と求まります。
セルE5にD5に5を乗算する数式、

=D5*5

を入力します。
16:00という表記になりますが、時刻は設定をしないと1日である24時間ごとの表記になります。
40時間は24時間と16時間なので、16:00と表記されました。
24時間以上の時間を表示するには、表示形式のユーザー定義の書式設定で[h]:mmと指定する必要があります。
記事「Excelの基本・時刻の表示形式を理解しましょう」を参照し、24時間以上の時間表示形式に変更しましょう。

時刻の掛け算

タイムカードへの応用

この原理を利用すればタイムカードも作ることができます。
ダウンロードしたファイルの「タイムカード」シートをご覧ください。
退勤時間から出勤時間を引けば会社にいた在社時間となります。
セルE2に6月1日の退勤時間から出勤時間を引く計算式

=D2-C2

を入力し、E3からE11までコピーすれば在社時間が求まります。

在社時間

 

そこからお昼休みの休憩時間を引けば勤務時間となります。
セルG2に6月1日の在社時間から休憩時間を引く計算式

=E2-F2

を入力し、G3からG11までコピーすれば勤務時間が求まります。

勤務時間

 

さらに、セルE12に在社時間の合計をオートSUMで求め、24時間以上の表示形式にすることで、在社時間の合計を求めることができます。
また、同様にセルG12に勤務時間の合計を求めることができます。

合計在社時間と勤務時間

日数の差

日数を求める場合、例えば2017年6月5日から2017年6月7日の日数は3日ですが、2017年6月7日から2017年6月5日を差し引くと、計算上は2という答えが出ます。
これは、2017年6月5日の午前0時から2017年6月7日の午前0時までの48時間を計算するので、2という結果になるからです。
もし、延べ日数を求める場合は1を加算する必要があります。

日時の大きさ

上記でも書いたように日時の大きさは、1日24時間を1と考えます。
これを元に計算式を考えると、

  • 年は、うるう年を考慮しなければ、西暦年より1900を引いた数値に365を乗算します。1902年は730となります。
  • 月は、ひと月を30日と考えれば、月数から1を引いたものに30を乗算します。
  • 日は、そのままで20日は20になります。
  • 時は、24分の1、つまり「1/24」を乗算します。2時は「2*(1/24)」で求められます。
  • 分は、24分の1の60分の1、つまり「1/1440」を乗算します。30分は「30*(1/1440)」で求められます。
  • 秒は、24分の1の60分の1の60分の1、つまり「1/86400」を乗算します。15秒は「15*(1/86400)」で求められます。
  • 小数点以下の秒は、秒と同じようにして求められますので、0.5秒は、「0.5*(1/86400)」で求められます。

ダウンロードしたファイルの「日時演算」シートをご覧ください。
上記の計算式を使ってA1からA6の範囲に入力した年月日と時分秒から一つの日時をセルC7に求める計算シートを作成しました。
セルC7は日付シリアル値です。記事「Excelの基本・時刻の表示形式を理解しましょう」を参照し、表示形式を「yyyy/mm/dd hh:mm:ss」形式に変更してみましょう。

日時の換算

 

入力は2017年6月1日15時55分38秒でしたが、結果は2017年5月1日15時55分38秒とちょうど1月ずれました。
この計算式ではうるう年や30日以外の月が考慮されていないので、正確な数値は求まりませんでした。
日付や時刻ではなく、単純に日数や経過時間を日付シリアル値に換算する場合はこの方法が有効です。

年月日、および時分秒から正確な数値を求める場合は、DATE関数やTIME関数が用意されています。
ダウンロードしたファイルの「DATE関数とTIME関数」シートをご覧ください。

DATE関数

DATE関数の書式は

=DATE(年,月,日)

です。2016年6月1日の日付を正確に求める式は

=DATE(2016,6,1)

で答えは42552になります。これは、1900年1月1日より数えて42552日目を表します。日付形式の書式に設定すれば、2016年6月1日であることがわかります。
では、「DATE関数とTIME関数」シートのセルC3に次のDATE関数を作成してみましょう。

=DATE(A1,A2,A3)

3つの数値から1つの日付データを求めました。

DATE関数

 

DATE関数で求めたセルは、「yyyy/m/d」の表示形式に自動で変更になります。

TIME関数

TIME関数の書式は

=TIME(時,分,秒)

です。13:30:25の時刻を正確に求める式は

=TIME(13,30,25)

で答えは0.562789352となります。これは1日24時間を1として考えて、そのうちのおおよそ0.56経過した時間ということを表します。時刻書式にすれば13:30:25であることがわかります。
では、セルC6に次のTIME関数を作成してみましょう。

=TIME(A4,A5,A6)

3つの数値から1つの時刻データを求めました。

TIME関数

 

TIME関数で求めたセルは、「h:mm AM/PM」の表示形式に自動で変更になります。
最後にセルC7に日付を求めたC3のセルと時刻を求めたC6のセルの合計を求めてみましょう。

=C3+C6

日付と時間の合算

 

日付と時刻が合算され、書式が日付と時刻がどちらも表示されている書式に変更になります。

日時データの入力の際のポイント

日時のデータを直接入力する際にも、Excelは日付として簡単に入力できるように考えて入力を手伝ってくれます。
様々な日時の入力でどのように認識するかまとめました。
実際に空欄のセルで動作を確認してみましょう。

日付の直接入力

日付は「1/25」や「1月25日」のような月日だけでも日付として認識します。その場合、年は入力時の年で自動的に入力されます。今年中に来年の日付を入力するような、年を跨る入力の際は、年も含めて入力することに注意が必要です。
あまり意識しませんが、時刻なしで日付を入力すると、その日付の午前0時のデータになります。日付と認識するための区切り文字は、「/」や「年」「月」「日」が使えます。他にも「-」が使え、「.」は使えません。
年は、4桁で入力せず、2桁で入力した場合、29までは2000年代で30からは1900年代になります。ただしこれは2017年6月時点のExcelではそうなのですが、今後、年が進むにつれて変わる可能性がありますので、年は4桁で入力するようにしましょう。
Excelは日付と解釈できるものをできるだけ日付として認識します。「1/1」は、入力年の1月1日になり、「2012/1」は2012年の1月1日になります。
ただし、「平成11年1月」は日付データではなく、文字になります。「平成11年1月1日」は日付データとして認識します。

時刻の直接入力

時刻を直接入力する際は「13:10」や「10時40分」のように「:」や「時」「分」で区切って時、分の形式で入力します。「2'10"7」という入力はできません。
午後に「3:00」と入力しても15時とは認識せず、午前3時になります。午後の時刻のことを考えると、時刻の入力は24時間表記で入力しましょう。
時刻を入力した場合、日付は入力した日付にはならず、表示上、1900年1月0日の入力した時刻となります。
翌日の午前1時を入力したい場合は「25:00」と入力します。深夜勤務などがある場合は、このように24時間以上の入力とします。
「1:0:9」と1桁ずつで入力しても「1:00:09」と認識します。
「1:25」と入力すると1時25分になります。1分25秒と入力したい場合は、「0:1:25」と入力します。

時刻と日付の組み合わせの入力

時刻と日付のどちらも入ったデータを入力する際は、日付と時刻の間に1個以上の全角か半角のスペースを置けば入力できます。
スペースを置かない場合は日付として認識せず、文字列になってしまうので注意が必要です。
「2/20 18:0」のような入力でも、入力年の2月20日の18時と認識します。

入力時の小数点以下の秒

小数点以下の秒を直接入力する場合、「0:00:00.xx」という時、分、秒とカンマ入力後の小数点以下の数値として入力ができます。
10.41秒は「0:00:10.41」で入力ができます。
直接入力は、小数点以下3桁の1000分の1秒までしか扱えません。
時間は単純な数値なので、とても小さな秒数でも「=1/24/60/60*(0.000001)」のような式で入力すれば、日付シリアル値としては認識できるので、例えばIF関数で100万分の1秒より大きいかどうかの判断には使用できますが、Excelには小数点4桁以下を表示させる手段はないので、どうしても表示したい場合は桁を換算するために100万を乗算するなどの工夫が必要です。

日時の計算時の書式設定

日時の計算式を入力したセルの書式設定は、自動的に日時の書式が設定されます。
気をつけたいのは、ある日付からある日付の日数を計算したかった場合でも、結果が日付の書式になるので、日数としてはわかりにくくなります。
その場合は、計算式を求めたセルの表示形式を標準に変更する必要があります。

まとめ

日時はただの数字だったということがおわかりいただけましたでしょうか。
日時が単純な数値で計算できるのはとても都合がよく、タイムカードなどの管理では、便利さがよくわかります。
例えばパートタイムの給与計算では、1日6時間つまり0.25日を20日勤務で合計5日と求まり、それを時間へ換算するのに24を乗算して120時間とし、そこに時給を乗算すれば1月の給与が計算されます。

Back to top