IF Statement for Date column

[Opus1]

Hi Everyone,

I have posted this question in Mr. Excel.com and still looking for some suggestions.
Please reply in Mr. Excel.com and appreciating any and all assistance.


http://www.mrexcel.com/forum/showthread.php?t=626180

[Cutter]

You're asking someone in this forum to go to another forum to provide an answer????

[Opus1]

Hi Cutter,
I am just following the rules Paul one of the forum moderators has sent me http://www.excelguru.ca/content.php?184
Under How to Post and When to Cross-Post, one point mentioned •Ask people to post their replies in the original forum.

[davesexcel]

Hi Cutter,
I am just following the rules Paul one of the forum moderators has sent me http://www.excelguru.ca/content.php?184
Under How to Post and When to Cross-Post, one point mentioned •Ask people to post their replies in the original forum.

Check out rule #8 in the forum rules.

8. Don't cross-post without a link. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post. Expect cross-posts without a link to be closed.

Although you provided the cross-post link, you did not ask the question in this forum, instead you linked us to your question in another forum.

The rule is, if you are going to ask the same question in another forum, you are to supply a link to it, not supply a link to the question in another forum and ask us to answer it there.
You should inform us if you question has been answered in the other forum.

[Opus1]

Hi Dave,

Thank you for the informing me on the proper procedures on cross-posting.
Here is the original question that I asked in the other forum:


Hi Everyone,

I have decided I need 1 of 4 different error codes to show up in cell C1. Column A is my date column specifically A1 to A20 with my target year being 2011 and my date format is MM/DD/YY,
so if I enter the date 01/37/11, I would like to see the error code "FIX DAY" show up in cell C1.
If I enter the date 15/25/11, I would like to see "FIX MONTH" show up in cell C1.
If I enter the date 04/02/14, I would like to see "FIX YEAR" show up in cell C1.
And finally, if cell A1 has the date 02/17/11 and cell A2 the date 03/21/11 and cell A3 the date 01/09/11, I would like to see "FIX DATE" show up in cell C1 because A3's date is earlier then A1's and A2's. The date need to be in sequential order from a start date going forward.

Is this possible to accomplish in a IF statement?

[Opus1]

Hi Bebo021999,

Thank you very much for your reply to my inquiry. It comes closest to the results I am looking for.

I have noticed thou if I put a date such as 15(month) 25(day) 2011(year), it still responds with "Fix Day". It does not pick up "Fix Month" from the formula. Is there a way to have "Fix Month" show up if the month enterd is > 12?

Many Regards

[Cutter]

I was not aware that cross posters were being sent a link to that site. You're right, it does say that. I can't see that being OK with this forum's owners, though.
I have provided a solution there.

Oh, and sorry for your loss (the Leafs).

[bebo021999]

Ithink it should be better to post complete query here.
Anyway, here is my solution in C1:

=IF(NOT(ISNUMBER(DAY(A1))),"FIX DAY",IF(NOT(ISNUMBER(MONTH(A1))),"FIX MONTH",IF(YEAR(A1)<>2011,"FIX YEAR",IF(COUNT(--($A$2:$A$3-$A$1:$A$2>0))<>SUM(--($A$2:$A$3-$A$1:$A$2>0)),"FIX DATE",""))))

Confirmed with Crrl-Shift-Enter

[Opus1]

Hi Cutter, thanks for the response.



I tried your formula and it works but I need greater details in the error codes such as "Fix Year" if it does not match the target year of 2011

and "Fix Month" if the month is greater then the 12th month and "Fix Day" if the day is greater then the 31st day and finally "Fix Date" if the

column of dates are not in sequetial order from the 1st date entered and in regards to the Leafs...who says time heels all wounds