If statement to show 'N/A' or subtract dates

[smithnjs2988]

Hi all,

I am hoping someone can help me, my knowledge is not the best when it comes to if statement!

I have attached some test data with a comments column showing the results I would like to see.


IF status = CN storage only starts if delivered after 10 days from ETA
IF status - PK storage only starts if delivered after 7 days from ETA

If delivered within 10 days for CN - display N/A
If delivered within 7 days for PK - display N/A


I appreciate any help

[etaf]

A sample sheet would help here

The forum does allow for spreadsheets to be uploaded direct to the forum -

Please see the Yellow Banner at the top of the page explaining how to attach a sample spreadsheet

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

Here are the instructions, found at the top of the page again

Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. For a new thread (1st post), scroll to Manage Attachments, otherwise scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

[smithnjs2988]

Hi Wayne,


I thought I attached the test data, just done it again

Thanks,

[etaf]

how about
=IF(AND(A2="CN",(C2-B2)>10),"Storage Starts "&TEXT(B2+10,"DD/MM/YYYY"),IF(AND(A2="PK",(C2-B2)>7),"storage Starts "&TEXT(B2+7,"DD/MM/YYYY"),"N/A"))

only i make the PK - 7 days the 8th Not the 18th

[smithnjs2988]

Hi Wayne,

Wow quick work.

That works great, thank you.

One last thing, if I only want to display the storage date and remove the text, which part of the formula do I need to remove?

Also, would it be possible to add if Del Field is blank and ETA is more than (7 days Pk or 10 days CN) in the past to display the display the date in the storage start date field?

I hope this makes sense.

Thanks

[etaf]

=IF(AND(A2="CN",(C2-B2)>10),B2+10,IF(AND(A2="PK",(C2-B2)>7),B2+7,"N/A"))

make sure
the cells / column are formatted as a date

if cell del date is blank and tests are based as if Del date is today

=IF(OR(AND(A2="CN",(C2-B2)>10),AND(A2="CN",C2="",(TODAY()-B2)>10)),B2+10,IF(OR(AND(A2="PK",(C2-B2)>7),AND(A2="PK",C2="",(TODAY()-B2)>7)),B2+7,"N/A"))

see last entry row 9 where i have changed the eta date

[smithnjs2988]

Great, thank you so much Wayne

[etaf]

you are welcome
Merry Xmas

[smithnjs2988]

Hi Wayne,

I tried adding another statement, but had an error when trying to add GB where the statement references PK, IS there a way to include both references in the same statement?

=IF(OR(AND(A2="CN",(C2-B2)>10),AND(A2="CN",C2="",(TODAY()-B2)>10)),B2+10,IF(OR(AND(A2="PK",(C2-B2)>7),AND(A2="PK",C2="",(TODAY()-B2)>7)),B2+7,"N/A"))

[etaf]

add GB where the statement references PK

can you provide more detail

OR(AND(A2="PK",(C2-B2)>7),AND(A2="PK",C2="",(TODAY()-B2)>7))
is this another OR , replacing PK with GB

if so

OR(AND(OR(A2="PK",A2="GB"),(C2-B2)>7),AND(OR(A2="PK",A2="GB"),C2="",(TODAY()-B2)>7))


as below
=IF(OR(AND(A2="CN",(C2-B2)>10),AND(A2="CN",C2="",(TODAY()-B2)>10)),B2+10,IF(

OR(AND(OR(A2="PK",A2="GB"),(C2-B2)>7),AND(OR(A2="PK",A2="GB"),C2="",(TODAY()-B2)>7))

,B2+7,"N/A"))

[AliGW]

Administrative Note:

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

[smithnjs2988]

Hi Wayne,

Sorry, I didn't give a clear explanation.

Basically, I would like to edit the formula where it checks for PK to check for GB too and apply the same logic as PK.

Thanks for all your help.

P.S. can you recommend any tutorials as you can see i'm not very good with if statements

[etaf]

the formula i posted should do that
it will check if PK OR if GB is in the cell and then it will apply the same logic , for PK or for GB

Or if you nolonger wanted to have PK in the formula - just replace with GB

this is more of a LOGIC issue rather than IF

AND/OR/NOT/ functions

[smithnjs2988]

Hi Wayne,

Me again, sorry!

I have put this if statement together, but it's wrong.

The results i want is, IF cell Y2 display's N/A display N/A or if it got a date subtract Y2 - M2

=IF(OR(Y2="N/A",(M2-Y2)))

Any help would be appreciated.

[AliGW]

Thank you. The thread may now proceed.

[smithnjs2988]

Hi Wayne,

Me again, sorry!

I have put this if statement together, but it's wrong.

The results i want is, IF cell Y2 display's N/A display N/A or if it got a date subtract Y2 - M2

=IF(OR(Y2="N/A",(M2-Y2)))

Any help would be appreciated.

[AliGW]

Try this:

=IF(ISNA(Y2),NA,M2-Y2)

[etaf]

=IF( Y2="N/A", "N/A",M2-Y2)

of if N/A is an error #N/A and NOT text N/A

=IF( ISNA(Y2) , "N/A", M2-Y2)

[smithnjs2988]

Great, thanks both

[AliGW]

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.