Excel help needed for a formula, please

Excel help needed for a formula, please

Didn't find your answer?

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.

avatar
By paulwakefield1
24th Aug 2011 11:01

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.

Thanks (0)
avatar
By avmmusic
24th Aug 2011 11:37

/ 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.

Thanks (0)
avatar
By cswan
24th Aug 2011 14:22

Both,

 

All sorted now, thanks very much!

Thanks (0)