-1

it pours!),

I am trying to write a statement in MS Excel 2013 that does the following:

If any cell within Column Y (except for cell Y1 - as it is a heading) contains a date, then output "Yes" in cell Z2.

Else output "No" in cell Z2

Courtenay L
  • 55
  • 8
  • Please refine your question: in excel, the format of a cell does not determine its value. You might have a valid date value in a cell, but displayed with a number format. And the other way around, you might have a cell with a date format, but containing an invalid date. – PA. Jul 22 '15 at 08:25
  • @PA - thank you for the feedback - I have refined my question (ie removed the format component). Sorry for any confusion. – Courtenay L Jul 22 '15 at 10:23

2 Answers2

1

I firmly believe you're looking for this:

=ISNUMBER(IF(ISNUMBER(I2);I2;DATEVALUE(I2)))

It returns TRUE if it's either a datenumber or a date stored as a text. I'm sure you can lookup the {=SUM(IF())} trick that looks for your "any cell" like this:

{=IF(SUM(IF(ISNUMBER(IF(ISNUMBER($Y$2:$Y$1048576),$Y$2:$Y$1048576,DATEVALUE($Y$2:$Y$1048576))),1,0))<>0,"Yes","No")}

Alternatively you may want to check texts only, in which case you want to check for something else. Stay tuned, I'm trying to create that filthy mess of a formula.
Disclaimer: separators are ;s.
EDIT: back with some more heat.
Since you seem to be picky and looking for the "yy/mm/dd" format I cut it back into parts.
Year: =MID(I2;1;2)
Month: =MID(I2;4;2)
Day: =MID(I2;7;2)
You can re-create the date from this: =DATE(MID(I2;1;2);MID(I2;4;2);MID(I2;7;2))
Unfortunately it accepts say "14/12/32" and outputs #[19]15/01/01#. You've got to check for each of the dateparts (or at least two) if they fit your expectations.
Year: =RIGHT(YEAR(DATE(MID(I2;1;2);MID(I2;4;2);MID(I2;7;2)));2)=MID(I2;1;2)
Month: =MONTH(DATE(MID(I2;1;2);MID(I2;4;2);MID(I2;7;2)))&""=MID(I2;4;2)
Day: =DAY(DATE(MID(I2;1;2);MID(I2;4;2);MID(I2;7;2)))&""=MID(I2;7;2)
The full logical output will look like this (you can take away one argument): 

=AND(RIGHT(YEAR(DATE(MID(I2;1;2);MID(I2;4;2);MID(I2;7;2)));2)=MID(I2;1;2);MONTH(DATE(MID(I2;1;2);MID(I2;4;2);MID(I2;7;2)))&""=MID(I2;4;2);DAY(DATE(MID(I2;1;2);MID(I2;4;2);MID(I2;7;2)))&""=MID(I2;7;2))
user3819867
  • 1,114
  • 1
  • 8
  • 18
  • thank you for the comprehensive answer. I have refined my question (ie removed the dd/mm/yy format component). I am unsure if that will change your answer? I have left work for the day so can confirm your answer tomorrow :) – Courtenay L Jul 22 '15 at 10:25
  • Make sure you replace the `I`s with `Y`s, the `;`s with `,`s and you induce the brackets yourself (i.e. press `Ctrl`+`Shift`+`Enter` in lieu of `Enter`) – user3819867 Jul 22 '15 at 10:37
0

upon talking to the business, it seems my question is now no longer required. Rather then input "yes" into cell Z2, they are now OK with the earliest date mentioned within Column Y. To achieve this I put the following into cell Z2

=MIN($Y:$Y)

not elegant, but it now meets what they asked for (assuming they do not change their mind again).

Thank you for those who replied, in particular @user3819867

Courtenay L
  • 55
  • 8