エクセルで期間満了日を出したい

質問

エクセルで契約期間の表を作っています。
満了日は契約日から1年後とし、満了日を過ぎたものは自動更新となり、同じセルに翌年の満了日を表示させたいのです。

(例)現在2007/10/17の場合
　　A1　　　 B1　　　C1
　契約日　　期間　　満了日
2006/11/1　　1年　　2007/10/31
2006/10/1　　1年　　2008/9/30（1年経過した為、2年後の日付）

関数で出せますでしょうか？
よろしくお願いいたします。

回答No.4

zap35 · ベストアンサー

＃０１です。すこし簡単に考えすぎましたね。＃０３さんのご指摘のように契約日がずっと昔で、複数年契約の自動更新のパターンが考慮できていませんでした。

理屈は＃０３さんと一緒ですが、こんな式でもできます。＃０１でも書きましたが「ツール」→「アドイン」で「分析ツール」にチェックが必要です。
　=EDATE(A1,CEILING(YEARFRAC(A1,TODAY(),1),B1)*12)-1

お礼

これでしたら、契約日が未来であってもきちんと繁栄しますね。
助かりました。ありがとうございます。

回答No.5

imogasi

今現在で１年先１日前、満了日をだすのは
=IF(TODAY()>A1,DATE(YEAR(A1)+1,MONTH(A1),DAY(A1)-1),A1)
のような式でできるかなと思う。
しかし満了日は次ぎの年になれば、今の満了日の列に、計算した満了日を入れたいだろうと思う。次々列を増やせないから。
関数では、計算に使ったデータのセルの値を、計算後の値で置き換えることができない。
ーー
それで契約日（A列）を元にするほうが良い
A列契約日を元に計算すると計算がややこしい。
=DATE(YEAR(A1)+DATEDIF(A1,TODAY(),"y")+1,MONTH(A1),DAY(A1)-1)
ーー
満了日（B列）を元にやるとすると
VBAでやらざるを得ないようにおもう。
WorkbookのOpenイベントにでも
Sub test01()
d = Range("a65536").End(xlUp).Row
For i = 1 To d
If Date > Cells(i, "B") Then
dd = Cells(i, "B")
Cells(i, "B") = DateSerial(Year(dd) + 1, Month(dd), Day(dd) - 1)
Else
End If
Next i
End Sub

回答No.3

grumpy_the_dwarf

http://oshiete1.goo.ne.jp/qa3433656.htmlでも書いたんですが、
=datedif(起算日,today(),"Y")+datedif(起算日,today(),"YD")/365
とすると経過年数の小数表示ができます。これをふまえて、

期間が1年の場合、経過年数が1年以下だったら1年後，2年以下だっ
たら2年後を返せばいいので、切り上げでいいような気がします。で
も期間が5年だったら…念のため=ceiling(経過年数,期間)というこ
とにしましょう。それなら期間が5年で経過年数が1.2年とかの場合
に5年後になりそうです。

zap35さんのedateを流用させてもらうと、
=edate(A1,ceiling(datedif(A1,today(),"y")+datedif(A1,today(),"yd")/365,B1)*12)-1
という感じですね。

お礼

ありがとうございます。
試したところ、契約日がtodayより後の場合にはエラーになってしましました・・・
そういったケースも割りとあるので、できれば未来の契約日に関しても満了日が反映するようにさせたいのです。
申し訳ありません。

回答No.2

mu2011

長い数式になりますが、次の方法は如何でしょうか。
=IF(TODAY()<=DATE(YEAR(A1)+1,MONTH(A1),DAY(A1))-1,DATE(YEAR(A1)+1,MONTH(A1),DAY(A1)-1),DATE(YEAR(A1)+2,MONTH(A1),DAY(A1)-1))

回答No.1

zap35

A1に「2006/11/1」の日付が、B1に「1」の数字があるとき、
　=EDATE(A1,B1*12)
で１年後の日付である「2007/11/1」を求められます。満了日はその１日前ですから
　=EDATE(A1,B1*12)-1
になります。（関数の２番目の引数は月数なので12を掛けています。またセルの書式は「日付」にしてください）
またEDATE関数は「ツール」→「アドイン」で「分析ツール」にチェックを入れないと使用できませんのでご注意ください

質問では「本日」が満了日を過ぎていたら次の満了日を表示したいとのことですので
　=IF(EDATE(A1,B1*12)-1<TODAY(),EDATE(A1,B1*24)-1,EDATE(A1,B1*12)-1)
で良いと思います

なおB列は計算に使用していますので「数値」を入力してください。表示に「年」をつけたいならセルの書式で「0"年"」とすれば、「1」と入力したとき「1年」と表示されます。