Function Formula Date and Text
Solved/Closed
Gerberreinette
Posts
14
Registration date
Friday March 4, 2016
Status
Member
Last seen
July 31, 2023
-
Updated on May 11, 2017 at 05:55 AM
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 - Mar 7, 2016 at 01:38 AM
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 - Mar 7, 2016 at 01:38 AM
Related:
- Function Formula Date and Text
- Date formula in excel dd/mm/yyyy - Guide
- Logitech formula vibration feedback wheel driver - Download - Drivers
- Number to words in excel formula - Guide
- Agp function - Guide
- Excel grade formula - Guide
1 response
vcoolio
Posts
1404
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 15, 2023
259
Mar 4, 2016 at 09:07 AM
Mar 4, 2016 at 09:07 AM
Hello Gerberreinette,
The followiing IF/AND formula should do the task for you:-
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.
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.
Mar 7, 2016 at 12:43 AM
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.
Mar 7, 2016 at 01:28 AM
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.
Mar 7, 2016 at 01:38 AM
=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.