Excel update column E based on column F

I would like to have   'Completed'   replace the number (formula) in column E   IF  there is a date in column F.

Please see attachment.
HoneyDoList.xlsx
ssblueAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian BEE Topic Advisor, Independant Technology ProfessionalCommented:
There is no function "isdate", so you need to see if column F contains something that matches the properties of a date. Since a date in Excel is really a number, I used this:

=IF(ISNUMBER(F2),"Completed",IF(B2="","",TODAY()-B2))

So addmitedly you could confuse the formula if you put any sort of a number in col F, even if it wasn't a date. However this should do the trick.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Michal ZiembaIT AdministratorCommented:
in the cell E4 paste this:
=IF(B4="";"";IF(F4="";TODAY()-B4;"Completed"))
than copy this to the rest cells in the F column
0
ssblueAuthor Commented:
Brian - yours puts 'Completed' there when the past due gets to '0'

Michal - I get an error when using your formula.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Michal ZiembaIT AdministratorCommented:
Try to replace ; with ,
0
Brian BEE Topic Advisor, Independant Technology ProfessionalCommented:
Michael's formula should use commas, not semi colons.

Your request was for the column to show "completed" when a date is entered in col F. Should there be more to it?

Actually, when I read that literally, you said you want the word "Completed" to REPLACE the formula in col E. That would require a macro.
0
ssblueAuthor Commented:
I need to use the formula to in E to calculate the past due  but  once there is a completed date in F  I need E to change to 'Completed'
0
Michal ZiembaIT AdministratorCommented:
Try this
=IF(B4="","",IF(F4="",TODAY()-B4,"Completed"))
0
ssblueAuthor Commented:
That did it! Thanks!!
0
Michal ZiembaIT AdministratorCommented:
Any time :-)
0
Brian BEE Topic Advisor, Independant Technology ProfessionalCommented:
I put in both formulae and Michal's formula and mine both return the same results in the example sheet you provided.

Note: His will also say completed if someone types in something in col F besides a properly formatted date.

I'm a Topic Advisor for Excel, so if you want me to reopen the question so you can close differently, let me know otherwise I'll ask the mods to do it.
0
ssblueAuthor Commented:
I needed the "Completed" to show up in column E once a date had been placed in column F. Brian's formula was not using the date in column F to determine when to place "Completed" in column E.
0
Brian BEE Topic Advisor, Independant Technology ProfessionalCommented:
As I explained, excel really can't tell if a cell contains a date. So the next best choice is to see if it is a number. If you don't check that, you could enter any old thing in the column and possibly cause an error, or at least an erroneous result.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.