• 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.

Date comparison isn't working

In the attached file, I am checking to see if the date in column A (sheet 1) is between the range in columns B:C of sheet 2.

The first comparison works, but why does the second part of the AND statement evaluate to false?

12.09.2014 is <= 05.14.2015 so I don't see what is wrong.
 

Attachments

  • Chandoo Question.xlsx
    8.9 KB · Views: 6
All your dates are Text, Not Dates

Goto Each date on each sheet in turn
Press F2, then Enter

Go back to Sheet 1
Press F9

I get:
upload_2015-2-28_12-2-53.png
 
Hi ,

Just as a tip :

When you see a date , if you change the format to General , you should see numbers ; if the date is just a date ( without a time component ) , you should see integer values , and if the date also has a time component , you should see decimal values.

For example , if you see a date such as February 28, 2015 , and when you change the cell format to General , you see 42063 , you can be sure it is a valid date ; if you see 42063.4375 , you can be sure it is a valid date which also has a time component.

If you see it still as February 28, 2015 or even as 28-02-2015 or any other date value in any format , it cannot be a valid date , at least as far as Excel is concerned.

Narayan
 
One more thing to add, which is of course a very basic check for dates which are treated as numbers internally. If you see your dates they are aligned left which Excel do for TEXT, so if you had not done it purposefully than you will come to know that your dates are not valid.

Regards,
 
Hi ,

Just as a tip :

When you see a date , if you change the format to General , you should see numbers ; if the date is just a date ( without a time component ) , you should see integer values , and if the date also has a time component , you should see decimal values.

For example , if you see a date such as February 28, 2015 , and when you change the cell format to General , you see 42063 , you can be sure it is a valid date ; if you see 42063.4375 , you can be sure it is a valid date which also has a time component.

If you see it still as February 28, 2015 or even as 28-02-2015 or any other date value in any format , it cannot be a valid date , at least as far as Excel is concerned.

Narayan

Good information. Using your suggestion I was able to pinpoint the problem. It is with the dates on the second sheet. Apparently they are formatted as text. How can I convert those to dates as a date format.

I tried using TEXT(B8,"mm/dd/"yyyy") and that didn't seem to work.
 
Good information. Using your suggestion I was able to pinpoint the problem. It is with the dates on the second sheet. Apparently they are formatted as text. How can I convert those to dates as a date format.

I tried using TEXT(B8,"mm/dd/"yyyy") and that didn't seem to work.

I've got it now. Using the DATEVALUE ( ) function will convert a text date to a true date. Now it works.
 
I tried your suggestion, and I still get the same results.

I'll suggest you didn't change the Dates on the 2nd sheet!

Otherwise how did i get these results:
upload_2015-3-1_13-29-30.png

see attached:
 

Attachments

  • Chandoo Question.xlsx
    9.1 KB · Views: 0
Back
Top