Googleスプレッドシートで使える日付の関数・シリアル化をご紹介

今回は、日付の関数やシリアル化についてご紹介していきます。GoogleスプレッドシートやExcelで日付の処理を行う事は多いですよね。しかし、便利な関数はたくさんあるの中、活用できていない人が多くいる現実。知らなくて使っていなかったなんてもったいないですよ！

日付の用途別関数の紹介

仕事に役立てられる、便利で簡単な日付の関数を紹介します。ちなみに、GoogleスプレッドシートとExcelで活用できますよ。

現在の日付や時刻を求める

①　NOW()
現在の日付と時刻を表示させます

②　TODAY()
現在の日付を表示させます

ある日付の一部のみを抜き取り、表示させる

①　YEAR(日付)
()に日付を入れると表示させたい年を表示させます

②　MONTH(日付)
()に日付を入れると表示させたい月を表示させます

③　DAY(日付)
()に日付を入れると表示させたい日を表示させます

④　WEEKDAY(日付,[種類])
()に日付を入れると表示させたい曜日を表示させます

何か月後の日付を求め、表示させる

①　EDATE(開始日,月数)
例えば、下記のような場合があります。
・前月の同じ日付を求める　→　DATE(TODAY(),-1)
・来月の同じ日付を求める　→　EDATE(TODAY(),1)
・1年後の同じ日付を求める　→　EDATE(TODAY(),12)

末日を求め、表示させる

①　EOMONTH(開始日,月数)
・前月末を求める　→　EOMONTH(TODAY(),-1)
・今月末を求める　→　EOMONTH(TODAY(),0)
・来月末を求める　→　EOMONTH(TODAY(),1)

上記で紹介した関数は、仕事を行う上で使用する機会が多くあります。スプレッドシートやExcelでも同じように使えますので、マスターしましょう。

Googleスプレッドシートを知らない方は、下記の記事で紹介していますので、ぜひご覧ください。
参考記事：使っている人が以外と少ない⁈Googleスプレッドシートをご紹介

日付の表示のシリアル化について

エクセルやGoogleスプレッドシートで「20160101」などと入力し表示形式を日付に変更すると「57096/06/23」と表示される経験はありませんか？そのような経験がある方にむけて日付のシリアル化について説明します。

シリアル値とは、日付を数値に置き換えたものです。エクセルの日付データは1900円1月1日を『１』としたシリアル値で管理されています。

例えば、

1900年1月1日　→　1（シリアル値）

1900年1月2日　→　2（シリアル値）

1900年1月3日　→　3（シリアル値）

というように数字で管理しています。

 

最近の日付ですと、

2016年6月1日　→　42522（シリアル値）

2016年6月2日　→　42523（シリアル値）

2016年6月3日　→　42524（シリアル値）

2016年6月4日　→　42525（シリアル値）

2016年6月5日　→　42526（シリアル値）

というようになります。

シリアル値のメリット

シリアル値の最大のメリットは、日付の１日前と１日後の計算がとても簡単なところです。シリアル値がない状態で１日前の日付と１日後の日付を計算することは自力で複雑な関数を組む必要があり、とても大変です。エクセルのシリアル値の仕組みを使うことで、エクセルが勝手に全部計算してくれます。

大変な作業は、シリアル値を使って全部エクセルやスプレッドシートに行ってもらいましょう。

日付を選択してもシリアル化する理由

それでは本題に入り、日付がシリアル化していまう時の改善方法についてご説明していきたいと思います。上記で説明した通り、エクセルやGoogleスプレッドシートでは日付に「シリアル値」という番号をつけて計算しています。1990年1月1日を「1」としてそこから番号を振り分けているため…1990年1月2日は「2」というよう表示されます。

なので1990年1月1日から「20160101」たった日付⇒「57096/06/23」といった表示になってしまうのです。

関数を使用してまずは日付にしてみる

関数を利用して日付にする方法を試してみましょう。

代表的な関数は「DATE」

=DATE(2016,1,1)とセルに入力すれば表示は「2016/01/01」となります。連番にするのであれば、先ほどの=DATE(2016,1,1)が入力されているセルの番号に「+1」としていくと次のセルには「2016/01/02」と表示されます。

例えば・・・

=A1+1　のような数式です。もしくはセルに「2016/01/01」と入力していただければ大丈夫なのですが…レポートをとってきた場合に「20160101」となってしまっているところは使えません。

「TEXT」関数が使える！

レポートなどからデータをコピーして貼り付けた場合に、「20160101」となっている場合はありませんか？それを日付にしたい！そんなときに使用できる関数が「TEXT」です。

=TEXT（A1,”0000!/00!/00”)*1と入力して、表示形式を日付にすることで「2016/01/01」と表示させることができます。

ポイントは「！」や「*1」を使用することで、エクセルやGoogleスプレッドシートが日付をシリアル値ではなく数値データとして扱ってくれることです。数値データとして扱うので「0000/00/00」だけだと割り算になってしまうので、「！」や「-」「”」を使用することで割り算をするのを防いでくれます。

ちなみに、TEXT関数を使用するとシリアル値を日付の文字列に変換することができます。セルの書式設定で日付を指定します。

Googleスプレッドシートの場合

エクセルの場合だと上記の

=TEXT（A1,”0000!/00!/00”)*1で大丈夫だったのですが、Googleスプレッドシートの場合はこれでは上手くいきません。

Googleスプレッドシートでは

=TEXT（A1,”0000””/””00””/””0”)*1

もしくは、

=TEXT(A1,”0000-00-00”)*1

とすることで上手くいきます。

関数を活用しても上手くいかない場合は…

無理やりですが、文字を結合するといった関数で「MID」関数を使用するやり方もあります。

=(MID(A1,1,4)&”/”&MID(A1,5,2)&”/”&MID(A1,7,2))*1

このようにA1に入力されている「20160101」の1文字目から4つ数字をとってきなさい。といった関数になります。それを＆で”/”をくっつけて日付に見せるといったやり方になります。または、一度テキストエディタなどで編集するといったやり方も…少し手間ですが、どうしても変換したい場合には一度試してみて下さい。

日付の関数やシリアル値を活用しよう

Excelでは使えますが、Googleスプレッドシートでは使えない関数などもあります。表計算ソフト別の使い方を十分に理解し、参考にしていただければ幸いです。各地でエクセルの使い方のセミナーも開催しているみたいなので、参加してみるのも良いでしょう。