# Date and text functions

Closed
mannyalley Posts 3 Registration date Tuesday September 3, 2013 Status Member Last seen September 6, 2013 - Sep 3, 2013 at 06:18 PM
mannyalley Posts 3 Registration date Tuesday September 3, 2013 Status Member Last seen September 6, 2013 - Sep 6, 2013 at 07:58 AM
Hi,

I am trying to find a function which will return the following:

If the cell date falls in the previous month of the today date cell and matches another cell with value complete then return yes. For example:

A1= 28/07/2013
B1= Today
c1= Complete
D1 - Only return yes if A1 falls in July

Also having problems with putting together if the same thing applied but it was within the last 12 months of the today date. Example:

A1= 28/07/2013
B1= Today
c1= Complete
D1 - Only return yes if A1 falls within last 12 month of today date

## 3 responses

Blocked Profile
Sep 4, 2013 at 09:07 AM
Good Morning,

I have this I use for my warranty, I have altered it for your situation:
=IF(AND(MONTH(B1)-MONTH(A1)=1,C1="Complete"),"Yes",FALSE)

We are using the formulas of:

=If(logic check cell, true, false)

and then nested within the logic check we have the formula of:

=And(logic checka, logic checkb) ...will return true if the two are true, so we have:

=AND(month(b1)-Month(a1)=1,C1="Complete") , and it is nested into the IF statement.

The Above logic checks to see if the month difference is equal to 1, if it is, it is last month.

So with that being said and understood, use the same logic statement for your 12 month warrranty period.

I hope this gives you some direction.

Have Fun!
mannyalley Posts 3 Registration date Tuesday September 3, 2013 Status Member Last seen September 6, 2013
Sep 5, 2013 at 04:22 PM
Hi Mark

that's great! Thank you, this has worked for the last month but I am having problem with the last 12 months, working out from the previous month.

I have a list of start dates and I have worked out in a cell the previous month from the today date and now am trying to return yes for all the dates that fall in the last 12 month of that last month date. For example:

A1 = start date
b1 = previous month (today date)
c1 = Yes

and then I can use the same formula to check if end date also falls in the last 12 months, and need to work

if end date is blank then count all yes cells from start date if not the all yes cells from end date.
mannyalley Posts 3 Registration date Tuesday September 3, 2013 Status Member Last seen September 6, 2013
Sep 6, 2013 at 07:58 AM
Hi

I am having problems with if formula between two dates. i have worked out the last month from today date as well as the last 12 months from today date but when i add a formula it is not capturing the full start of the previous 12 months.

ch2 = Today eg. 06/09/2013
ch3 =TEXT(TODAY()-DAY(TODAY()),"MMMM YYYY") eg. August 2013
ch4 = DATE(YEAR(CH2),MONTH(CH2)-CI2,MIN(DAY(CH2),DAY(DATE(YEAR(CH2),MONTH(CH2)-CI2-1,0)))) eg. September 2012

q:q = start date eample 01/09/2012
r:r = end date example 31/08/2013
u:u = Complete
in column s i have =IF(AND(Q11>=CH4,Q11<=CH3),"Yes","No")

this formula is returning No but when i change the start date to 06/09/2012 it returns yes.

if i add formula to check if column u ="complete" and check if there is an end date entered if so see if the end date falls within the two dates ch3 & ch4, if there is no end date then use the start date and check if this falls within ch3 & ch4. this is bring back error example below:
=IF(AND(Q11>=CH4,Q11<=CH3,R11>="0",U11>="0"),"Yes","No")