Here again for help

  • Thread starter Thread starter winnie123
  • Start date Start date
W

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
 
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
 
Winnie,

Try this

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

Mike
 
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)
 
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
 
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"}))
 
Back
Top