Excel: if cell contains any date?

Question

I need a formula that is for the following.
If cell A3 contains a date such as (27/3/13) then cell C14 should populate with a 1.
but this also needs to work for if A4 and so on contains a date, then C14 should populate from 1 to 2.

Also whilst you're at it. I need another formula for Q3 if it contains a date to remove the 1 from C14.

Thanks!

Answer 1

A PARTICULAR date? If you mean just "contains a date of any kind" Excel doesn't know a date from a doorknob; everything is a number to Excel. It makes guesses (not always correctly) based on the format you choose to enter things in as to whether or not you INTENDED an entry to be a date and changes the number it's seeing if it thinks you intended a date, but 27/3/13 is only a DATE because of it's format. To Excel, 27/3/13 is actually 41360.



What you are describing can't be done with a single formula. If I needed to do it, I consider using VB, or I'd write a bunch of individual formulae and hide the cells I placed them in. But that's just me. YMMV. But it's impossible to do what you describe in a single cell, and your biggest challenge is how to tell Excel what constitutes a date. I would define a range as "date" and I'd use multiple cells to achieve the results you describe and hide them.

Answer 2

Contains Excel

Answer 3

Excel If Contains

Answer 4

The first part for C14 would be

=IF(A3=DATE(2013,3,27),1," ")



Then just copy the formula down.



You can't put a formula somewhere to tell it to remove the 1 in C14. The reason is because C14 is already being controlled by the above IF formula. The only way to remove the 1 in C14 is to change the date in A3.

Answer 5

I ve been trying this, and the problem I find with the examples above is that any number shows as a date, so instead I ve been forced to go with checking the format of the cell to see if it displaying as a date:



=IF(CELL("format",B4)="D1","Yes","No")



The above formula is checking if cell B4 is in a date format or "D1". It also has the benefit of respondign with No if the cell is empty (even if the cell is set to a date format)

Answer 6

problematic stuff. try searching from google and yahoo. that can help!

Answer 7

The following works very well for me:



=IF(ISNUMBER(DAY(A13)),"date","")

Answer 8

c14 =if(q3,0,count(a3:a4))