• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel Formula

J

Jagdev Singh

Active Member
Hi Experts
Could you please help me with the formula which will help give me output as Ok or Old Update or Blank. The value it needs to check is from column A where there is a date. I need to apply the formula is column B which will check the days which are 10 days old from today i.e. 18th March, 2015. The Cell A2 is 8th March, 2015 then the result is “Ok” and if it is more than 10 days it should display “Old Update” and if it encounter blank cell then it should display “Blank”.
Please refer the sample file for your reference.
Regards,
JD
 

Attachments

Hi JD,
A2 will contain text:
08/03/2015: On time stats

Or there will be a valid date i.e. 08/03/2015?
and 08/03/2015 is dd-mm-yyyy or mm-dd-yyyy ?

Regards,
 
Hi Khalid
The way data is there in the A2 it is correct the date is added with some text in the cells. The date format is dd-mm-yyyy.
Regards,
JD
 
Hi JD,
Can you go through these 2:

For the text: 08/03/2015: On time stats
=IFERROR(IF(DATE(MID(A2,7,4),MID(A2,4,2),LEFT(A2,2))>DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()+10)),"OK","Old Update"),"Blank")

For valid date:
=IF(A2="","Blank",IF(DATE(YEAR(A2),MONTH(A2),DAY(A2))>DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()+10)),"OK","Old Update"))

I am sure there will be more ways, I just thought about these, can you confirm?

Regards,
 
Hi JD,

Try below formula in B2:

=IF(A2="","",IF(DATEVALUE(TRIM(LEFT(A2,LOOKUP(99^99,--MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),ROW(INDIRECT("1:"&LEN(A2)))))))>=(TODAY()-10),"OK","Old Update"))

Note your data must start with a date and should not contain any number in text.

Regards,
 
Hi Both
Thanks for the formulas
Your first one is working but throwing incorrect result. The value in B2 should be OK, but your formula is saying Old Updates. I changed the value “Ok” and “Old Update” and tried it, it showed the correct value in B2, but when I drag it to B3 instead of Showing “Old Update” it is showing “Blank”. The second one didn’t work.
The First one was very close to the result.
@Somendra – Thanks for the Formula it worked fine. Could you please break the formula and let me understand the logic behind it.
Regards,
JD
 
Jagdev Singh said:
Hi Both
Thanks for the formulas
Your first one is working but throwing incorrect result. The value in B2 should be OK, but your formula is saying Old Updates. I changed the value “Ok” and “Old Update” and tried it, it showed the correct value in B2, but when I drag it to B3 instead of Showing “Old Update” it is showing “Blank”. The second one didn’t work.
The First one was very close to the result.
@Somendra – Thanks for the Formula it worked fine. Could you please break the formula and let me understand the logic behind it.
Regards,
JD
Click to expand...

Hi JD,
Sorry I put the wrong condition results, and didn't checked with A3...
Can you check this modified version:

=IFERROR(IF(DATE(TRIM(SUBSTITUTE(MID(A2,FIND("/",A2)+4,4),"-","")),MID(A2,FIND("/",A2)+1,2),TRIM(LEFT(A2,FIND("/",A2)-1)))>=DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()-10)),"OK","Old Update"),"Blank")

Regards,
 
Little shorter than above:
=IFERROR(IF(DATE(TRIM(SUBSTITUTE(MID(A2,FIND("/",A2)+4,4),"-","")),MID(A2,FIND("/",A2)+1,2),TRIM(LEFT(A2,FIND("/",A2)-1)))>=TODAY()-10,"OK","Old Update"),"Blank")
 
Hi JD,
Thanks for the feedback...

Let me try.
1st we break this formula with colors to identify:

=IFERROR(IF(DATE(TRIM(SUBSTITUTE(MID(A2,FIND("/",A2)+4,4),"-","")),MID(A2,FIND("/",A2)+1,2),TRIM(LEFT(A2,FIND("/",A2)-1)))>=TODAY()-10,"OK","Old Update"),"Blank")

All color highlighted formulas are wrapped in DATE formula (YEAR,MONTH,DAY)

suppose we have string in A2 "16/03/2015: On time stats"

YEAR:
TRIM(SUBSTITUTE(MID(A2,FIND("/",A2)+4,4),"-",""))
This is extracting from A2; result = 2015

MONTH:
MID(A2,FIND("/",A2)+1,2)
This is extracting from A2; result = 03

DAY:
TRIM(LEFT(A2,FIND("/",A2)-1))
This is extracting from A2; result = 16

Combining all these with date formula will give you 16/03/2015

Once we get the date, we can check this date is less than 10 days from today or not with =TODAY()-10

We can now make an IF condition, so if the value is TRUE, then "OK" else "Old Update"

Formula is now complete, just one thing is remaining; if the cell A2 is blank then it should show "Blank". I've wrapped this formula with IFERROR, so when A2 is blank it will give us error.

Hope It helped.

Regards,
 
Jagdev Singh said:
Hi Both
Thanks for the formulas
Your first one is working but throwing incorrect result. The value in B2 should be OK, but your formula is saying Old Updates. I changed the value “Ok” and “Old Update” and tried it, it showed the correct value in B2, but when I drag it to B3 instead of Showing “Old Update” it is showing “Blank”. The second one didn’t work.
The First one was very close to the result.
@Somendra – Thanks for the Formula it worked fine. Could you please break the formula and let me understand the logic behind it.
Regards,
JD
Click to expand...


@Jagdev Singh

Will do it today evening.

Regards,
 
You must log in or register to reply here.
Back
Top