CODEWITHSUNDEEP
excelexcel-formulaoffice365extract
Nick
Nick

Reputation: 775

Extracting Date From Cells using excel formulas

Im exploring how feasible it is to extract data from cells that consist of text. ultimately. I will apply conditional formatting rules based on the date relative to today, but right now I just need to be able to extract them.

So far I have achieved the following:

enter image description here

To achieve this I've used the formula found online =IFERROR(DATEVALUE(LEFT(RIGHT(B2,(LEN(B2)-(FIND("-",B2)-3))),11)),""). The issue is that if a cell contains only a date, the Length is subtracted from itself and nothing is returned. I have tried modifying the above equation but keep running into errors.

Hoping someone may be able to help me out modify this formula. Alternatively, if anyone knows how dates can be extracted used just excel formulas that would be great.

Upvotes: 0

Views: 724

Answers (2)

Nick
Nick

Reputation: 775

This is a solution I came up with with appears to work for almost Any Date format provided the date is at the top of the cell. Note this was done for dates in cell H6 of my workbook.

The formula below can be entered below to extract the date from this cell amongst text.

=TEXT(IFERROR(LEFT(H6,SEARCH(CHAR(10),H6,1)-1),H6),"dd-mmm-yyyy")

This formula checks if the date is within 3 years and returns a true of false value. This can be used to conditionally Format Cells if the date once extracted is out of date.

=IF(ISBLANK(H6),FALSE,IFERROR(IF(DATEVALUE(TEXT(IFERROR(LEFT(H6,SEARCH(CHAR(10), H6, 1)-1),H6),"dd-mmm-yyyy"))<=TODAY()-(365*3),TRUE,FALSE),TRUE))

Works really well for me!

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152465

The issue is that without the other text it is a true date and a true date is a double and has no - in it.

The simple fix is to wrap change the"" return from the IFERROR to B2:

=IFERROR(DATEVALUE(LEFT(RIGHT(B2,(LEN(B2)-(FIND("-",B2)-3))),11)),B2)

If that is not sufficient you can do an IF to test if date:

=IF(ISNUMBER(B2),B2,IFERROR(DATEVALUE(LEFT(RIGHT(B2,(LEN(B2)-(FIND("-",B2)-3))),11)),""))

Upvotes: 1

Related Questions