Excel チュートリアル: DateTime 計算 (差の計算、年齢、加算/減算)
Excel では、XNUMX つの日付/時刻の差を計算したり、datetime を加算または減算したり、誕生日に基づいて年齢を取得したりするなど、日付時刻の計算が頻繁に使用されます。 このチュートリアルでは、日時計算に関するほとんどのシナリオをリストし、関連するメソッドを提供します。
このチュートリアルでは、メソッドを説明するためにいくつかの例を作成します。以下の VBA コードまたは式を使用するときに必要な参照を変更できます。
1. XNUMX つの日付/時刻の差を計算する
XNUMX つの日付または XNUMX つの時刻の差を計算することは、毎日の Excel 作業で遭遇する日時計算の最も一般的な問題かもしれません。 提供されている以下の例に従うと、同じ問題が発生したときに効率を高めるのに役立つ場合があります。
1.11 XNUMX つの日付の差を日/月/年/週で計算する
Excel の DATEDIF 関数を使用すると、日、月、年、および週の XNUMX つの日付の差をすばやく計算できます。
詳細については、クリックしてください DATEIF function
XNUMX つの日付間の日数の差
セル A2 と B2 の XNUMX つの日付の日数差を取得するには、次の式を使用してください。
=DATEDIF(A2,B2,"d")
イベント 入力します 結果を得るための鍵。
XNUMX つの日付の月差
セル A5 と B5 の XNUMX つの日付の月差を取得するには、次の式を使用してください。
=DATEDIF(A5,B5,"m")
イベント 入力します 結果を得るための鍵。
XNUMX つの日付の年差
セル A8 と B8 の XNUMX つの日付の年差を取得するには、次の式を使用してください。
=DATEDIF(A8,B8,"y")
イベント 入力します 結果を得るための鍵。
XNUMX つの日付の週差
セル A11 と B11 の XNUMX つの日付の週差を取得するには、次の式を使用してください。
=DATEDIF(A11,B11,"d")/7
イベント 入力します 結果を得るための鍵。
注：
1) 上記の数式を使用して週の差を取得すると、結果が日付形式で返される場合があります。必要に応じて、結果を一般または数値に書式設定する必要があります。
2) 上記の数式を使用して週の差を取得すると、XNUMX 進数に戻る場合があります。整数の週番号を取得する場合は、以下に示すように前に ROUNDDOWN 関数を追加して、整数の週の差を取得できます。
=ROUNDDOWN(DATEDIF(A11,B11,"d")/7,0)
1.12 XNUMX つの日付の間の年と日を無視して月を計算する
下のスクリーンショットが示すように、XNUMX つの日付間の年と日を無視して月の差を計算したいだけの場合は、次の式が役立ちます。
=DATEDIF(A2,B2,"ym")
イベント 入力します 結果を得るための鍵。
A2 は開始日、B2 は終了日です。
1.13 XNUMX つの日付の間の年と月を無視して日を計算する
下のスクリーンショットに示すように、XNUMX つの日付間の年と月を無視して日差を計算したいだけの場合は、次の式が役立ちます。
=DATEDIF(A5,B5,"md")
イベント 入力します 結果を得るための鍵。
A5 は開始日、B5 は終了日です。
1.14 XNUMX つの日付の差を計算し、年、月、日を返す
下のスクリーンショットが示すように、XNUMX つの日付の差を取得し、xx 年、xx か月、および xx 日を返したい場合は、式も提供されます。
=DATEDIF(A8, B8, "y") &" years, "&DATEDIF(A8, B8, "ym") &" months, " &DATEDIF(A8, B8, "md") &" days"
イベント 入力します 結果を得るための鍵。
A8 は開始日、B8 は終了日です。
日付と今日の差を自動的に計算するには、上記の式の end_date を TODAY() に変更します。 ここでは、過去の日付と今日の日数の差をインスタンスとして計算します。
=DATEDIF(A11,TODAY(),"d")
イベント 入力します 結果を得るための鍵。
Note: 将来の日付と今日の差を計算する場合は、start_date を今日に変更し、将来の日付を次のように end_date として取得します。
=DATEDIF(TODAY(),A14,"d")
DATEDIF 関数の start_date は end_date より小さくなければならないことに注意してください。そうしないと、#NUM に戻ります。 エラー値。
1.16 XNUMX つの日付の間の休日の有無にかかわらず稼働日を計算する
場合によっては、指定された XNUMX つの日付の間の休日の有無にかかわらず、稼働日数をカウントする必要がある場合があります。
この部分では、NETWORKDAYS.INTL 関数を使用します。
NETWORKDAYS.INTL その引数と使用法を知るために。
休日を含む平日を数える
セル A2 と B2 の XNUMX つの日付の間に休日がある稼働日をカウントするには、次の式を使用してください。
=NETWORKDAYS.INTL(A2,B2)
イベント 入力します 結果を得るための鍵。
休日のない平日を数える
セル A2 と B2 の 5 つの日付の間に休日がある稼働日をカウントし、範囲 D9:DXNUMX の休日を除外するには、次の式を使用してください。
=NETWORKDAYS.INTL(A5,B5,1,D5:D9)
イベント 入力します 結果を得るための鍵。
注：
上記の式では、土曜日と日曜日を週末としています。異なる週末がある場合は、引数 [weekend] を必要に応じて変更してください。
XNUMX つの日付の間の週末の数を数えたい場合は、SUMPRODUCT または SUM 関数が役に立ちます。
セル A12 と B12 の XNUMX つの日付の間の週末 (土曜日と日曜日) を数えるには:
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A12&":"&B12)),2)>5))
Or
=SUM(INT((WEEKDAY(A12-{1,7})+B12-A12)/7))
イベント 入力します 結果を得るための鍵。
XNUMX つの日付の間の月曜日などの特定の曜日の数を数えるには、INT 関数と WEEKDAY 関数の組み合わせが役立ちます。
セル A15 と B15 は、その間に月曜日を数えたい XNUMX つの日付です。数式を次のように使用してください。
=INT((WEEKDAY(A15- 2)-A15 +B15)/7)
イベント 入力します 結果を得るための鍵。
別の曜日をカウントするように WEEKDAY 関数の曜日番号を変更します。
1 は日曜日、2 は月曜日、3 は火曜日、4 は水曜日、5 は木曜日、6 は金曜日、7 は土曜日)
以下のスクリーンショットに示すように、提供された日付に基づいて、月または年の残りの日数を知りたい場合があります。
当月の残り日数を取得する
EOMONTH 引数と使用法を知る。
セル A2 で当月の残りの日数を取得するには、次の式を使用してください。
=EOMONTH(A2,0)-A2
イベント 入力します キーを押しながら自動入力ハンドルをドラッグして、必要に応じてこの数式を他のセルに適用します。
ヒント： 結果は日付形式で表示される場合がありますが、一般的な形式または数値形式に変更するだけです。
今年の残り日数を取得する
セル A2 で今年の残りの日数を取得するには、次の式を使用してください。
=DATE(YEAR(A2),12,31)-A2
イベント 入力します キーを押しながら自動入力ハンドルをドラッグして、必要に応じてこの数式を他のセルに適用します。
XNUMX つの時間の差を求めるのに役立つ XNUMX つの簡単な式を次に示します。
次の式を使用して、セル A2 と B2 に start_time と end_time が別々に含まれているとします。
=B2-A2
=TEXT(B2-A2,"hh:mm:ss")
イベント 入力します 結果を得るための鍵。
注：
- end_time-start_time を使用すると、[セルの書式設定] ダイアログで、必要に応じて結果を別の時間形式に書式設定できます。
- TEXT(end_time-first_time,"time_format") を使用する場合は、TEXT(end_time-first_time,"h") が 16 を返すなど、式に表示される結果の時間形式を入力します。
- end_time が start_time より小さい場合、両方の式がエラー値を返します。 この問題を解決するには、ABS(B2-A2)、ABS(TEXT(B2-A2,"hh:mm:ss")) などの式の前に ABS を追加し、結果を時間として書式設定します。
1.22 時間/分/秒で XNUMX つの時間の差を計算する
下のスクリーンショットが示すように、時間、分、または秒で XNUMX つの時間の差を計算する場合は、この部分に従ってください。
XNUMX つの時刻の時間差を取得する
A5 と B5 の XNUMX つの時刻の時間差を取得するには、次の式を使用してください。
=INT((B5-A5)*24)
イベント 入力します キーを入力してから、時間形式の結果を一般または数値として書式設定します。
小数点以下の時間差を取得する場合は、(end_time-start_time)*24 を使用します。
XNUMX つの時刻の分数差を取得する
A8 と B8 の XNUMX つの時間の分差を取得するには、次の式を使用してください。
=INT((B8-A8)*1440)
イベント 入力します キーを入力してから、時間形式の結果を一般または数値として書式設定します。
1440 進数の分の差を取得する場合は、(end_time-start_time)*XNUMX を使用します。
XNUMX つの時刻の秒差を取得する
A5 と B5 の XNUMX つの時間の秒差を取得するには、次の式を使用してください。
=(B11-A11)*86400)
イベント 入力します キーを入力してから、時間形式の結果を一般または数値として書式設定します。
1.23 24 つの時刻の時間差のみを計算する (XNUMX 時間を超えない)
24 つの時間の差が XNUMX 時間を超えない場合、HOUR 関数はこれら XNUMX つの時間の時間差をすばやく取得できます。
HOUR この機能の詳細については、
セル A14 と B14 の時間差を取得するには、次のように HOUR 関数を使用してください。
=HOUR(B14-A14)
イベント 入力します 結果を得るための鍵。
start_time は end_time より小さくする必要があります。そうでない場合、式は #NUM! を返します。 エラー値。
1.24 60 つの時刻の分数の差のみを計算する (XNUMX 分を超えない)
MINUTE 関数を使用すると、これら XNUMX つの時刻の分単位の差のみをすばやく取得でき、時間と秒は無視されます。
MINUTE この機能の詳細については、
セル A17 と B17 の時刻の分差のみを取得するには、次のように MINUTE 関数を使用してください。
=MINUTE(B17-A17)
イベント 入力します 結果を得るための鍵。
start_time は end_time より小さくする必要があります。そうでない場合、式は #NUM! を返します。 エラー値。
1.25 60 つの時刻の秒差のみを計算する (XNUMX 秒を超えない)
SECOND 関数は、これら XNUMX つの時刻の差を秒単位ですばやく取得し、時間と分を無視します。
SECOND この機能の詳細については、
セル A20 と B20 の時間の秒差のみを取得するには、次のように SECOND 関数を使用してください。
=SECOND(B20-A20)
イベント 入力します 結果を得るための鍵。
start_time は end_time より小さくする必要があります。そうでない場合、式は #NUM! を返します。 エラー値。
1.26 XNUMX つの時間の差を計算し、時、分、秒を返す
XNUMX つの時刻の差を xx 時間 xx 分 xx 秒として表示したい場合は、以下のように TEXT 関数を使用してください。
TEXT この関数の引数と使用法を実現します。
セル A23 と B23 の時間の差を計算するには、次の式を使用します。
=TEXT(B23-A23,"h"" hours ""m"" minutes ""s"" seconds""").
イベント 入力します 結果を得るための鍵。
注：
また、この式は 24 時間を超えない時間差のみを計算し、end_time は start_time より大きくなければなりません。そうでない場合は #VALUE! を返します。 エラー値。
mm/dd/yyyy hh:mm:ss の形式で XNUMX つの時刻がある場合、それらの差を計算するには、必要に応じて以下の式のいずれかを使用できます。
XNUMX つの日時の時差を取得し、結果を hh:mm:ss 形式で返す
セル A2 と B2 の XNUMX つの日付時刻を例として、次の式を使用してください。
=B2-A2
イベント 入力します キー、datetime 形式で結果を返し、この結果を次のようにフォーマットします。 [h]：mm：ss の下のカスタム カテゴリで 数 タブの セルの書式設定 ダイアログ。
XNUMX つの日時の差を取得し、日、時、分、秒を返す
セル A5 と B5 の XNUMX つの日付時刻を例として、次の式を使用してください。
=INT(B5-A5) & " Days, " & HOUR(B5-A5) & " Hours, " & MINUTE(B5-A5) & " Minutes, " & SECOND(B5-A5) & " Seconds "
イベント 入力します 結果を得るための鍵。
注: どちらの数式でも、end_datetime は start_datetime より大きくなければなりません。そうでない場合、数式はエラー値を返します。
まず、ミリ秒を表示するためにセルをフォーマットする方法を知る必要があります。
ミリ秒を表示するセルを選択し、右に選択します セルの書式設定 を有効にする セルの書式設定 ダイアログ、選択 カスタム セクションに カテゴリー [番号] タブの下にリストを表示し、これを入力します。 hh：mm：ss.000 テキストボックスに。
式を使用します：
ここで、セル A8 と B8 の XNUMX つの時間の差を計算するには、式を次のように使用します。
=ABS(B8-A8)
イベント 入力します 結果を得るための鍵。
1.29 週末を除く XNUMX つの日付間の労働時間を計算する
場合によっては、週末 (土曜日と日曜日) を除く XNUMX つの日付の間の稼働時間をカウントする必要がある場合があります。
ここでは、労働時間は毎日 8 時間に固定されており、セル A16 と B16 に指定された XNUMX つの日付間の労働時間を計算するには、次の式を使用してください。
=NETWORKDAYS(A16,B16) * 8
イベント 入力します キーを入力し、結果を一般または数値としてフォーマットします。
XNUMX つの日付間の労働時間の計算に関するその他の例については、次の URL をご覧ください。 ExcelでXNUMXつの日付の間の労働時間を取得する
あなたが持っている場合 Kutools for Excel を Excel にインストールすると、数式を覚えなくても、日時差の計算の 90% をすばやく解決できます。
1.31 Data & Time Helper で XNUMX つの日時の差を計算する
Excel で XNUMX つの日時の差を計算するには、 日付と時刻のヘルパー 十分です
1. 計算結果を配置するセルを選択し、 クツール > フォーミュラヘルパー > 日付と時刻のヘルパー.
2.ポッピングで 日付と時刻のヘルパー ダイアログで、次の設定に従います。
- チェック 差異 オプション;
- で開始日時と終了日時を選択します 引数入力 セクションでは、日時を手動で入力ボックスに直接入力するか、カレンダー アイコンをクリックして日付を選択することもできます。
- ドロップダウン リストから出力結果のタイプを選択します。
- 結果をプレビュー 結果 のセクションから無料でダウンロードできます。
3。 クリック Ok. 計算結果が出力されるので、自動入力ハンドルを計算する必要のあるセルにドラッグします。
ヒント：
Kutools for ExcelでXNUMXつの日時の差を取得し、結果を日、時間、分として表示する場合は、次のようにしてください。
結果を配置するセルを選択し、 クツール > フォーミュラヘルパー > 日付と時刻 > XNUMX つの日付の間の日、時間、分を数える.
その後、 数式ヘルパー ダイアログで開始日と終了日を指定し、 Ok.
差分結果は、日、時間、分で表示されます。
日付と時刻のヘルパー この機能の使用法をもっと知るために。
Kutools for Excel このアドインのすべての機能を知る。
無料ダウンロード Kutools for Excelの30日間の無料トライアルを取得するには
1.32 Formula Helper を使用して、XNUMX つの日時の週末/平日/特定の平日の差を計算する
週末、平日、またはXNUMXつの日時の間の特定の平日をすばやく数えたい場合は、Kutools for Excelの フォーミュラヘルパー グループがお手伝いします。
1. 計算結果を配置するセルを選択し、 クツール > 統計的 > XNUMXつの日付の間の非稼働日数/XNUMXつの日付の間の稼働日数/特定の曜日の数を数える.
2.飛び出しで 数式ヘルパー ダイアログで、適用する場合は開始日と終了日を指定します 特定の曜日の数を数える、曜日も指定する必要があります。
特定の平日を数えるには、ノートを参照して、1 ～ 7 を使用して日曜日～土曜日を示すことができます。
3。 クリック Ok、必要に応じて、週末/稼働日/特定の平日の数をカウントする必要があるセルにオートフィル ハンドルをドラッグします。
Kutools for Excel このアドインのすべての機能を知る。
無料ダウンロード Kutools for Excelの30日間の無料トライアルを取得するには
2.日付と時刻の加算または減算
XNUMX つの日時の差を計算することを除いて、加算または減算も Excel の通常の日時計算です。 たとえば、製品の製造日と保存日数に基づいて期日を取得したい場合があります。
特定の日数を日付に加算または減算するには、XNUMX つの異なる方法があります。
セル A21 の日付に 2 日を追加すると仮定すると、以下のいずれかの方法を選択して解決してください。
方法 1 日付 + 日
セルを選択し、数式を入力します。
=A+21
イベント 入力します 結果を得るための鍵。
21 日を減算する場合は、juts をプラス記号 (+) からマイナス記号 (-) に変更します。
方法 2 特殊貼り付け
1. セル C2 のセルに追加する日数を入力し、 を押します。 Ctrlキー + C それをコピーする。
2. 次に、21 日を追加する日付を選択し、右クリックしてコンテキスト メニューを表示し、[ 特殊貼り付け....
3。 の中に 貼り付け ダイアログ、チェック Add オプション (日数を減算する場合は、 減算 オプション）。 クリック OK.
4. 元の日付が 5 桁の数字に変わり、日付として書式設定されます。
日付に月を加算または減算するには、EDATE 関数を使用できます。
EDATE その引数と使用法を研究する。
セル A6 の日付に 2 か月を追加すると仮定すると、式を次のように使用します。
=EDATE(A2,6)
イベント 入力します 結果を得るための鍵。
日付から 6 か月を減算する場合は、6 を -6 に変更します。
日付に n 年を加算または減算するには、DATE、YEAR、MONTH、および DAY 関数を組み合わせた数式を使用できます。
セル A3 の日付に 2 年を追加すると仮定すると、次の式を使用します。
=DATE(YEAR(A2) + 3, MONTH(A2),DAY(A2))
イベント 入力します 結果を得るための鍵。
日付から 3 年を減算する場合は、3 を -3 に変更します。
日付に週を加算または減算するには、一般式は次のとおりです。
セル A4 の日付に 2 週間追加すると仮定すると、次の式を使用します。
=A2+4*7
イベント 入力します 結果を得るための鍵。
日付から 4 週間を減算する場合は、プラス記号 (+) をマイナス記号 (-) に変更します。
このセクションでは、WORKDAY 関数を使用して、休日を除く、または休日を含む特定の日付に営業日を加算または減算する方法を紹介します。
訪問 就業日 その引数と使用法についての詳細を知るために。
休日を含む平日を追加
セル A2 は使用する日付です。セル B2 には、追加する日数が含まれています。式を次のように使用してください。
=WORKDAY(A2,B2)
イベント 入力します 結果を得るための鍵。
休日を除く平日を追加
セル A5 には使用する日付、セル B5 には追加する日数が含まれ、範囲 D5:D8 には休日がリストされています。式を次のように使用してください。
=WORKDAY(A5,B5,D5:D8)
イベント 入力します 結果を得るための鍵。
注：
WORKDAY 関数は土曜と日曜を週末と見なします。週末が土曜と日曜の場合は、週末の指定をサポートする WOKRDAY.INTL 関数を適用できます。
訪問 WORKDAY.INTL のガイドをご参照ください。
稼働日を日付から差し引く場合は、数式で日数をマイナスに変更するだけです。
特定の年、月、日を日付に追加する場合は、DATE、YEAR、MONTH、および DAYS 関数を組み合わせた式が役立ちます。
A1 の日付に 2 年 30 か月と 11 日を加算するには、次の式を使用してください。
=DATE(YEAR(A11)+1,MONTH(A11)+2,DAY(A11)+30)
イベント 入力します 結果を得るための鍵。
減算する場合は、すべてのプラス記号 (+) をマイナス記号 (-) に変更します。
ここでは、日時に時間、分、または秒を加算または減算するための数式をいくつか示します。
日時に時間を加算または減算する
セル A3 の日時 (時刻の場合もあります) に 2 時間を追加すると仮定すると、次の式を使用してください。
=A2+3/24
イベント 入力します 結果を得るための鍵。
日時に時間を加算または減算する
セル A15 の日時 (時刻の場合もあります) に 5 分を追加すると仮定すると、次の式を使用してください。
=A2+15/1440
イベント 入力します 結果を得るための鍵。
日時に時間を加算または減算する
セル A20 の日時 (時刻の場合もあります) に 8 秒を追加すると仮定すると、次の式を使用してください。
=A2+20/86400
イベント 入力します 結果を得るための鍵。
XNUMX 週間のすべてのスタッフの勤務時間を記録した Excel の表があると仮定すると、支払いを計算するための合計勤務時間を合計するには、次のように使用できます。 SUM(範囲) 結果を得るために。 しかし、一般的に、下のスクリーンショットが示すように、合計された結果は 24 時間を超えない時間として表示されます。正しい結果を得るにはどうすればよいでしょうか?
実際には、結果を次のようにフォーマットするだけです。 [hh]:mm:ss.
結果セルを右クリックして、 セルの書式設定 コンテキストメニューとポップで セルの書式設定 ダイアログで選択 カスタム ブリストから、入力します [hh]:mm:ss 右側のセクションのテキストボックスに、 OK.
集計結果が正しく表示されます。
ここでは、特定の労働時間を開始日に追加し、週末 (土曜と日曜) と休日を除外することに基づいて、終了日を取得するための長い式を示します。
Excel テーブルでは、A11 には開始日時が含まれ、B11 には稼働時間が含まれます。セル E11 と E13 には稼働開始時刻と終了時刻が含まれ、セル E15 には除外される休日が含まれます。
次の式を使用してください。
=WORKDAY(A11,INT(B11/8)+IF(TIME(HOUR(A11),MINUTE(A11),SECOND(A11))+TIME(MOD(B11,8),MOD(MOD(B11,8),1)*60,0)> $E$13,1,0),$E$15)+IF(TIME(HOUR(A11),MINUTE(A11),SECOND(A11))+TIME(MOD(B11,8),MOD(MOD(B11,8),1)*60,0)>$E$13,$E$11 +TIME(HOUR(A11),MINUTE(A11),SECOND(A11))+TIME(MOD(B11,8),MOD(MOD(B11,8),1)*60,0)-$E$13,TIME(HOUR(A11),MINUTE(A11),SECOND(A11)) +TIME(MOD(B11,8),MOD(MOD(B11,8),1)*60,0))
イベント 入力します 結果を得るための鍵。
Kutools for Excelがインストールされている場合、ツールはXNUMXつだけです – 日付と時間のヘルプr は、datetime の加算と減算に関するほとんどの計算を解くことができます。
1. 結果を出力したいセルをクリックし、 をクリックしてこのツールを適用します。 クツール > フォーミュラヘルパー > 日付と時刻のヘルパー。
2。 の中に 日付と時刻のヘルパー ダイアログ、チェック Add オプションまたは 減算 必要に応じてオプションを選択し、セルを選択するか、使用する日時を直接入力します 引数入力 セクションで、加算または減算する年、月、週、日、時、分、および秒を指定して、 Ok。 スクリーンショットを参照してください：
で計算結果をプレビューできます。 結果 のセクションから無料でダウンロードできます。
結果が出力されたので、自動ハンドルを他のセルにドラッグして結果を取得します。
日付と時刻のヘルパー この機能の使用法をもっと知るために。
Kutools for Excel このアドインのすべての機能を知る。
料金 ダウンロード Kutools for Excelの30日間の無料トライアルを取得するには
2.41 日付の有効期限が切れているかどうかを確認または強調表示する
製品の有効期限のリストがある場合は、下のスクリーンショットに示すように、今日に基づいて有効期限が切れている日付を確認して強調表示することをお勧めします。
実際、 条件付き書式 この仕事をすばやく処理できます。
1. 確認したい日付を選択し、 をクリックします。 ホーム > 条件付き書式 > 新しいルール.
2。 の中に 新しい書式設定規則 ダイアログ、選択 式を使用して、フォーマットするセルを決定する セクションに ルールタイプを選択します セクション、およびタイプ =B2 入力ボックス (B2 は確認する最初の日付) に入力し、 フォーマット ポップアップする セルの書式設定 ダイアログで、必要に応じて有効期限が切れないように別の書式を選択します。 クリック OK > OK.
一部の製品の有効期限は、製造月の月末または製造の翌月の最初の日です。製造日に基づいて有効期限をすばやくリストするには、この部分に従ってください.
今月末を取得する
セル B13 の製造日は次のとおりです。数式を次のように使用してください。
=EOMONTH(B13,0)
イベント 入力します 結果を得るための鍵。
翌月の 1 日を取得する
セル B18 の製造日は次のとおりです。数式を次のように使用してください。
=EOMONTH(B18,0)+1
イベント 入力します 結果を得るための鍵。
3.年齢を計算する
このセクションでは、特定の日付またはシリーズ番号に基づいて年齢を計算する方法を解決する方法を示します。
生年月日から年齢をXNUMX進数で取得
イヤーフラック 引数と使用法の詳細については、 を参照してください。
たとえば、列 B2:B9 の生年月日のリストに基づいて年齢を取得するには、次のような式を使用してください。
=YEARFRAC(B2,TODAY())
イベント 入力します キーを押し、すべての年齢が計算されるまで自動入力ハンドルを下にドラッグします。
ヒント：
1) 必要に応じて小数点以下の桁数を指定できます セルの書式設定 ダイアログ。
2) 特定の生年月日に基づいて特定の日付の年齢を計算する場合は、TODAY() を =YEARFRAC(B2,"1/1/2021") のように二重引用符で囲まれた特定の日付に変更します。
3) 生年月日から翌年の年齢を取得したい場合は、=YEARFRAC(B1,TODAY())+2 のように式に 1 を足すだけです。
生年月日から整数で年齢を取得する
DATEIF 引数と使用法の詳細については、 を参照してください。
上記の例を使用して、B2:B9 のリストの生年月日に基づいて年齢を取得するには、次の式を使用してください。
=DATEDIF(B2,TODAY(),"y")
イベント 入力します キーを押してから、すべての年齢が計算されるまでオートフィル ハンドルを下にドラッグします。
ヒント：
1) 特定の生年月日に基づいて特定の日付の年齢を計算する場合は、TODAY() を =DATEDIF(B2,"1/1/2021","y") のように二重引用符で囲まれた特定の日付に変更します。 .
2) 生年月日から翌年の年齢を取得したい場合は、=DATEDIF(B1,TODAY(),"y")+2 のように数式に 1 を足すだけです。
3.12 与えられた誕生日から年、月、日の形式で年齢を計算する
下のスクリーンショットに示すように、特定の生年月日に基づいて年齢を計算し、結果を xx 年、xx か月、xx 日として表示する場合は、次の長い数式が役立ちます。
セル B12 の生年月日に基づいて年、月、日で年齢を取得するには、次の式を使用してください。
=DATEDIF(B12,TODAY(),"Y")&" Years, "&DATEDIF(B12,TODAY(),"YM")&" Months, "&DATEDIF(B12,TODAY(),"MD")&" Days"
イベント 入力します キーを押して年齢を取得し、オートフィル ハンドルを他のセルまでドラッグします。
ヒント：
特定の生年月日に基づいて特定の日付の年齢を計算する場合は、TODAY() を = =DATEDIF(B12,"1/1/2021","Y")& のように二重引用符で囲まれた特定の日付に変更します。 " 年、"&DATEDIF(B12,"1/1/2021","YM")&" 月、"&DATEDIF(B12,"1/1/2021","MD")&" 日".
3.13 1 年 1 月 1900 日より前の生年月日で年齢を計算する
Excel では、1 年 1 月 1900 日より前の日付を日時として入力したり、正しく計算したりできません。 しかし、指定された生年月日 (1 年 11900 月より前) と死亡日に基づいて有名人の年齢を計算したい場合は、VBA コードだけが役に立ちます。
1。 押す 他の + F11 有効にするキー アプリケーション向け Microsoft Visual Basic ウィンドウをクリックして インセット タブを選択して モジュール 新しいモジュールを作成します。
2. 次に、以下のコードをコピーして新しいモジュールに貼り付けます。
VBA：1年1月1900日より前の年齢を計算する
Public Function AgeFunc(SDate As Variant, EDate As Variant) As Long
'UpdatebyExtendOffice
Dim xSMonth As Integer
Dim xSDay As Integer
Dim xSYear As Integer
Dim xEMonth As Integer
Dim xEDay As Integer
Dim xEYear As Integer
Dim xAge As Integer
If Not GetDate(SDate, xSYear, xSMonth, xSDay) Then
AgeFunc = "Invalid Date"
Exit Function
End If
If Not GetDate(EDate, xEYear, xEMonth, xEDay) Then
AgeFunc = "Invalid Date"
Exit Function
End If
xAge = xEYear - xSYear
If xSMonth > xEMonth Then
xAge = xAge - 1
ElseIf xSMonth = xEMonth Then
If xSDay > xEDay Then xAge = xAge - 1
End If
If xAge < 0 Then
AgeFunc = "Invalid Date"
Else
AgeFunc = xAge
End If
End Function
Private Function GetDate(ByVal DateStr As String, Y As Integer, M As Integer, D As Integer) As Boolean
Dim I As Long
Dim K As Long
Y = 0
M = 0
D = 0
GetDate = True
On Error Resume Next
I = InStr(1, DateStr, "/")
M = CLng(Left(DateStr, I - 1))
D = CLng(Mid(DateStr, I + 1, InStr(I + 1, DateStr, "/") - I - 1))
Y = CLng(Right(DateStr, Len(DateStr) - InStrRev(DateStr, "/")))
If M < 1 Or M > 12 Or D < 1 Or D > 31 Or Y < 1 Then
GetDate = False
End If
End Function
3. コードを保存し、シートに戻り、計算された年齢を配置するセルを選択します。この場合、「=AgeFunc(birthdate,deathdate)」と入力します。 =AgeFunc(B22,C22)、Enterキーを押して年齢を取得します。 必要に応じて、自動塗りつぶしハンドルを使用して、この数式を他のセルに適用します。
あなたが持っている場合 Kutools for Excel Excelにインストールされている場合、適用できます 日付と時刻のヘルパー 年齢を計算するツール。
1. 計算した年齢を配置するセルを選択し、 をクリックします。 クツール > フォーミュラヘルパー > 日付と時刻のヘルパー.
2。 の中に 日付と時刻のヘルパー ダイアログ、
- 1）チェック ご年齢 オプション;
- 2) 生年月日のセルを選択するか、生年月日を直接入力するか、カレンダー アイコンをクリックして生年月日を選択します。
- 3）選択 今日 現在の年齢を計算する場合は、オプションを選択します 指定日 過去または未来の年齢を計算する場合は、オプションを選択して日付を入力します。
- 4) ドロップダウン リストから出力タイプを指定します。
- 5) 出力結果をプレビューします。 クリック Ok.
日付と時刻のヘルパー この機能の使用法をもっと知るために。
Kutools for Excel このアドインのすべての機能を知る。
無料ダウンロード Kutools for Excelの30日間の無料トライアルを取得するには
最初の 6 桁を使用して生年月日を記録する ID 番号のリストがある場合 (920315330 など)、生年月日が 03 年 15 月 1992 日であることを意味する場合、どうすれば生年月日を別の列にすばやく取得できますか?
次に、セル C2 から始まる ID 番号のリストをインスタンスとして取り、式を次のように使用します。
=MID(C2,5,2)&"/"&MID(C2,3,2)&"/"&MID(C2,1,2)
イベント 入力します 鍵。 次に、オートフィル ハンドルを下にドラッグして、他の結果を取得します。
注：
数式では、必要に応じて参照を変更できます。 たとえば、ID 番号が 13219920420392 と表示され、誕生日が 04 年 20 月 1992 日の場合、数式を =MID(C2,8,2)&"/"&MID(C2,10,2)&"/ に変更できます。 "&MID(C2,4,4) で正しい結果が得られます。
最初の 6 桁を使用して生年月日を記録する ID 番号のリストがある場合 (920315330 など)、生年月日が 03 年 15 月 1992 日であることを意味する場合、Excel で各 ID 番号に基づいて年齢をすばやく計算するにはどうすればよいでしょうか?
次に、セル C2 から始まる ID 番号のリストをインスタンスとして取り、式を次のように使用します。
=DATEDIF(DATE(IF(LEFT(C2,2)>TEXT(TODAY(),"YY"),"19"&LEFT(C2,2),"20"&LEFT(C2,2)),MID(C2,3,2),MID(C2,5,2)),TODAY(),"y")
イベント 入力します 鍵。 次に、オートフィル ハンドルを下にドラッグして、他の結果を取得します。
注：
この式では、年が現在の年よりも小さい場合、年は 20 で始まると見なされます。たとえば、200203943 は 2020 年と見なされます。 年が現在の年よりも大きい場合、年は 19 で始まると見なされます。たとえば、920420392 は 1992 年と見なされます。
その他のExcelチュートリアル：
複数のワークブック/ワークシートをXNUMXつにまとめる
このチュートリアルでは、直面する可能性のあるほぼすべての組み合わせシナリオをリストし、比較的専門的なソリューションを提供します。
テキスト、数値、および日付のセルを分割します（複数の列に分割します）
このチュートリアルは、テキストセルの分割、数値セルの分割、日付セルの分割のXNUMXつの部分に分かれています。 各パートには、同じ問題が発生したときに分割ジョブを処理する方法を理解するのに役立つさまざまな例が用意されています。
Excelでデータを失うことなく複数のセルの内容を組み合わせる
このチュートリアルでは、セル内の特定の位置に抽出を絞り込み、Excelの特定の位置によってセルからテキストまたは数値を抽出するのに役立つさまざまな方法を収集します。
Excelでの一致と相違についてXNUMXつの列を比較する
この記事では、遭遇する可能性のあるXNUMXつの列を比較する可能性のある最も可能性の高いシナリオについて説明し、それが役立つことを願っています。
最高のオフィス生産性ツール
Kutools for Excelはほとんどの問題を解決し、生産性を80％向上させます
- スーパーフォーミュラバー （複数行のテキストと数式を簡単に編集できます）; 読書レイアウト （多数のセルを簡単に読み取って編集する）; フィルター範囲に貼り付け...
- セル/行/列をマージする およびデータの保持。 分割セルコンテンツ; 重複する行と合計/平均を組み合わせる...重複セルを防止します。 範囲を比較する...
- [複製]または[一意]を選択します 行; 空白行を選択 （すべてのセルは空です）; スーパーファインドとファジーファインド 多くのワークブックで; ランダム選択...
- 正確なコピー 数式参照を変更せずに複数のセル。 参照の自動作成 複数のシートに; 箇条書きを挿入、チェックボックスなど...
- お気に入りの数式をすばやく挿入する、範囲、チャート、写真; セルを暗号化する パスワード付き。 メーリングリストを作成する そしてメールを送る...
- テキストを抽出、テキストの追加、位置による削除、 スペースを削除する; ページング小計の作成と印刷。 セルの内容とコメントを変換する...
- スーパーフィルター （フィルタースキームを保存して他のシートに適用します）; 高度な並べ替え 月/週/日、頻度など。 特殊フィルター 太字、斜体...
- ワークブックとワークシートを組み合わせる; キー列に基づいてテーブルをマージします。 データを複数のシートに分割; xls、xlsx、PDFをバッチ変換...
- ピボットテーブルのグループ化 週番号、曜日など... ロック解除された、ロックされたセルを表示する さまざまな色で; 式/名前を持つセルを強調表示する...
- Word、Excel、PowerPointでタブ付きの編集と読み取りを有効にする、パブリッシャー、アクセス、Visioおよびプロジェクト。
- 新しいウィンドウではなく、同じウィンドウの新しいタブで複数のドキュメントを開いて作成します。
- 生産性を 50% 向上させ、毎日何百回もマウス クリックを減らすことができます!
