Solved: Feeling dumb today!!!

[marshybid]

OK, I need to write a formula to then populate down a column of cells, please help;

Column A = Order date (if no order then A=<Null>)
Column B = Order confirmation date (if no order then B=<Null>)

This is essentially the data that I start with.

I would like a formula to do the following;

C1=IF(A1<>"<Null>",IF(B1 between today and today -7 THEN "1", " ")

I think I just left my brain at home in bed today!!!

Thanks,

Marshybid

[tinyjack]

"" is what you could use for "<Null>" & TODAY() might be of help.

HTH

TJ

[RichardSchollar]

Hi

=IF(ISNUMBER(A1)*(B1>TODAY()-7)*(B1<=TODAY()),1,"")

You may want to amend the > and <= operators dependent on your exact requirements.

Richard

[marshybid]

Hi

=IF(ISNUMBER(A1)*(B1>TODAY()-7)*(B1<=TODAY()),1,"")

You may want to amend the > and <= operators dependent on your exact requirements.

Richard

Richard,

Perfect solution to the problem. I must remember my brain in the morning.

I'm not familiar with the way you have written the formula though??

What does * do? It seems to serve more than one purpose.

Thanks for the help. Marking as solved

Marshybid

[RichardSchollar]

I used the * (ie the multiplication operator) between each term of the conditional IF argument (ie the bit that results in True or False, telling Excel which value for the If to return) because it produces an AND effect:

only if the 3 terms are all True (terms being Isnumber(a1) for example as well as the two bracketed expressions using TODAY()) will the IF execute the True result (because True*True*True = 1, but anything other than 3 Trues will result in a 0 (or False)).

Make sense?

[marshybid]

I used the * (ie the multiplication operator) between each term of the conditional IF argument (ie the bit that results in True or False, telling Excel which value for the If to return) because it produces an AND effect:

only if the 3 terms are all True (terms being Isnumber(a1) for example as well as the two bracketed expressions using TODAY()) will the IF execute the True result (because True*True*True = 1, but anything other than 3 Trues will result in a 0 (or False)).

Make sense?

Makes perfect sense now.

I had just never seen a formula written using the * in that way. Now I have I will put it to good use.

Once again VBA Express wins the day.

Thanks loads.

Marshybid

[Bob Phillips]

I used the * (ie the multiplication operator) between each term of the conditional IF argument (ie the bit that results in True or False, telling Excel which value for the If to return) because it produces an AND effect:

only if the 3 terms are all True (terms being Isnumber(a1) for example as well as the two bracketed expressions using TODAY()) will the IF execute the True result (because True*True*True = 1, but anything other than 3 Trues will result in a 0 (or False)).

Make sense?

OK, so why didn't you use a bog-standard AND(condition,condition,condition) - * being used to emulate AND should only be used when AND fails - IMO.

[marshybid]

OK, so why didn't you use a bog-standard AND(condition,condition,condition) - * being used to emulate AND should only be used when AND fails - IMO.

Hi xld,

Is there a reason why AND would fail? Also, how would the use of * fix this problem?

This was why I went back to Richard initially as I had never seen * being used in this way.

Thanks,

Marshybid

[Bob Phillips]

AND does not fail in your situation.

It will fail if you try to AND an array, because AND does not return an array, just a single value, then you use *.

[marshybid]

As ever, learning something new every day.

Thanks for the clarification

Marshybid

[RichardSchollar]

OK, so why didn't you use a bog-standard AND(condition,condition,condition) - * being used to emulate AND should only be used when AND fails - IMO.

It was just the way the formula flowed thru my fingers!

By the way Bob, your site appears to be down - is it only temporary or more long term?

EDIT: Scratch that - it ain't down anymore...

[Bob Phillips]

By the way Bob, your site appears to be down - is it only temporary or more long term?

EDIT: Scratch that - it ain't down anymore...

I felt that statement coming through the ether, so I upped and fixed it!

[RichardSchollar]

I guess we'll have to add another shot of the Sapphire to my round

[Bob Phillips]

No, I think I owe you that one!