Occasional Contributor
For date from> = 08/20/1950 to display the value 80 and for date> = 09/20/1950 to <= 10/30/1998 to display the value 60 and for date> = 10/31/1998 to and <= 01/30/2008 to display the value 60!
I apply the datevalue command but I get something wrong! Could you help me ?
Thank you very much
20 Replies


why 8/20/1950 and 9/20/1950?

Why twice the value 60?

Try a variation of this, where C2 is the cell with the date:



For date from
1. <= 20/08/1950 to display the value 80 points and
2. for date> = 20/09/1950 until <= 30/10/1998 to display the value 60 points and
3. for date> = 31/10/1998 until <= 30/01/2008 to display the value 40 points
Which way is the best to find the solution ?


What about dates between 21/08/1950 and 19/09/1950?

What about dates after 30/01/2008?

Is about some deadlines !
My problem is that I cannot find the right functions about that!


With the information that you have provided:


=IF(C2<=DATE(1950,8,20), 80, IF(C2<=DATE(1950,9,19), "Undefined", IF(C2<=DATE(1998,10,30), 60, IF(C2<=DATE(2008,01,30), 0, "Undefined"))))


where C2 is the cell with the date.

@emmapapa2  ....  It is unclear what the problem is.  Is it simply that DATEVALUE does not work for you?  If so, what does it return?  Or is that your logic does not work for you?  If so, what exactly are the formulas that you tried?


The problem might be that the form of the dates is not recognized as numeric dates; instead, Excel might be treating them as text.  Use formulas like =ISNUMBER(A1) to determine the type of the data.


If you are still having a problem, I suggest that you attach an Excel file (not an image) that demonstrates the problem

Construction age (x5):
The time period in which the date of issuance of the building permit is included is stated. In case of addition of a permit and / or inclusion in a law of arbitrary settlement, the declaration is made on the basis of the period of issuance of the initial building permit. In case there is no building permit, the construction declaration is made on the basis of being subject to an arbitrary settlement law.
For the dates of issuance of a permit / construction based on a declaration of affiliation, the scoring is as follows:
(x5≤09.08.1955) = 100 points,
(10.08.1955≤ x5≤09.06.1975) = 80 points,
(10.06.1975≤ x5≤31.12.1982) = 60 points,
(01.01.1983≤ x5≤31.12.1992) = 45 points,
(01.01.1993≤ x5≤31.12.2003) = 30 points,
(01.01.2004≤ x5≤31.12.2011) = 15 points
(x5> 1.1.2012) = 0 points
The real problem is this! And I would like to introduce a functions tofind thw solution! Could you helpme???


I'd create a small lookup table. In the screenshot below, it is on the same sheet, but it can be on another sheet if you prefer.


Formula in Y5:


if it isn't difficult I want two screenshot in which I wouldlike to see and thw number of bar/cell tobe clear, cause on these pictures a see only the formulas
best response confirmed by emmapapa2 (Occasional Contributor)


Here is the sample workbook.

@Hans Vogelaar last question! COULD YOU HELP ME???



See the attached sample workbook.

This file is very useful, but I would like a way in which all this can operate automatically!
For example if I click region 1 ( in a cell) I take the result of the region 1. If 2 etc


The sample workbook that I attached already returns K(x3) for all regions...

I want a way like a choice !
For example if I choose the cell A1 with region 1 automatically I would like the excel calculate the result !
Like a multiple choice !


What's the point?

I have the areas that I sent you with the corresponding numbers!
I would like when one of my clients is from area 1, 2 or any other to be able to press the area to actually get me the result, so that I can use it in later calculations


You can do that with the sample workbook.