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?
 
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
 
Michal ZiembaIT System ArchitectCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Michal ZiembaIT System ArchitectCommented:
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 System ArchitectCommented:
Try this
=IF(B4="","",IF(F4="",TODAY()-B4,"Completed"))
0
 
ssblueAuthor Commented:
That did it! Thanks!!
0
 
Michal ZiembaIT System ArchitectCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.