8

The following formulas in Excel 2010 both give me a "No" answer:

Formula:

=IF(S8="24/03/2015","Yes","No")

where S8 has format/value dd/mm/yyyy / 24/03/2015 

=IF(S9>"30/06/2013","Yes","No")

where S9 has format/value dd/mm/yyyy / 24/03/2015

The data was originally loaded from a text file generated with the dos command dir /s > Dir.txt.

I've tried copying/formatting values, datevalue() and other internet suggested solutions to no avail.

I need a Guru's help!

Aaroninus
  • 280
Stan the Man
  • 81
  • I had that problem once, and it was the fact that one of the date comprised the hours, they did not show in the cell because of the format , but one were all 00:00 and the other had different values. Can you check your formulas by copy/paste value one of the date in the other column ? – P. O. May 28 '15 at 16:35

5 Answers5

8

Take the quotes off of the dates in your formula. It is trying to find the greater of two text values instead of the greater of two date values.

edswartz
  • 215
  • 3
    This won't work. Try it before you post. – Mr. Mascaro May 28 '15 at 16:26
  • I did test the scenario on a DEFAULT page and it did (does) work. However, the scenario you describe in your answer is more precise and therefore more relevant to the question. In any event, the user needs to be cognizant of the underlying data type the function is trying to address. – edswartz May 30 '15 at 02:18
  • I've tried and neither it works at my side. Instead of interpreting correctly as date, Excel performs two division. – Máté Juhász Dec 08 '23 at 12:02
8

if your S8 is date,

try this

=IF(S8 = Date(yyyy;mm;dd);"Yes";"No")

if your S8 is date, compare it with date as well...

  • Thanks but it still didn't work. – Stan the Man May 28 '15 at 02:15
  • what does the error say? –  May 28 '15 at 02:16
  • No Error, for method 1 - after entering the function and pressing [Enter] the month in the formula deleted the zero to leave 24/3/2015 and the answer was still No – Stan the Man May 28 '15 at 02:19
  • 4
    For option 0 above, entering the formula returned an error - possible because of the use of ";" as separators instead of commas. After entering "," separators, the formula returned the right answer - Thank you very much!! – Stan the Man May 28 '15 at 02:23
  • haha, ; and , is different in different country...

    I didn't figure it....

     –  May 28 '15 at 02:24
  • 1
    hahaha... the separator ; and , is different in another country. I forgot to figure that.

    don't forget to mark as accepted answer...

     –  May 28 '15 at 02:26
2

If dates are stored as date but formatted as dd/mm/yyyy you can use this function:

=IF(S8=DATE(2015,3,24),"Yes","No")
Salman A
  • 1,722
0

You can use these functions without any fuss:

=IF(S8=DATEVALUE("24/03/2015"),"Yes","No")
=IF(S8=DATE(2015,3,24),"Yes","No")

But be warned, if your date inside the DATEVALUE formula is not the same as your localization settings there will be ambiguity. For example: 5/1/2015. Is it January 5th or May first?

Mr. Mascaro
  • 418
0

I tried this and it worked in Excel 2010:

=IF(S8=DATEVALUE("24/3/2015"),"Yes","No")
=IF(S9>DATEVALUE("30/06/2013"),"Yes","No")

Basically what's going on it's that you have "24/3/2015" but excel may take this a 2 divisions, like 24 divided by 3, and then that divided by 2015.

Felipe Gavilán
  • 101