dd-mm-yyyy?

Fester675

Board Regular
Joined
Sep 16, 2016
Messages
116
I'm after some help! :confused:

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! ;)
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
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

Well-known Member
Joined
Oct 20, 2009
Messages
7,882
Office Version
365
Platform
Windows
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>
 
Last edited:

Fester675

Board Regular
Joined
Sep 16, 2016
Messages
116
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?
 

Forum statistics

Threads
1,089,274
Messages
5,407,324
Members
403,134
Latest member
fmlp

This Week's Hot Topics

Top