【Excel関数小技】日付や曜日表示を変更・書き換えできる便利な関数まとめ

データベースを数多く取り扱っているビデオリサーチでは、日々データの収集や分析・解析業務を行っています。その際に欠かせないのがエクセルです。データを扱いやすい形に整える作業はとても大事で効率的に進めたいものです。このシリーズでは、当社の日々の業務で使用している【エクセル関数】を使った『小技』を紹介します。

今回ピックアップするのは「日付や曜日に関する関数」です。
Excelで業務をしている際には、売上管理やプロジェクトの進捗管理、データベースの整備の際など「日付」や「曜日」に関する情報を入力するシーンが多数あります。
本記事では、そんな作業の際に知っていると手間が省ける便利な関数をいくつかまとめて事例形式でご紹介します。

1．日付に関する関数

数字や文字列を日付に変換できるDATE関数・DATEVALUE関数

何らかの理由で、以下のように「年」「月」「日」でそれぞれセルを分けてまとめているデータがあったとき、1発で書式設定を「日付」に変換することができるのがDATE関数です。

＝DATE(A4,B4,C4)
=「年」が入力されているセルA4、「月」が入力されているセルB4、「日」が入力されているセルC4を、1セルにまとめた「日付」に変換する

DATE関数を使用すると、「yyyy/mm/dd」という形式で日付が表示されます。

バラバラに入力されている「年」「月」「日」をDATE関数で「日付」にまとめることで、期間や生年月日に基づいた年齢などのさまざまな計算や、名簿の体裁を整える場面などで活用することができます。

また、データベースの入力ルールの都合などが理由で、日付をわざと「文字列」として格納しているケースもよくあります。この場合、書式設定を「文字列」⇒「日付」に変換できるのがDATEVALUE関数です。

＝DATEVALUE（A4)
＝セルA4の書式を、文字列から日付に変換する

上記の例では該当するセルをかっこ内で選択するだけなので、簡単に変換することが可能です。
更に、DATEVALUE関数は複数の文字列セルを組み合わせて変換をかけることもできます。
以下は応用例です。

=DATEVALUE(LEFT(E$1,6)&A4&"日")
＝セルE1の左から6文字分（年・月）とセルA4（日）をまとめ、日付に変換する

この例では、毎月同じ「日」にセールを行うので、1行目に記載した「23年●月のセール開催日」の「23年●月」部分を書き換えると自動的に直下の「日付」も該当年月になるよう関数を組んでいます。

DATEVALUE関数が正常に動作していれば、日付として正しくない組み合わせはきちんとエラーとして表示されます。
上記の例では、「30日」「31日」は1月ではきちんと日付になっていますが、2月ではエラー「♯VALUE!」となっていることが分かります。

なお、DATEVALUE関数利用時のポイントは、「年」「月」「日」の"表記"をそろえることです。
この例の場合、セル単体で見ると、セルE1の「年」「月」は「23年」「2月」となっていますが、セルA4の「日」は「1」と数字の状態です。
そこで、セルA4には"日"をつけ、「1日」としてあげることで正しくDATEVALUE関数が稼働します。

日付から年だけ・月だけ・日だけを抜き出すYEAR関数・MONTH関数・DAY関数

関数の使い方の説明に入る前に、基礎知識であるExcelにおける数字のカウント機能について触れておきます。

Excelは1日24時間を、数字の「1」として管理しています。それを私たちに分かりやすいよう、日付という見た目に変換して見せているだけなので、「2023/10/1」という日付は、Excelから見ると「45200」というただの数字になります。

ちなみにこの数字のことをExcel内では「シリアル値」（「1900年1月1日」を「1」として、何日経過したかを示す数値）と呼びます。

そのためExcelは何もせずとも「2023/10/1」という日付を、年は「2023」で、月は「10」で、日は「1」だよという風に、即答してくれることはありません。

そこで登場するのがこれから紹介するYEAR関数・MONTH関数・DAY関数です。
これらの関数を使うことで指定した日付データから、年の部分の数字だけや、月の部分の数字だけ、日の部分の数字だけを取り出すことができるようになります。

＝YEAR（A4）
＝セルA4の「日付」から「年」だけを抽出する

＝MONTH(A4)
=セルA4の「日付」から「月」だけを抽出する

＝DAY(A4)
=セルA4の「日付」から「日」だけを抽出する

さらに、ここからはYEAR関数・MONTH関数・DAY関数に使える応用編を「YEAR関数」を用いて解説します。

例えばファンクラブの入会日を管理しているデータベースがあるとします。このデータを入会年ごとに集計する場合、YEAR関数を用いて入会年のみを抽出し年ごとの入会者数を明らかにすることができます。

日付から年ごとの人数や個数を抽出する場合、SUMPRODUCT関数とYEAR関数を組み合わせます。

=SUMPRODUCT((YEAR($F$4:$F$15)=I4)*1)
=セルF4:F15の範囲内で、セルI4の年が含まれている数を抽出する

SUMPRODUCT 関数とは、対応する範囲または配列の要素の合計を返す関数です。
YEAR関数・MONTH関数・DAY関数などと組み合わせることで、日付から年や月、日にちを条件にして集計することができます。

上図のようにI4セルに入っている2020年入会の人の数を求めたい場合、YEAR($F$4:$F$15)=I4とし、入会日から抽出した年がI4と等しいかどうかを判定します。
それに*1をして数値化をすると、人数を求めることができます。

今日の日付を自動で表示できるTODAY関数

自動で今日の日付を表示したいときに使うのがTODAY関数です。

