my uncooperative "IF"

[dkz3r0]

I am trying to create an IF formula that I can use to create a visual aid in tracking time when services are being offered.

The formula that I am currently trying is:
=IF(AND($B:$B="11/1/2010", $C:$C="8:50:00 AM"), "Y1", " ")

In Column B there multiple fields with "11/1/2010" expressed in the (Date)*3/14/2001 format, and in Column C there are multiple fields with "8:50:00 AM" expressed in the (Custom)h:mm format.

I am obviously entering the formula wrong because I need for the TRUE statement to populate anytime the date and time are found in the columns, but regardless, it's always false. I would appreciate any and all help with this formula.

 

[schielrn]

Welcome to the Board!

Maybe try this if you are using excel 2007 or newer:

=IF(sumproduct(($B:$B="11/1/2010"*1)*($C:$C="8:50:00 AM"*1)), "Y1", " ")

or:

=IF(countifs($B:$B,"11/1/2010"*1,$C:$C,"8:50:00 AM"*1), "Y1", " ")

If you ware using something older than Excel 2007, you cannot use whole ranges within sumproduct, so you would need something like:

=IF(sumproduct(($B$1:$B$10000="11/1/2010"*1)*($C$1:$C$10000="8:50:00 AM"*1)), "Y1", " ")

Hope that helps.

 

[texasalynn]

Welcome to MrExcel board...

Yes your formula is currently saying it is only true when every cell in column B & C contains those values. If you are copying the formula down to each row then you would want

Excel Workbook
	B	C	D
2	11/1/2010	8:50:00	Y1
3	11/5/2010	8:50:00	N
Sheet1

Excel 2003

Cell Formulas
Range	Formula
D2	=IF(AND(B2=DATE(2010,11,1),C2 =TIME(8,50,0)), "Y1", "N")

 

[dkz3r0]

Thank you much Texasalynn & Schielrn!

I tried all of your suggestions and got the most use from the =IF(countifs) formula. Now that the hard part is done, I have the tedium of filling cells.

 

[dkz3r0]

One more question:

If I wanted to change the date from 11/1/2010 to 11/2/2010 for the entire row, is there a way I can update all the cells at the same time?

 

[texasalynn]

if you highlight the row then use the replace
Ctrl + H
Find: 11/1/2010
Replace 11/2/2010
change the drop down for look in to values

 

[dkz3r0]

DUH.. you rock my world.. appreciate it!!