実務の現場でExcelを使う時は、数字の集計がメインですが、日付を使って作業することもあります。
日付データの基本 シリアル値
Excelで日付を入力する方法を考えましょう。
余り意識せずに数字を入力すると、自然に日付が表示されていた、という経験があるかもしれません。
たとえば、あるセルに
と入力すると
「1月23日」と変換してくれます。
Excelが気を利かせて日付データとして処理してくれるので、このような正しい表示ができます。
ありがたい機能ではありますが、文字や数値データとして入力したものが、勝手に日付データに置き換わってしまうと言うこともあります。
せっかくですので、Excel上の日付データの仕組みを知って、正しく日付データを扱えるようにしておきましょう。
Excelは日付データを「年、月、日」の形で処理しているわけではありません。
日付に”通し番号”をつけて、その番号に応じて「○年×月△日」の形に変換して表示しています。
この”通し番号”のことを「シリアル値」といいます。
シリアル値の１は「1900年1月1日」。
「1900年1月２日」＝２
「1900年1月３日」＝３
「1900年1月４日」＝４
・
・
と1日ごとに番号が割り振られていて、セルの形式を「日付」にすることで、日付の形で表示されるという仕組みです。
ですので、あるセルに「１」と入力して、
Enterをタッチすると
と表示されます。
もちろん、通常の入力でシリアル値を意識しながら、日付を入力するのは面倒です。
日付をシリアル値として認識されるように入力するためには、
「2015/1/23」のように「年/月/日」をスラッシュで区切って入力すればOKです。
数値に表示方法を変えると、
シリアル値で入力されていることが分かりました。
このように正しく数値データが入力されていれば、自由に計算ができますが、
「20150123」とスラッシュなしで入力されていたり、
「年」、「月」、「日」が別のセルに入力されていたり、
して、日付データがシリアル値として入力されていないことがあります。
このような場合に、表示されている数値をシリアル値に変換してくれる関数があります。
DATE関数です。
使い方は簡単です。DATE関数の中に、年、月、日、を入力するだけ。
＝DATE（①年,②月,③日）
３つの数値を入力すれば、シリアル値にしてくれます。
やっていることは、セルに「2015/1/23」とスラッシュで正しく入力しているのと同じです。
データの形が「2015/1/23」になっていない、日付データを扱うときには、DATE関数を使ってシリアル値にしてから作業することになります。
実務で使う日付に関する関数
日付データを実務で使うケースを２つ考えてみます。
在籍年数、年齢の計算
ボーナスの計算や保険料の計算などで、従業員の在籍年数や年齢を計算する場合があります。
その際に使うのがDATEDIF関数です。
早速使ってみます。
入力するデータは３つです。
①開始日
②終了日
③表示形式
これだけです。
上の例だと、年齢の計算なので「①開始日」は誕生日。
終了日は、今日の日付なのでTODAY関数を使っています。
今日のシリアル値を求めるのがTODAY関数で「TODAY()」と入力すればOKです。
表示形式は”年”で表示したいので”Y”としています。
。
関数の機能としてはシンプルで、引き算しているだけ。
日付データ（年、月、日）から、その間の期間を計算するときには、DATEDIF関数があることを覚えておいて下さい。
在籍年数も、開始日を入社年月日にすれば同様に求められます
営業日数を求める
営業日数を求めます。
こちらもDATEDIF関数と同じで期間をもとめますが、土、日、祝日があるので、これをどう取り除くかが問題。
ですが、これにも営業日数を求める関数があります。
NETWORKDAYS関数です。
今日から決済日までの営業日数を求めることを考えます。
これも、考え方はDATEDIF関数と同じで、日付同士の引き算です。
入力するデータは、
①開始日
②終了日
の２つ。
①開始日はその日の日付ですから、TODAY関数を使って、「TODAY()」を入力
②終了日は決済日の日付だから、データが入力セル（上の例では”C4”）を入力
その結果が。
41と出ました。
計算としては正しいのですが、データとしては不十分です。
実は、もう一つ考えなければいけないことがあります。
それは、祝日のデータ。
上の41は土、日を考慮しているだけで祝日までは考慮されていません。
そこで、別に祝日のデータを使って、該当する日があればその日もマイナスすることになります。
（Excelでは日本の祝日までは織り込めていないので仕方ありません）
WEBから祝日のデータを入手します。
おっと、困りましたね。
文字データで「2015年1月1日」と入力されています。
これでは、シリアル値として扱うことができませんから、シリアル値に直す必要があります。
先ほど出てきた、DATE関数ですね。
＝DATE（①2015, ②1, ③1）
と入力することになります。
ですが、データの中にある「年」「月」「日」の文字を除いて、DATE関数に入力しなければいけません。
わざわざ手打ちするのも面倒です。
そこで使うのが、MID関数です。
MID関数は、セルの中にあるデータについて、「何番目から、いくつ分」と指定すれば、そのデータを表示してくれる関数です。
たとえば、
「2015年01月01日」と入力されているセルについて
＝MID（H4,1,4）と入力すると
「H4セルについて、1番目から４つ分表示せよ」と言う意味になります。
この要領で、MID（H4,6,1）、MID（H4,8,2）として「月」「日」を抜き出すことができます。
こうして、祝日データが完成します。
このデータが入力されている範囲を、NETWOEKDAYS関数の最後に入力します。
各社のセルにコーピーすると
これで、決済日までの営業日数が表示されました。
まとめ
日付データは、シリアル値で処理することを覚えておくと、扱いやすくなります。
＜おまけ＞
ようやく晴れました。
気温も高いので走りに行きます。
【セミナー情報】
Excelセミナーを開催します。
”実務で使えるExcel入門セミナー 基礎編”