Hi,

I have tried to write a formula today, I think it is right but it is a
pretty complicated IF so here goes.

I have an order date and a works started date. I want to highlight orders
where work has not been started two weeks after the order date. I wrote the
IF formula original to work off the argument TODAY()(B2+14) but this caused
the problem that if B2 was null then it entered 14/01/1900. I then needed to
adda further IF. IF B2 is null do nothing, if a date is present add 14. This
way the first IF only works if B2 has a value. I hope that wasnt too many
IF's!!!!

Here's what I came up with, it works but I would like your opinion on if it
is the most efficient way or if there is any glaringly obvious holes. I'm a
bit close to the project to judge properly :-)

Date order recieveid is in column B, this formula is in column C and date
work started is in column I.

=IF((TODAY()(IF(B2="","",B2+14)))*(I2=""),"OVERDU E","")

Hi

Your formula works fine.
I think I might have written it as
=IF(B2="","",IF(AND(TODAY()B2+14,I2=""),"OVERDUE" ,""))
as it seems a little clearer to me
Basically if B2 is blank, do nothing, otherwise
if both the condition Today is more than 14 days after B2, AND, I2 is blank,
then enter Overdue
--
Regards
Roger Govier

"Pyrite" wrote in message
...
Hi,

I have tried to write a formula today, I think it is right but it is a
pretty complicated IF so here goes.

I have an order date and a works started date. I want to highlight orders
where work has not been started two weeks after the order date. I wrote
the
IF formula original to work off the argument TODAY()(B2+14) but this
caused
the problem that if B2 was null then it entered 14/01/1900. I then needed
to
adda further IF. IF B2 is null do nothing, if a date is present add 14.
This
way the first IF only works if B2 has a value. I hope that wasnt too many
IF's!!!!

Here's what I came up with, it works but I would like your opinion on if
it
is the most efficient way or if there is any glaringly obvious holes. I'm
a
bit close to the project to judge properly :-)

Date order recieveid is in column B, this formula is in column C and date
work started is in column I.

=IF((TODAY()(IF(B2="","",B2+14)))*(I2=""),"OVERDU E","")

I might have written your formula like this:

=IF(LEN(B2)=0,"",IF(AND(TODAY()(B2+14),LEN(I2)=0) ,"OVERDUE",""))

or like this:

=IF(OR(LEN(B2)=0,LEN(I2)0),"",IF(TODAY()(B2+14), "OVERDUE",""))

But your formula gives the same result, so it's just a matter of preference
and clarity.

Hope this helps,

Hutch

"Pyrite" wrote:

Hi,

I have tried to write a formula today, I think it is right but it is a
pretty complicated IF so here goes.

I have an order date and a works started date. I want to highlight orders
where work has not been started two weeks after the order date. I wrote the
IF formula original to work off the argument TODAY()(B2+14) but this caused
the problem that if B2 was null then it entered 14/01/1900. I then needed to
adda further IF. IF B2 is null do nothing, if a date is present add 14. This
way the first IF only works if B2 has a value. I hope that wasnt too many
IF's!!!!

Here's what I came up with, it works but I would like your opinion on if it
is the most efficient way or if there is any glaringly obvious holes. I'm a
bit close to the project to judge properly :-)

Date order recieveid is in column B, this formula is in column C and date
work started is in column I.

=IF((TODAY()(IF(B2="","",B2+14)))*(I2=""),"OVERDU E","")