Assistance constructing a particular IF Statement in Excel

Question

Okay, so I have cells that look like such:

A1: 8/19 Completed 
A2: Completed 4/17

All cells either look like those two or they are blank.

So I want to put together an IF Statement that does the following: If a cell is blank, I want it to say "Not Completed" but if it isn't blank, I want to return the date within the cell.

So A1 would simply say "8/19" and A2 "4/17" but if its blank, it just says "Not Completed"

I obviously know it includes an IF(ISBLANK()) statement, but I'm having trouble figuring out the rest!

I've basically picked any of the hundreds of possible duplicates for this; your question is basically just "How do I use an IF statement". — Grade 'Eh' Bacon
– Grade 'Eh' Bacon, Commented Mar 2, 2016 at 20:55
Not really. He's also trying to get the date out even though it's entered in different ways. — asp8811
– asp8811, Commented Mar 2, 2016 at 21:10

asp8811 · Accepted Answer · 2016-03-03 14:10:52Z

1

So, to test if your cell is blank, use:

=IF(A1="","Not Completed","Completed")

Easy enough, but you want the date instead of "Completed". So, our next step is to find where the date is within the cell.

If you have "Completed" and the date entered in a different order randomly, you will need to determine where to find "Completed" in the string the cell contains to determine the order. You can do that using:

=FIND("Completed",A1)

This will give you the starting position of the string "Completed" within "8/21 Completed" or "Completed 8/21". If this function returns 1, then the format is "Completed 8/21." Anything else and the format is "8/21 Completed".

Now, we just need chop up the string. If =FIND("Completed",A1) is equal to 1 then we want to use =RIGHT(A1,LEN(A1))-10. This will trim the string, starting from the right side, to whatever the length is minus ten characters (the length of "Completed "). If it is anything other than 1, we want to use =LEFT(A1,LEN(A1)-10). This will do the same thing but start from the left.

So, our full formula boils down to

=IF(A1="","Not Completed",IF(FIND("Completed",A1)=1,RIGHT(A1,LEN(A1))-10,LEFT(A1,LEN(A1)-10))

edited Mar 3, 2016 at 14:10

answered Mar 2, 2016 at 21:26

asp8811

8038 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Henry W Over a year ago

I think you've a mistake in the '=RIGHT(A1,LEN)-10' part. Needs to be '=RIGHT(A1,LEN(A1)-10

Collectives™ on Stack Overflow

Assistance constructing a particular IF Statement in Excel

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related