I'm trying to create a formula that says if Date1 is before the date in a fixed field, AND date 2 is after the date in fixed field 2, THEN action A, else action B. But I'm not using range names, and I think I'm confusing the spreadsheet with dates I think are numbers and it thinks are text, as I'm geeting a #name error?
Any help much appreciated.
Thanks
Replies (3)
Please login or register to join the discussion.
Hard wired dates?
Are you "hard wiring" Date1 and Date2 into the formula?
If not, your formula could look like =if(and(A1<$D$1,A2>$d$2),Action A,Action B)
where column A contains the varaible dates and Column D carries the fixed dates and make sure the entries in Columns A and D are formatted as dates.
If you have to "hardwire" your dates, you could substitute Date(year,month,day) for the relevant cell reference.
/ not .
Something that I find all the time is that some people are in the habit of typing dates using dot seperators instead of slash. If you type a date as 31.03.2011 instead or 31/03/2011 excel doesn't realsie its a date, even if you try to change it in the cells formatting options.
Therefore if you try to reference this value in a formula excel can't deal with it as it would if it were in the format using the /
If that's not the case I agree with paulwakefield1 above. That's what the formaula should look like.