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
mannyalley Posts 3 Registration date Tuesday September 3, 2013 Status Member Last seen September 6, 2013 - Sep 6, 2013 at 07:58 AM
Related:
- Date and text functions
- Accessor and mutator functions c++ - Guide
- Hard disk parts and functions - Guide
- Excel functions in french - Guide
- What are the key functions of the supportassist for home pcs? - Download - Cleaning and optimization
- Spreadsheet functions - Guide
3 responses
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!
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
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.
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
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")
Please help!
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")
Please help!
