EXCELで、ある月が含まれる四半期末日の年月日を

質問

EXCELで、ある月が含まれる四半期末日の年月日を表示する方法

60歳に到達する誕生日（年月日）から、その月が含まれる四半期末日の年月日を表示する数式を知りたいです。
誕生月が4月～6月なら6月末日、7月～9月なら9月末日という具合です。
（例：2021年5月5日が60歳の誕生日なら2021年6月30日と表示したい）
各セルには下記の内容が入っています。

H5 ⇒ 生年月日（●年●月●日）

K5 ⇒ 60歳に到達する年月日の数式 =DATE(YEAR(H5)+60,MONTH(H5),DAY(H5))

N5 ⇒ K5から月を抜き出す数式 =MONTH(H5)

O5 ⇒ K5の月が含まれる四半期末月 =CHOOSE(N5,3,3,3,6,6,6,9,9,9,12,12,12)&"月"

O5セルにK5から年を抜き出し、CHOOSEの式、末日を表示する式EOMONTHを繋げようとするとエラーになってしまいます。
○年○月末日と表示できる方法があれば教えてください。

H5以外は表示する必要のないセルなので、上記の数式にこだわらず一番スマートな方法を教えていただけますと幸いです。
よろしくお願い致します。

回答No.2

asciiz · ベストアンサー

これでどうでしょう

H5 誕生日入力
I5(満60歳の日) =DATE(YEAR(H5)+60,MONTH(H5),DAY(H5))
J5(I5の四半期末日) =EOMONTH(I5,MOD(3-MONTH(I5),3))

式の解説
EOMONTH が、「指定日からxか月後の末日」を求められる関数なんですから、1月だったら2か月先、2月だったら1か月先、3月だったら当月(0)となるようにxを求めています。→ x = MOD(3-MONTH(I5),3)

N5・O5セルは不要ですが、表示が必要なら、求められた四半期末日セルからMONTHで取り出してもいいですね。

お礼

ありがとうございます！
求めたい内容が表示できました。
考え方を変えるとこんなにスマートな式ができるんですね。
先にご回答いただいた文字列の件も含め、大変勉強になりました。
丁寧にご解説いただき、ありがとうございました。

回答No.5

Chiquilin

なんだかよく分かりませんが

=DATE(YEAR(H5)+60,CEILING(MONTH(H5),3)+1,0)

こういうことですか？

お礼

ありがとうございます！
こちらの式でも求めたい内容が表示できました。
ひとつの回答を得るにもこんなに色々な方法があるんですね。
勉強になりました。

回答No.4

kkkkkm

No3です

H5はK5でした。

回答No.3

kkkkkm

=EOMONTH(DATE(YEAR(H5),CHOOSE(MONTH(H5),3,3,3,6,6,6,9,9,9,12,12,12),1),0)

で試してみてください。

お礼

ありがとうございます！
求めたい内容が表示できました。
元々作ろうとしていた式の正解がわかり、大変勉強になりました。

回答No.1

asciiz

＞O5セルにK5から年を抜き出し、CHOOSEの式、末日を表示する式EOMONTHを繋げようとするとエラーになってしまいます。

O5セルの式で「&"月"」って文字列を追加しちゃってるので、そのセルのデータが「文字列」となってしまっています。
それで、のちの計算に利用できないのでしょう。
計算用のセルなんですから、「&"月"」なんかつけずに数字のままでいいんですよ。(どうしても月表示したければ、書式設定で「#"月"」としましょう)
そこを直せば、次の計算もできると思います。

もう少し効率良くできるかどうかは、これから考えてみます。