=TODAY()
＝常に自動で今日の日付を表示する

TODAY関数には引数を入力する必要がなく、上の式を入力するだけで、自動で今日の日付を表示することができます。

＝TODAY()-1
＝常に自動で昨日の日付を表示する

＝TODAY()+１
＝常に自動で明日の日付を表示する

TODAY関数で表示した日付は、翌日に開くと翌日の日付で表示され自動で「今日」が更新される仕様になっています。
自動で更新をせず固定したいときには「=TODAY()」を入力後に《F9キー》を押すことで日付を固定することができます。

2．曜日に関する関数

日付だけの情報から曜日を取得できるTEXT関数

＝TEXT(A4,"aaaa(表示形式)")
＝セルA4から曜日を抽出する値を決められた表示形式を適用した文字列に変換する

数値を文字列に変換するときに用いるのがTEXT関数です。この例では日付をもとに「aaaa」という表示形式を入力することで、「曜日」を抽出しています。

そして、数値を文字列に変換する表示形式のパターンは数多く存在しています。
以下に、日付に関する表示形式をいくつか抜粋してご紹介します。

◆西暦4桁【例：2023】＝yyyy
◆西暦2桁【例：23】＝yy
◆和暦の年【例：5】＝e
◆和暦の元号【例：令和】＝ggg
◆月【例：1】＝m
◆日【例：12】＝d
◆曜日【例：木曜日】＝aaaa
◆曜日(短縮)【例：木曜】＝aaaなど

ほかにも「時間」や「数値」など、様ざまな表示形式があります。用途に合わせてその時に適した表示形式を選んでください。

曜日を1~7の数字で表示できるWEEKDAY関数

＝WEEKDAY(A4(日付を表すセル),"1(種類)")
＝セルA4が何曜日に当たるのかを求める

該当の日付が何曜日に当たるのかを求めることができる関数がWEEKDAY関数です。

WEEKDAY関数は、シリアル値（日付を表すセル）に対応した週の「種類」を数字で指定することで曜日を1~7の値で表します。

週の種類は1〜3､11〜17の数字で指定することができ、種類を「1」もしくは「省略」したときは日曜日が「1」、月曜日が「2」、火から土は順に「3〜7」の数値で表されます。

以下の表で「種類」と「曜日の数字」の関係を表しています。

あまりややこしく感じる必要はなく、種類の値は基本的に「１」または「省略」を使用する、と覚えておけば問題はありません。

続いて応用編としてWEEKDAY関数とIF関数を組み合わせて、スケジュールに「定休日」を追加する方法をご紹介します。

=IF(WEEKDAY($A4,2)>=6,"定休日","")
＝指定の曜日に「定休日」と表示する

この例では、とある店舗の営業スケジュールに定休日である土曜日と日曜日の欄に「定休日」の文言を追加しています。
前述のWEEKDAY関数では曜日番号を、日曜日を「1」、月曜日を「2」...土曜日を「7」としていましたが、この場合、土曜日が「7」、日曜日が「1」と数字が連続せず、関数を組みにくくなります。

そこで、土曜日と日曜日を連続する値にできるようWEEKDAY関数に入力する種類の値を「2」にしています。
こうすることで、土曜日が「6」、日曜日が「７」となるため曜日番号が6以上のときは定休日と表示するという条件式を作ることができます。

WEEKDAY関数は種類の値によって曜日番号の振り方が異なるため、土日以外が定休日であっても、上記の式の種類の値を変更するだけで条件式を作ることができます。

指定した日付が「今年の第何週目なのか」を求めるWEEKNUM関数

＝WEEKNUM(E4(日付),週の基準(1または省略))
＝セルE4が「今年の第何週目にあたるのか」を求める

「第一週」「第二週」と言うと月ごとの何週目かをイメージしますが、Excelには月の第何週目かを求める関数が存在しません。
ですが、その"年"の何週目にあたるのかを求めることができる関数はあります。それがWEEKNUM関数です。

WEEKNUM関数で求められるのは"年"ごとの週番号で１から52、または53週まであり、「週の基準」には対応する番号＝基準値を数字で入力します。

「週の基準」は開始日となる曜日に沿った数字を入力します。おおよその場合、週の初めを日曜日もしくは月曜日としていることが多いかと思います。

その場合、
日曜日を週のはじめとする場合の基準値は「１」もしくは「省略」
月曜日を週のはじめとする場合の基準値は「2」とします。

何曜日をはじまりとするか、それに付随する基準値は何になるかは以下のとおりです。
※前項でご紹介したWEEKDAY関数とは基準値の数字の使い方が異なります。

◆週の開始日：日曜日→週の基準：1or省略
◆週の開始日：月曜日→週の基準：2
◆週の開始日：火曜日→週の基準：12
◆週の開始日：水曜日→週の基準：13
◆週の開始日：木曜日→週の基準：14
◆週の開始日：金曜日→週の基準：15
◆週の開始日：土曜日→週の基準：16

しかし、必ずしも週の始まりが日曜日または月曜日とカウントするとは限りません。

例えば一週間の中で店休日が火曜日のお店があった場合、一週間の売り上げを店休日の翌日である水曜日〜翌週の火曜日で計算するためには、週の始まりを水曜日に設定することになります。この場合は週の基準値を「13」と設定します。

このように自由に週のはじまりの曜日を設定することができるのがWEEKNUM関数の特徴です。

いかがでしたか？
関数には無限の組み合わせや使い方があり、今回紹介した例はその一例にすぎません。
本連載は今後も不定期で更新予定です。日々の業務の効率化にお役立ていただけましたら幸いです。?