• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

IF formula is not working correctly - Need to find out if mentioned date is between 2 dates or not

Pinang

Member
Hi,

I have applied correct formula but result is still showing "Correct". it should be "Error".

Attached image for reference. Refer cell D4, where I have put condition to find out if Actual end date is falling between Start and End Date but result is still showing "Correct".

Date format is "DD.MM.YYYY".

Please help.
 

Attachments

  • Capture.PNG
    Capture.PNG
    11.4 KB · Views: 2
Pinang
I remember that I've noticed something same a long time ago...
If 'date' is written as dd.mm.yyyy then Excel for some great reason do not always understand it as date.
I would fill dates as dd/mm/yyyy and format it as dd.mm.yyyy ... then it works better.
I tested few more rows in Your file and You can see how do it works.
 

Attachments

  • date.xlsx
    9.6 KB · Views: 3
Thanks vletm

In that case, I have to use this formula
=IF(AND(TEXT($C4,"DD/MM/YYYY")>=TEXT($A4,"DD/MM/YYYY"),TEXT($C4,"DD/MM/YYYY")<=TEXT($B4,"DD/MM/YYYY")),"Correct","Error")
 
Pinang
I would myself fill those dates as dd/mm/yyyy and show (with format) as wanted.
If You compare two TEXTs as 01.12.2019 and 31.12.2019 it won't give wanted result.
... but You can test those Yourself
 
In that case, I have to use this formula
=IF(AND(TEXT($C4,"DD/MM/YYYY")>=TEXT($A4,"DD/MM/YYYY"),TEXT($C4,"DD/MM/YYYY")<=TEXT($B4,"DD/MM/YYYY")),"Correct","Error")
More like:
Code:
=IF(AND(DATE(RIGHT($C2,4),MID($C2,4,2),LEFT($C2,3))>=DATE(RIGHT($A2,4),MID($A2,4,2),LEFT($A2,3)),DATE(RIGHT($C2,4),MID($C2,4,2),LEFT($C2,3))<=DATE(RIGHT($B2,4),MID($B2,4,2),LEFT($B2,3))),"Correct","Error")

but as vletm suggests, get the dates as proper dates in columns A:C. This macro run while your sample sheet is active will do that:
Code:
Sub blah()
For Each colm In Range("A2:C6").Columns
  Colm.TextToColumns Destination:=Colm, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 4), TrailingMinusNumbers:=True
Next colm
End Sub
 
Back
Top