Excel業務カイゼンブログ

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

基本の知識

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

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

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

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

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

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

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

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

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

基本の知識

それに対して、次のような、花の名前を入力したら、振り仮名や季節、写真の表示ができるウィンドウを作成します。

「表示」シートのセルB2を先頭とした結合セルには、「データベース」シートのA2からA4までの範囲から花の名前が選択できるように入力規則のリストがすでに設定されています。セルG2を先頭とした結合セルには、「データベース」シートのA2からD4までの中で、セルB2の花の名前に該当する２列目の振り仮名を求めるVLOOKUP関数が入力されています。セルM3を先頭とした結合セルには、「データベース」シートのA2からD4までの中で、セルB2の花の名前に該当する４列目の季節を求めるVLOOKUP関数が入力されています。枠線は、［表示］タブの中の［枠線］のチェックを外して、非表示にしています。

セルB4を先頭とした結合セルに、セルB2の花の名前に対応した「データベース」シートのC列の花の写真を表示すれば完成です。その手順を紹介します。

実践ファイルはこちらからダウンロードできます。

日時の計算

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

セルD1には2017年6月1日の午前0時ということで、「2017/6/1 0：00」と入力されています。2017年6月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時になることを確認しましょう。

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

タイムカードへの応用

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

=D2-C2

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

そこからお昼休みの休憩時間を引けば勤務時間となります。セルG２に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という結果になるからです。もし、延べ日数を求める場合は１を加算する必要があります。

日時の大きさ

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

• 年は、うるう年を考慮しなければ、西暦年より1900を引いた数値に365を乗算します。1902年は730となります。
• 月は、ひと月を30日と考えれば、月数から1を引いたものに30を乗算します。
• 日は、そのままで20日は20になります。
• 時は、２４分の１、つまり「1/24」を乗算します。2時は「2*(1/24)」で求められます。
• 分は、２４分の１の６０分の１、つまり「1/1440」を乗算します。30分は「30*(1/1440)」で求められます。
• 秒は、２４分の１の６０分の１の６０分の１、つまり「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つの数値から１つの日付データを求めました。

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つの数値から１つの時刻データを求めました。

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」は、入力年の１月１日になり、「2012/1」は2012年の1月1日になります。ただし、「平成11年1月」は日付データではなく、文字になります。「平成11年1月1日」は日付データとして認識します。

時刻の直接入力

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

入力時の小数点以下の秒

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

日時の計算時の書式設定

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

まとめ

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