Here again for help

[winnie123]

I have a formula, not sure if its the correct one for what I want to do

Column C shows the despatch date
Column H shows the method of shipment

I have the following formula in Column I

=IF(ISNUMBER(SEARCH("sea",H57)),C57+28,C57+7)&IF(ISNUMBER(SEARCH("export",H57)),C57+28,C57+7)

This should result in a new date which if the method of shipment is either
sea or export it will add 28 days to the Despatch date if not then it will
add 7 days.

Column I is formatted to date but the value I get is numeric (I think it
could be a date series)

When I just had the first criteria of "sea" it worked and the result showed
as a date but now I have added the second criteria of "export" the result is
for example 3983339833.

What am I doing wrong??
Thanks
Winnie

 

[Pete_UK]

Both halves of the formula are adding days to the date, but the &
symbol is then just butting the serial numbers that those new dates
represent together, so you have 39833 & 39833. I think you want to do
something like this:

=IF(OR(ISNUMBER(SEARCH("sea",H57)),ISNUMBER(SEARCH
("export",H57))),C57+28,C57+7)

Hope this helps.

Pete

 

[Mike H]

Winnie,

Try this

=IF(ISNUMBER(SEARCH("sea",H57)),C57+28,IF(ISNUMBER(SEARCH("export",H57)),C57+7,""))

Mike

 

[~L]

You are performing two separate searches rather than one search for two
criteria and then joining the results. 3983339833 is 39833 repeated, which
is not yet recognized as a date, so you get a number instead.

Without knowing anything about the data, I'd try:

=IF(AND(ISNUMBER(SEARCH("sea",H57)),ISNUMBER(SEARCH("export",H57))),C57+28,C57+7)

 

[winnie123]

Thankyou Pete that does it.

I am so greatful for this site

 

[winnie123]

Thanks,

your formula didnt give me a Snumber but it just returned the false value so
was getting 7 days onto every despatch date.

Pete's suggestion did the trick.

Thanks for taking time out to respond

Winnie

 

[Rick Rothstein]

Here is another method for you to consider...

=C57+7+21*OR(H57={"export","sea"})

 

[Rick Rothstein]

I guess if there is a possibility that you will copy this formula down
through a range where no dispatch dates have been entered yet, then you
should use this formula in order to suppress the output...

=IF(C57="","",C57+7+21*OR(H57={"export","sea"}))

 

[Pete_UK]

Glad to hear that - thanks for feeding back.

Pete

 

[Teethless mama]

=SUMPRODUCT(--ISNUMBER(SEARCH({"sea","export"},H57)))*21+7+C57