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(I SNUMBER(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

On Feb 17, 7:49*pm, winnie123
wrote:
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(I SNUMBER(SEARCH("export",H*57)),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

You should use Nested IF like this;
=IF(ISNUMBER(SEARCH("sea",H57)),IF(ISNUMBER(SEARCH ("export",H57)),C57+28,C57+7),C57+7)
and format the result cell as DATE...

39833 represents 1/20/2009 in Excel...

Since you have two IFs with an & you are getting
3983339833



"winnie123" wrote:

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(I SNUMBER(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

Winnie,

Try this

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

Mike

"winnie123" wrote:

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(I SNUMBER(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

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(SEARC H("export",H57))),C57+28,C57+7)

"winnie123" wrote:

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(I SNUMBER(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

Thankyou Pete that does it.

I am so greatful for this site :-)


"Pete_UK" wrote:

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

On Feb 17, 7:49 pm, winnie123
wrote:
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(I SNUMBER(SEARCH("export",HÂ*57)),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

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

"Sheeloo" wrote:

You should use Nested IF like this;
=IF(ISNUMBER(SEARCH("sea",H57)),IF(ISNUMBER(SEARCH ("export",H57)),C57+28,C57+7),C57+7)
and format the result cell as DATE...

39833 represents 1/20/2009 in Excel...

Since you have two IFs with an & you are getting
3983339833



"winnie123" wrote:

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(I SNUMBER(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

Here is another method for you to consider...

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

--
Rick (MVP - Excel)


"winnie123" wrote in message
...
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(I SNUMBER(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

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"}))

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
Here is another method for you to consider...

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

--
Rick (MVP - Excel)


"winnie123" wrote in message
...
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(I SNUMBER(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

Glad to hear that - thanks for feeding back.

Pete

On Feb 17, 8:35*pm, winnie123
wrote:
Thankyou Pete that does it.

I am so greatful for this site :-)

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


"winnie123" wrote:

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(I SNUMBER(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