IF function yet want to stop counting when another cell has data entered.

=IF(U90="","",($E$5-Q90))

Hi all,

The above IF function is being used to only calculate when cell U90 is empty. Yet is there a way to stop the function calculating when a date is entered into cell U90?

Dates in Excel are stored as numbers and then formated to a date format, therefore Excel cannot distinguish between a number and a date. As long as this is OK, then you can use the formula:-

=IF(OR(U90="",ISNUMBER(U90)),"",($E$5-Q90))

However, if you want to look for dates only, you would have to format the cell as text (which would then stop any validation on a date being entered correctly i.e. 31/4/08, would get converted to 1/5/08 normally, however if you enter it as text it will stay as 31/4/08), you could then change the fomula to:-

=IF(OR(U90="",AND(LEN(U90)=8,MID(U90,3,1)="/",MID(U90,6,1)="/")),"",($E$5-Q90))

The above requires the date to be entered in the format of DD/MM/YY, or MM/DD/YY. If you want any other format you would need to amend the formula. The above is checking for the text to be 8 characters long and in postion 3 and 6 there is a "/".

Hope this helps

Thanks for the reply Gary.

I think the 1st formula is more suitable to what I want to achive.

I've attached an example of the spreadsheet to give a clearer view.