表示形式がバラバラで見にくい!
また日付の入力ミスしちゃった…
日々の業務で、Excelの日付操作に苦労していませんか?
日付は、ただの記録ではなく、業務効率化やデータ分析の鍵を握る重要な要素です。
- 入力ミスを劇的に減らし、正確な日付データを入力する方法
- 表示形式を統一し、誰が見ても分かりやすいデータにするコツ
- 複雑な日付計算も、関数を使ってサクサクこなすテクニック
この記事では、日付操作の基本から応用まで、具体的な操作方法やビジネスシーンでの活用例を分かりやすく解説します。
読み終わる頃には、あなたも日付操作のエキスパートに!時間と手間を大幅に削減し、より質の高い仕事ができるようになるでしょう。
さあ、一緒に日付操作の達人を目指しませんか?
日付データの基本と入力テクニック!ミスをなくす第一歩
「そもそも日付データって何?」基本の「キ」と正しい入力のお作法
日付データ。普段何気なく使っているけれど、扱いに困った経験のある方も多いのでは?
思った形式にならない。毎回手入力がめんどくさい。入力ミスをしてしまう。
こういった問題を解決して日付データを正しく扱うためには、基本概念と正しい入力形式を理解することが大切です!
シリアル値とは
コンピュータって、実は日付を直接理解できないんです。そこで登場するのが「シリアル値」。
これは、日付をコンピュータが理解できる数値に変換したものなんです。
1900年1月1日を「1」として、1日経つごとに1ずつ加算される仕組みです。例えば、2023年10月26日のシリアル値は45220。日付は、裏側ではこんな風に数字として管理されているんですね。
日付として認識される入力方法
日付を正しく入力するには、いくつかのルールがあります。
「yyyy/mm/dd」形式(例:2023/10/26)
「yyyy-mm-dd」形式(例:2023-10-26)
「yyyy年mm月dd日」形式(例:2023年10月26日)
これらは全て日付として認識されます。
間違った入力形式と対処法
では、間違った入力をしてしまったらどうなるのでしょう?
例えば、全角数字で入力したり、スラッシュやハイフン以外の記号で区切ったり、日付の順序を間違えたりすると、Excelは日付として認識してくれません。こんな時は、どうすればいいのでしょうか。
まず、入力し直すのが一番確実です。正しい入力形式で再度入力してみましょう。
もし、たくさんのセルで間違えて入力してしまっていたら、表示形式の変更や関数による変換が便利です!
文字列として認識された日付を「日付形式」に変更したり、DATEVALUE関数を使って文字列を日付に変換したりすることができます。
例えば、「2023年10月26日」と全角で入力してしまった場合、DATEVALUE関数を使って
=DATEVALUE(A1)
と入力すればOK。
また、「20231026」と区切り文字なしで入力してしまった場合は、
=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))
という関数で日付データに変換できます。
ちょっと複雑に見えるかもしれませんが、慣れれば簡単ですよ!
「毎回入力するのが面倒…」連続データをササッと入力する時短ワザ
日付データを連続で入力する場合、一つ一つ手入力するのはめちゃくちゃ大変ですよね…。
私も最初は、便利な機能があることを知らず手入力していました。すごく時間がかかってました。ウンザリでしたね…(遠い目)
だからこそ、同じウンザリを感じる人が減るように、入力スピードが劇的にアップする方法を皆さんにもご紹介したいと思います!
オートフィル機能の活用
まず、オートフィル機能。使ってますか?
これは、連続するデータやパターン化されたデータを自動的に入力してくれる優れものです。
やり方は超簡単ですよ。例えば、A1セルに「2025/1/1」と入力したとします。
入力されたセルの右下にカーソルを持っていくとプラスマークに変化するので、そこを押してドラッグしましょう。あっという間に連続した日付の完成です。
ショートカットキーと関数の組み合わせ
あとは、ショートカットキーと関数の組み合わせ技があります。
「Ctrl + ;」で今日の日付を一瞬で入力できますし、ROW関数を使えば、連番を作成して基準となる日付に加算することで、連続した日付を入力することも可能です。
例えば、A1セルに基準日「2025/1/1」を入力し、A2セルに
=$A$1+ROW()-1
と入力します。
これをA10セルまでコピーすると、A2セルからA10セルに連続した日付が入力されます。ROW関数は行番号を返すので、これを利用して1ずつ増加する数値を生成し、基準日に加算しているんです。
日付の表示形式を自由自在に!見やすさもバッチリ
日付データは入力するだけでなく、表示形式も重要です。
見やすい表示形式を使うことで、データの理解度が格段に向上しますよ。
「表示形式って、どう変えるの?」基本操作と見やすい書式の選び方
日付の表示形式を変更したいなら「Ctrl+1」で書式設定ダイアログボックスを開き、「表示形式」タブを選択しましょう。
「日付」カテゴリには、様々な表示形式が用意されています。
定義済みの表示形式の選択
定義済みの表示形式が良いなら、「日付」カテゴリから目的に合った表示形式を選ぶだけ。
表示形式のユーザー定義
定義済みの表示形式でぴったりのものがない…困ったぞ
大丈夫です。ご安心ください。
「ユーザー定義」を選べば、表示形式を自由にカスタマイズできます。
yyyy | 西暦4桁 |
yy | 西暦2桁 |
ggge | 和暦 |
ge | 和暦の略称 |
m | 月 |
mm | 月2桁 |
d | 日 |
dd | 日2桁 |
aaa | 曜日略称 |
aaaa | 曜日 |
これらの記号を組み合わせて、オリジナルの表示形式を作成できます。
例えば、「R7.1.1(水)」と表示したい場合は、「ge.m.d(aaa)」と定義しましょう。
↓こちらの記事もおススメです!
日付計算のエキスパートになろう!複雑な計算もラクラク
「日付の計算」って聞くと、なんだか難しそうに感じるかもしれませんね。
でも、コツさえ掴めば大丈夫。基本的な計算から複雑な計算まで、一緒にマスターしていきましょう!
「え、日付も計算できるの?」足し算・引き算から期間計算まで
日付データはシリアル値として扱われるため、実は足し算や引き算ができるんです。
例えば、ある日付に10日後を計算したい場合は、その日付に10を足すだけ。
セルA1に「2025/1/1」が入力されているとして、10日後の日付をB1に表示したい場合は、B1に「=A1+10」と入力すれば、「2025/1/11」と表示されます。
5日前なら「=A1-5」と入力するだけ。超簡単ですよね!
そして、より複雑な計算がしたいなら関数が便利です。
・DATE関数
・YEAR関数
・MONTH関数・
DAY関数
・TODAY関数
これらの関数を組み合わせることで、様々な日付計算が効率的に行えます。
例えば、A1に「2025/1/1」が入力されているとして、その5年後の日付をB1に表示したい場合は、
=DATE(YEAR(A1)+5,MONTH(A1),DAY(A1))
と入力します。
YEAR関数で年を取り出し、それに5を足し、DATE関数で新しい日付を生成しているという流れですね。
また、「=TODAY()」と入力すれば今日の日付が常に表示されるので、例えば納期までの日数を計算する際に
=DATE(2025,1,1)-TODAY()
と入力すれば、今日から2025年1月1日までの残り日数を常に表示できます。
これは便利ですよ!しゃがめない時の靴ベラくらい便利ですねw
「曜日とか、月末とか…計算面倒!」曜日や特定の日をパパッと計算するコツ
曜日や月末の計算、手計算だと面倒ですよね。でも、Excelの関数を使えば、あっという間に計算できるんです。
曜日計算の基本
曜日を割り出すには、TEXT関数やWEEKDAY関数が便利です。
まず、TEXT関数は数値を指定した表示形式の文字列に変換します。
例えば、
=TEXT(A1,"aaaa")
と入力すると、A1セルに入力された日付に対応する曜日が表示されます。A1に「2025/1/1」が入力されている場合は「水曜日」と表示されます。
また、数値で曜日を取得したい場合はWEEKDAY関数を使い、
=WEEKDAY(A1)
と入力しましょう。日付に対応する曜日を数値で返してくれます(日曜が1、土曜が7)。
単純に曜日を知りたいだけならTEXT関数で十分ですが、WEEKDAY関数を使うと以下のようなメリットがあります。
WEEKDAY関数を使うメリット
それは、曜日に関する、より柔軟な処理や計算が可能になるということです。
WEEKDAY関数を使うことで、曜日を単に表示するだけでなく、曜日を数値として取得できるため、以下のような応用が可能になります。
このように、TEXT関数だけでも曜日を割り出して表示することはできますが、WEEKDAY関数を使うことで曜日を数値として取得し、それに基づいて様々な処理や計算を行うことができるようになります。
特定の日付(月末、特定曜日など)の算出
月末を計算するには、EOMONTH関数を使いましょう。
例えば、
=EOMONTH(A1,0)
と入力すると、A1セルに入力された日付の月の末日が返されます。
また、特定の曜日を計算したい時には、WEEKDAY関数とIF関数を組み合わる方法があります。
WEEKDAY関数は「指定された日付が週の何日目にあたるか」を数値で返しますので、WEEKDAY 関数の第二引数に 1 を指定すると、日曜日を 1、月曜日を 2、…、土曜日を 7 として返します。
この戻り値を利用して、IF関数で条件分岐を行うことができます。
- 例1: A1 の日付が日曜日の場合、”日曜日です” と表示し、それ以外の場合は “日曜日ではありません” と表示するPowerShell
=IF(WEEKDAY(A1,1)=1, "日曜日です", "日曜日ではありません")
- WEEKDAY(A1,1): A1 の日付の曜日を数値で取得します(日曜日が 1)。
- IF(WEEKDAY(A1,1)=1, …): WEEKDAY 関数の結果が 1(日曜日)と等しいかどうかを判定します。
"日曜日です"
: 条件が真(日曜日)の場合に表示する文字列です。"日曜日ではありません"
: 条件が偽(日曜日以外)の場合に表示する文字列です。
- WEEKDAY(A1,1): A1 の日付の曜日を数値で取得します(日曜日が 1)。
- 例2: A1の日付から見て、次の月曜日を求めるPowerShell
=A1+IF(WEEKDAY(A1,2)=1,7,IF(WEEKDAY(A1,2)=2,6,IF(WEEKDAY(A1,2)=3,5,IF(WEEKDAY(A1,2)=4,4,IF(WEEKDAY(A1,2)=5,3,IF(WEEKDAY(A1,2)=6,2,IF(WEEKDAY(A1,2)=7,1)))))))
- A1: A1の日付
- WEEKDAY(A1,2): A1の曜日を数値で取得します(月曜日が1、日曜日が7)。
- IF(WEEKDAY(A1,2)=1,7, …): A1が月曜日の場合、7日後(次の月曜日)を計算します。
- IF(WEEKDAY(A1,2)=2,6, …): A1が火曜日の場合、6日後(次の月曜日)を計算します。
- 水、木、金、土 も同様に。
- IF(WEEKDAY(A1,2)=7,1, …): A1が日曜日の場合、1日後(次の月曜日)を計算します。
A1+ ...
: A1の日付に、上記のIF関数で計算された日数を加算することで、次の月曜日を求めます。
- A1: A1の日付
- 例3: A1の日付から見て、次の月曜日を求める(別パターン)
例2の関数だと長くて見づらいので、もっと可読性を上げたいならCHOOSE関数を使用する方法もあります。
PowerShell=A1+CHOOSE(WEEKDAY(A1,2),7,6,5,4,3,2,1)
- A1: A1の日付
- WEEKDAY(A1,2): A1の曜日を数値で取得します(月曜日が1、日曜日が7)。
- CHOOSE(WEEKDAY(A1,2),7,6,5,4,3,2,1): WEEKDAY関数の結果に応じて、次の月曜日までの日数を返します。
- 月曜日(1)なら7日後
- 火曜日(2)なら6日後
- 水曜日(3)なら5日後
- 木曜日(4)なら4日後
- 金曜日(5)なら3日後
- 土曜日(6)なら2日後
- 日曜日(7)なら1日後
- A1+CHOOSE(WEEKDAY(A1,2),7,6,5,4,3,2,1): A1の日付に、CHOOSE関数で得られた次の月曜日までの日数を加算することで、次の月曜日を求めます。
- A1: A1の日付
- 補足
- WEEKDAY 関数の第二引数を変更することで、週の始まりを日曜日以外に変更できます。
- IF関数の中にさらにIF関数を入れ子にすることで、より複雑な条件分岐も可能です。
- CHOOSE関数やSWITCH関数など、他の関数と組み合わせることで、より柔軟な処理を行うこともできます。
- WEEKDAY 関数の第二引数を変更することで、週の始まりを日曜日以外に変更できます。
営業日計算とネットワーク関数
土日祝日を除いた営業日数を計算するには、NETWORKDAYS関数やWORKDAY関数が便利。
NETWORKDAYS関数は開始日から終了日までの営業日数を、WORKDAY関数は開始日から指定した営業日数後の日付を計算します。
また、祝日リストを指定することも可能ですよ!
例えば、
・A1に開始日「2025/1/1」
・A2に終了日「2025/1/31」
・A3に祝日「2025/1/13」
が入力されているとしましょう。
A1からA3までの営業日数を計算したい場合は、
=NETWORKDAYS(A1,A2,A3)
と入力すると「22」が返されます。
また、A1から15営業日後の日付を計算したい場合は、
=WORKDAY(A1,15,A3)
と入力すると「2025/1/23」が返されます。
セルの表示形式を「標準」と「日付」で使い分けないと数字の羅列になってしまうため、ご注意ください!
日付関連関数を使いこなそう!ワンランク上のデータ処理
Excelの日付関数は、単独で使うだけじゃなく他の関数と組み合わせることで、もっともっと便利に使えるんです!
「関数って難しそう…」主要な関数と使い方を超シンプルに解説
TODAY関数、NOW関数
TODAY関数は今日の日付を、NOW関数は今日の日付と現在時刻を返します。
帳票に自動で今日の日付を入力したり、経過時間を計算したりする時に便利ですよ。
YEAR関数、MONTH関数、DAY関数
YEAR、MONTH、DAY関数は、日付データからそれぞれ年、月、日を取り出します。日付データを分析したり、集計したりする際に役立ちます。
A1に「2025/1/1」と入力されている場合、B1に=YEAR(A1)、C1に=MONTH(A1)、D1に=DAY(A1)と入力すると、それぞれ「2025」「1」「1」と表示されます。
DATE関数、EDATE関数、EOMONTH関数
DATE関数は指定した年月日で日付を生成、EDATE関数は指定した月数前後の日付を、EOMONTH関数は指定した月数前後の月末の日付を返します。
基準日から特定の日付を計算したり、月末の日付を一覧表示したりする際に使えますね。
例えば、6ヶ月後の日付を求めたい場合は、A1に=TODAY()と入力し、B1に=EDATE(A1,6)と入力すればOK。
「こんな時どうする?」関数を使って、もっと便利に日付を操作する方法
年齢計算
生年月日から年齢を計算するには、DATEDIF関数を使いましょう。
例えば、
=DATEDIF(A1,TODAY(),"Y")
と入力すると、A1セルに入力された生年月日から今日までの年齢が年単位で表示されます。
勤続年数の計算
勤続年数もDATEDIF関数で計算できます。
A1に入社日「2020/04/01」が入力されている場合、
=DATEDIF(A1,TODAY(),"Y") & "年" & DATEDIF(A1,TODAY(),"YM") & "ヶ月"
と入力すると、今日時点での勤続年数が「〇年〇ヶ月」の形式で表示されます。
期限管理
EDATE関数やWORKDAY関数を使えば、特定の日付から〇ヶ月後や〇営業日後の期限を計算できます。
A1に開始日「2025/1/1」が入力されている場合、2ヶ月後の期限はB1に
=EDATE(A1,2)
10営業日後の期限(祝日はC1セルにリストされているとする)は
=WORKDAY(A1,10,C1)
と入力すれば計算できますよ。
「もっと複雑な計算もできる?」関数を組み合わせて、日付処理の達人に
条件付き書式と日付関数
条件付き書式と日付関数を組み合わせれば、期限切れの日付を赤く表示したり、今日の日付を強調表示することもできます。
- ①期限が入力されているセル範囲を選択
- ②条件付き書式で「数式を使用して、書式設定するセルを決定」を選択
- ③数式に入力→書式で赤を設定→OKPowerShell
=A1<TODAY()
- ④完成!
↓こちらの記事もおススメです!
日付操作をもっと便利に!自動化とトラブル解決
「もっとラクしたい!」日付操作を自動化して、時間を有効活用する方法
手作業で入力したり、計算したり…。時間がかかるうえにミスも発生してたら、やってられないですよね。
マクロやVBAを使うことで、日付操作を自動化できるため、作業効率が格段にアップしますよ!
マクロ記録機能による日付操作の自動化
マクロ記録機能を使えば、一連の操作を記録して、後から繰り返し実行できます。
この機能はVBAと違ってプログラミング知識が不要で、イメージとしては「録画」して「再生」するような感じですね!
例えば、
・特定の形式で日付を入力する
・複数のシートに同じ日付を入力する
・日付データを別のシートに転記する
といった操作を自動化できます。
複数のシートに今日の日付を入力するマクロを記録するには、「開発」タブのマクロの記録を開始し、シートを切り替えながら今日の日付を入力する操作を行い、記録を停止します。
このマクロを実行すれば、記録された操作が再現され、複数のシートに今日の日付が自動的に入力されますのでお試しあれ。
VBAによるカスタム日付関数の作成
VBAを使えば、Excelの機能を拡張し、自分だけのカスタム関数を作成することも可能です。
例えば、特定の条件で日付を計算する関数や、日付データを整形する関数など、自分のニーズに合わせた関数を作ることができます。
「エラーが出た!どうしよう…」焦らず解決!よくあるエラーとその対処法
日付操作中にエラーが発生すると焦ってしまいますよね。
でも、落ち着いて対処すれば大丈夫。よくあるエラーと対処法を覚えておけば、いざという時に役立ちますよ!
シリアル値エラー
日付データが正しくシリアル値として認識されない場合に発生します。
例えば、
・文字列として入力された日付を計算に使用
・日付形式ではないデータを日付形式に変更したりした
場合などに起こりがちです。
対処法としては、DATEVALUE関数で文字列を日付に変換したり、表示形式を日付形式に変更したりするなどが有効です。
表示形式エラー
表示形式が正しく設定されていない場合に発生します。
日付が「#######」と表示されたり、意図しない形式で表示されたりします。
対処法は、表示形式を正しい形式に変更したり、列幅が足りない場合は列幅を調整したりするだけです。
関数エラー
日付関数の引数が正しくない場合に発生します。
例えば、DATE関数の引数に文字列を指定したり、存在しない日付を指定したりするとエラーになります。
対処法は、関数の引数を確認し、正しい値を指定すること。関数のヘルプを参照したり、インターネットで検索したりするのも良いですね。
まとめ
この記事では、Excelの日付操作について、基本から応用まで幅広く解説しました。
入力ミスを防ぐテクニック、表示形式の統一、複雑な日付計算、関数活用、そして自動化とトラブルシューティング。
これらの知識とスキルを習得することで、日々の業務効率が格段に向上し、データ分析の精度も高まることを実感いただけたのではないでしょうか。
- 日付データの基本と入力テクニック
- 表示形式のカスタマイズと統一
- 日付計算の関数活用
- 自動化とエラー対処
特に、関数とマクロを組み合わせることで、日付操作は飛躍的に効率化されます。
具体的なアクションステップとしては、まずこの記事で紹介した基本的な入力方法や表示形式を試してみることから始めましょう。
次に、簡単な日付計算の関数をいくつかマスターし、徐々に複雑な計算にも挑戦してみてください。そして、最終的にはマクロやVBAを活用して、日付操作の自動化を目指しましょう。
この記事で紹介した知識とテクニックを駆使することで、時間と手間を大幅に削減し、より意味のある仕事に集中できるようになります。そして、サクッと仕事してパパッと帰りましょ!
この記事が、あなたのExcelスキル向上と業務効率化の一助となれば幸いです。最後までお読みいただき、ありがとうございました!
「帰宅したら23:00か…」
毎日の残業、本当に仕方ないことなのでしょうか?
「帰宅すると家族は寝てる…」
「冷たくなったご飯を1人で食べて寝るだけ…」
「自分の時間なんて一切ない…」
ガマンし続けたその先に、あなたの望む未来はホントにありますか?
私が経験した驚きの変化
すべては、ある”気づき”から始まりました
大手企業の管理職として10年。
最初はエクセルの知識ゼロから始めた私。
そんな私でも、ある方法に出会い、驚くほどの「ヒマ」を手に入れることができました。
※このページは近日中に非公開となります