Report

Funtion Formula Date and Text [Solved]

Ask a question Gerberreinette 1Posts Friday March 4, 2016Registration date March 4, 2016 Last seen - Last answered on Mar 7, 2016 01:38AM
Good day

I am looking for help with a function formula PLEASE.

Example

Decleration Start Date
2016.02.17

Last inv_ Inventory_ Decl frm 18 Feb 2016
2016.03.04_ IMPAIRED 201503_ Not Actioned
2016.02.18_ PENDING DONATIO_ Not Actioned
2016.02.19_ DECLARED_ Accepted
2016.05.13_ REJECTED160115_ Accepted


If the description in the Inventory column is "Rejected / Declared" and the date is after 2016.02.17 = the result in the Decl frm 18 Feb 2016 column should be "Accepted"
If the description in the Inventory column is anything else = the result should be "Not Actioned"

Thank you so much in advance for taking the time to help and for your answers - this is much appreciated.

Regards
See more 
Helpful
+0
moins plus
Hello Gerberreinette,

The followiing IF/AND formula should do the task for you:-

=IF(B5="","",IF(AND(A5>$A$2,B5="Declared"),"Accepted",IF(AND(A5>$A$2,B5="Rejected"),"Accepted","Not Actioned")))


As I don't know how your work book is set up, have a look at my test work book at the following link:-

https://www.dropbox.com/s/1weipbgysv5hn6g/Gerberreinette%28IF%20AND%20formula%29.xlsx?dl=0

You'll see that I have the Declaration Start Date in cell A2 and the other data in the range A4:C. The formula is in Column C. Change the cell references in the formula to suit your work book. Drag the formula down as far as you need.

In the test work book, change/mix the dates and inventory criteria to see the formula at work.

I hope that this helps,
Cheerio,
vcoolio.
Gerberreinette- Mar 7, 2016 12:43AM
Thank you so much Vcoolio I really appreciate this.
One question: The Rejected description has a date after the text. When I take the date out the formula works 100%.
Is there maybe something that can be added to the formula that will calculate text and number. If not I will take out the date. Thank you for assisting me in this because I really struggled with this.
Reply
Gerberreinette- Mar 7, 2016 01:28AM
Hi Vcoolio

Thank you so much for this. Declared and Rejected is mixed in with other text within the cell for example REJECTED160115

So I tried to format the dates as dates and NOT text 2016.03.04 - can be text or a date formatted to display as entered above


=IF(AND(A2 > DATEVALUE("2016/02/17"),OR(ISNUMBER(SEARCH("rejected",B5)),ISNUMBER(SEARCH("DECLARED",B5)))),"Accepted","Not Accepted")

It worked. Have a splendid day and thanks for your time in helping me.
Reply
vcoolio 832Posts Thursday July 24, 2014Registration date ModeratorStatus December 8, 2016 Last seen - Mar 7, 2016 01:38AM
=IF(AND(A2 > DATEVALUE("2016/02/17"),OR(ISNUMBER(SEARCH("rejected",B5)),ISNUMBER(SEARCH("DECLARED",B5)))),"Accepted","Not Accepted")


Well done Gerberreinette!

Glad that I was able to help out in some way.

Cheerio,
vcoolio.
Reply
Add comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!