日時に関する関数を並べます。
勤怠管理の出勤簿で業務時間や日時などの自動計算を行う場合に割と使用することも多いです。
名前の通り、年・月・日を返す関数です。
引数には年・月・日のシリアル値である必要があります。
Excelは日付形式で入力した年・月・日をシリアル値として解釈するので、その使い方でも十分機能します。
A1＝2011/3/11
=YEAR(A1)
→2011
=MONTH(A1)
→3
=DAY(A1)
→11
今度は引数として入力した年・月・日から、それに対応するシリアル値を返す関数。
シリアル値はセルの形式に基づいて表示値を変えることもある。
DATEの引数は年・月・日とで分かれている。
=DATE(2011,3,11)
DATEVALUEの引数は日付形式として認識できれば問題ない(文字列として入力する)。
=DATEVALUE("2011/3/11")
日付のシリアル値はWinなら1900年日付システムを採用しているため1900年1月1日を1とする値として計算されるが、
MacOSの場合は1904年日付システムを採用しているため1904年1月1日を0として計算するため、
WinとMacOSとでは日付が4年と1日(1,462日)もずれることになる。
最新版ではこれに合わせてExcelのオプション設定から1904日付システムを使用するかどうか選択できるため、
これで異OS間でのデータのやり取りをしても値はずれなくなる。
ただし、DATEやDATEVALUE関数の引数にした数値や文字列についてはあくまで定数なので、
関数の返り値も1900年日付システムやら1904年日付システムやらを直接気にする必要なく扱える。
現在の日時を返す。そのため、この関数の返り値は可変値となる。引数は不要。
なお、具体的にはTODAYの場合は現在の日付のみ、NOWは日時(つまり時間も)返す。
NOWは時間も含めて値を返すことから、HOUR等を使用する場合は意味を帯びてくるけれども、時間系の関数についてはここでは触れない。
=TODAY()
=NOW()
TODAY の値はもちろんDATEVALUE、つまり、日付のシリアル値になるのだが、
可変値であることが幸いし、処理系によって実際の値が変わるため、
1900年日付システムやら1904年日付システムやらを直接気にする必要はない。
和暦をバカまじめに入力するという手もありますが、自動入力のほうがいい場合もあります。
特に、私のように、履歴書をExcelで作っている場合なんかは特に。。。
Excel関数を駆使して和暦の作る場合は要は簡単な話で、西暦も変われば和暦も変わるハズなので、これを利用するだけのことです。
考え方として、平成何年や昭和何年が西暦何年なのかを細かく覚えている必要はない。
だいたい覚えているとしたら、今年が西暦何年で和暦何年なのかぐらいは把握できているハズだろう。
または、自分が生まれた年など、自分なりのアニバーサリーを覚えているということも考えられる。
このページの編集時期時点である平成最後の夏では、ホットな話題として年号が変わるという話があるので、
新元号ではそれ基準にするというのは言うまでもないと思う。
ということで、令和は以下のようにして求める。
西暦2019年＝令和元年(1年)
見ての通り、西暦と令和とでは数値の差が2018もあるので、YEAR関数に2018を引いた値が令和になるということがわかる。
つまり、
="令和"&YEAR(TODAY())-2018&"年"
ということになる。
今後の参考のために、単に今年の西暦・和暦を利用して和暦を導き出す方法を紹介。
西暦2018年＝平成30年
年数の差分は、ここでは考える必要はない。というのも、そのためのExcelなので、予め答えを考えておくこと自体がナンセンスなのである。
つまり、2018を引いて30を足せばいいという事実をありのままに書けばいいだけなので、
="平成"&YEAR(TODAY())-2018+30&"年"
経歴書などで必要ならVLOOKUP関数などを使って、年号と各年号の元年の値の対応表から値を引っ張り出して自動計算させる表を作り出すのもいいかもしれない。
2つの日付の差を求める関数としてDATEDIF関数というのがありますが、現行のExcelでは互換性を保つためにあるだけの存在らしく、
今後はサポートせず、もしかしたら、将来的に消えていく可能性もあります。
それゆえか、ヘルプや関数の予測入力候補にも出てこず、手動で入力していくしかありません。
そういったことで、ここではDATEDIF関数を使用しないで日付の差を求める、身近な例として、自分の年齢を自動計算することにしてみます。
考え方は、やっぱり年齢ということなので生年月日の入力が必要となる。
つまり、DATE関数を使って生年月日と今日の日付との差分を求めることで日数のシリアル値が求められるということになる。
ただ、見ての通り、シリアル値が出るという問題があるため、それを解決する必要も出てくることになる。
また、算出するのは年齢なので、YEAR関数を利用する。
実際に、2011/3/11が誕生日の人の計算式を作ってみた。
=YEAR(TODAY()-DATE(2011,3,10))-YEAR(0)
シリアル値からの日付計算を行う関数を使用する都合、つまり、関数の説明にもあるように、
1900年日付システムの場合は1900/1/1を1とした値であるという点に注目されたい。
つまり、この1900の値をどうにかする必要があるため、計算結果に1900を引く必要が出てくる。
しかし、1904年日付システムの場合は1904が起点であることに注意。
ただ、いずれにせよ、シリアル値の起点となる、”シリアル値が0の時の値を引けばいいだけ”なので、
どちらの日付システムでもYEAR(0)を引くだけで話が終わるのである。
また、見ての通り、誕生日の日の前日を引いているけれども、当日にするとシリアル値の差分的に年数の桁がちょうど繰り上がらないので、前日を入力にしている。
もうひとつは、DAYS関数を使うことでも実現可能。
これは、2つの引数となる”終了日”と”開始日”の差分を日にちで返すため、考え方は上で紹介している方法と一緒です。
唯一注意なのは、引数1が”終了日”、引数2が”開始日”であることに注意。逆にしてしまうと負の値になってしまいます。
=YEAR(DAYS(TODAY(),DATE(2011,3,10)))
やっていることは上とほとんど変わっていません。
カレンダーを作る場合は簡単で、例えば2011年3月のカレンダーを作る場合、
2011年3月1日のセルから値を1ずつインクリメントしていくだけという簡単なお仕事となります。
もちろん、3月の暦なので31日まであるが……
問題は月末が何日になるかということです。
一番長い月では31日もありますが、4月は30日まで、2月は最も短く28日までしかない。
しかもその翌年2012年はうるう年なので2月29日まであるという始末。
月毎カレンダーを作るにあたり、1ずつインクリメントするだけのカレンダーを毎年毎月同じフォーマットにしてしまうと、
翌月1～3日の値まで表示されてしまい、これでは格好が悪い。
そのため、それを月末処理まできちんとしているカレンダーが作れると格好も付くと思います。
月末処理のやり方については2種類ありますが、まずは昔ながら方法。
昔はこれしか方法がなかったので。。。
=IF(DAY(前日)>DAY(前日+1),"",前日+1)
DAY関数を用いてシリアル値に対応する日付のみを取得し、
日付の値が少なくなったら(たとえば4月30日の次が5月1日になった場合など)表示しないようにするというアナログな手法。
28日までなら「=前日+1」で十分だが、29日以降のセルからこの値を放り込んでおくことで実現する。
もう一つは最近のExcelでひそかに実装されているエンドオブMONTH関数を使う方法。
いずれにせよ、IFによる比較が必要となる。
=IF(EOMONTH(当日,0)<前日+1,"",前日+1)
当日のシリアル値に該当する月(つまり当月)の月末よりも大きな値になる場合は非表示、それ以外は表示というもの。
EOMONTH関数は最近のExcelなら使用できる関数のため、この方法で実現可能。
=EOMONTH(開始日,月)
関数の説明が何気にわかりにくいけど、とにかく、「開始日」から「月」に書いた数値分だけ月を足し、
その月の月末がいつなんだっていうことである。
=EOMONTH("2011/3/11",2)
この場合は2011年3月11日の2か月後の月末なので、2011年5月31日のシリアル値が得られることになる。
=EOMONTH("2011/3/11",0)
要するに、当月の月末にしたければ0にすればいいだけの話。