IF AND statements with dates

[Tee10823]

I am very inexperienced with this type of formula. Can someone help me correct the following:

Please Login or Register  to view this content.

In B4 I can have 0,50 or 100. If the result is 0, I want it to enter the current date plus 3 years. If the result is 50, then I want it to enter the current date plus 2 years and finally (I gave up at this point) if the result is 100 I want it to put the date plus 1 year. The first bit works, ie upto the 1095, but the rest doesn't. Any ideas appreciated.

[martindwilson]

use edate and today
=IF(B4=0,EDATE(TODAY(),36),IF(B4=50,EDATE(TODAY(),24),IF(B4=100,EDATE(TODAY(),12),"")))

[misrasomendra]

Hi,

Put 0,50,100 in D1:D3 and 1095,730,365 in E1:E3,

Than use below formula:

=LOOKUP(B4,D1:D3,NOW()+E1:E3)

[judgeh59]

and now for something completely different

Please Login or Register  to view this content.

[Tee10823]

Worked like a dream....I see I have a lot to learn! Thanks

[Tee10823]

Thanks very much

[martindwilson]

or
=EDATE(TODAY(),(3-B4/50)*12)
note adding
1095,730,365 does not account for leap years
edate() does

[TMS]

One way:

Please Login or Register  to view this content.

Regards, TMS

[judgeh59]

Isn't amazing how many different ways you can do the same thing....WOW...

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[TMS]

	A	B	C	D
1	0	1095
2	50	730
3	100	365
4		0	22/10/2017	=IFERROR(DATE(YEAR(TODAY())+CHOOSE(MATCH(B4,{0,50,100},0),3,2,1),MONTH(TODAY()),DAY(TODAY())),"invalid")
5			22/10/2017	=EDATE(TODAY(),(3-B4/50)*12)
6			21/10/2017	=IF(B4=0,NOW()+1095,IF(B4=50,NOW()+730,NOW()+365))
7			21/10/2017	=LOOKUP(B4,A1:A3,NOW()+B1:B3)
8			22/10/2017	=IF(B4=0,EDATE(TODAY(),36),IF(B4=50,EDATE(TODAY(),24),IF(B4=100,EDATE(TODAY(),12),"")))
9
10
11
12				In B4 I can have 0,50 or 100. If the result is 0, I want it to enter the current date plus 3 years. If the result is 50, then I want it to enter the current date plus 2 years and finally (I gave up at this point) if the result is 100 I want it to put the date plus 1 year. The first bit works, ie upto the 1095, but the rest doesn't. Any ideas appreciated.

Regards, TMS