dd-mm-yyyy?

[Fester675]

I'm after some help!

I have 3 data validation lists - E4 (01-31); F4 (Jan-Dec); H4 (2019-1969)
and I want to check what the User selects - IF their input is ON or BEFORE 30-04-2013 the result cell will be 31.5

IF it is AFTER 30-04-2013 and the Users input is GREATER than 5 years from today's date, the result will be 30
IF it is AFTER 30-04-2013 and the Users input is LESS than 5 years from today's date, the result will be 25

Assistance is greatly appreciated in advance!

 

[Gerald Higgins]

This will be easier if your inputs in cell E4, F4, and H4 are all numeric values, not text strings, especially the month part.
Let's assume they are all numbers.
This might get you started.

=IF(DATE(H4,F4,E4)<=DATE(2013,4,30),31.5,0)

You might find it easier to replace
(2013,4,30)
with cell references which carry the date - perhaps a single cell holding the entire date, or if you like, separate cells holding DD/MM/YYYY.
This will make it easier for you to change that date.
This formula returns 0 if the date is after 30/04/2013.

If this bit works, you can then adapt it to deal with the other conditions.
Post back if you need more help with that.

 

[steve the fish]

See if this does it:

=IF(ISNUMBER(DATEVALUE(E4&F4&G4)),IF(DATEVALUE(E4&F4&G4) < DATE(2013,5,1),31.5,IF(DATEVALUE(E4&F4&G4)<EDATE(TODAY(),-60),30,25)),"Not Valid Date")<date(2013,5,1),31.5,if(datevalue(e4&f4&g4)<edate(today(),-60),30,25)),"not valid="" date")<="" html=""></date(2013,5,1),31.5,if(datevalue(e4&f4&g4)<edate(today(),-60),30,25)),"not>

 

[Fester675]

Thanks guys!
A variation of Gerald's worked fine for what I need - cheers!
Just a quick one though, what would the formula be for CURRENT DATE minus 5 YEARS?