Formula for date error checking if a certain date is greater than another

[Nathag]

Hi all,

I work in a business where we have a date driven process. As happens with all data, however, sometimes entry errors happen.

I want a formula that will check to make sure that the second record date (AE) is not greater than the first record date (AC).

However, when I write the formula =IF(AE2>AC2,"1","0) then it returns a 1 for some fields that shouldn't be flagged as errors.

I even tried wrapping them in the DATEVALUE function so that =IF(DATEVALUE(AE2)>DATEVALUE(AC2),"1","0"), but that didn't work either.

Part of the problem is that a date in AE might be 3/2/2018 and a date in AC might be 4/19/2019, which would normally make the date in AE less than AC, but when we translate them to number then AC = 42844 and AE = 43161, making AE greater than AC and messing up my world.

How might I write formula around this little problem and allow Excel to understand that 3/2/2018 (AE) really is less than 4/19/2019 (AC)?

 

[Special-K99]

"a date in AE might be 3/2/2018 and a date in AC might be 4/19/2019, which would normally make the date in AE less than AC,"

Thats only true if the dates are text. If they are numbers then the dates are Excel internal dates and your assumption would be incorrect.

Also your return codes should most probably be 1 and 0 and not "1" and "0"

You can test if a date is a number by referring to the cell it is in using ISNUMBER(), e.g. A1 has what looks like a date, in a blank cell enter ISNUMBER(A1)
If it says TRUE then its a date, it FALSE then it s text.

 

[Nathag]

"a date in AE might be 3/2/2018 and a date in AC might be 4/19/2019, which would normally make the date in AE less than AC,"

Thats only true if the dates are text. If they are numbers then the dates are Excel internal dates and your assumption would be incorrect.

Also your return codes should most probably be 1 and 0 and not "1" and "0"

You can test if a date is a number by referring to the cell it is in using ISNUMBER(), e.g. A1 has what looks like a date, in a blank cell enter ISNUMBER(A1)
If it says TRUE then its a date, it FALSE then it s text.

I am not sure I understood some of what you said or how it will help me.

Here is what I do know. When I run the query, the Excel output for the date fields are formatted as date in Excel because the query has those field types set to date. When I select the given date cells and choose either the number or general formats, then 3/2/2018 in AE becomes 43161 and 4/19/2019 in AC becomes 42844. The dates are stored as numbers but the greater the date does not necessarily translate into the greater the number, and therein likely lies my problem. How to run a formula to error check dates?

Beyond that, I don't know what to tell you.

 

[Special-K99]

The only way to check this properly is to post a file, posting an image as suggested by moderators will not work in this case.

Post a sample spreadsheet remove any sensitive data.
You cant attach files on this forum.
Upload the file to an online storage site and post a link to it, though some people may not download the file for fear of viruses.