If error formula across 3 cells

hdierkers

Hello,

I am trying to create a column formula to capture the year of an end date, but only wanting one to pull.

Essentially, we have 3 end dates

1. The original planned
2. The new planned
3. The actual

In my tracker, one or all may be filled in, but I only want to capture the actual when that is available (essentially, I want a formula that says: If the actual end date is blank, use the year of the new planned. If the new planned is blank, use the original planned).

I've tried an IF formula and and IFERROR and can't seem to get it right. I wanted to use an IFERROR in case all 3 are not known.

Carson Penticuff

You will of course have to modify this somewhat based on your setup. The IFERROR() will not be needed with this approach.

=IF(ISDATE([Actual End Date]@row), YEAR([Actual End Date]@row), IF(ISDATE([New Planned End Date]@row), YEAR([New Planned End Date]@row), IF(ISDATE([Original Planned End Date]@row), YEAR([Original Planned End Date]@row), "")))

Also of note: Make sure the column you are placing the formula into is NOT formatted as DATE since you are only pulling in the YEAR().

hdierkers

@Carson Penticuff - Here's an issue I think with the formula. I want it to pull the 2nd columns year because that essentially replaces the original/Contract submission date, but it will ultimately be the actual once that is filled in at the end of the project.

Carson Penticuff

Here is an updated version using your column names:

=IF(ISDATE([Actual Submission Date]@row), YEAR([Actual Submission Date]@row), IF(ISDATE([Client-Target Submission Date]@row), YEAR([Client-Target Submission Date]@row), IF(ISDATE([Contract Submission Date]@row), YEAR([Contract Submission Date]@row), "")))

I believe this will do what you are asking.

If [Actual Submission Date] is present, it will use that year. If not, it will check for [Client-Target Submission Date]. If that is not present, it will use the year from [Contract Submission Date]. If none of the three cells contain dates, it will return a blank cell.

hdierkers

That is what I did before, but it is not over-riding with the new target - see screenshot above. Basically, since the client-target is later than the contract date, I want to capture that year because it is the new one we want to track. Ultimately, the actual will over-ride them all, but we won't know that until the project is complete.

Carson Penticuff

With the formula I posted, the second column should have priority. I do see, however, that the second column is populated from another formula. Is it possible the second column is formatted as a date? If so, the formula above would ignore it and produce the result you are seeing.

If you plug this formula in, it will let you know if it formatted as a date.

=IF(ISDATE([Client-Target Submission Date]@row), "Is a date", "Is not a date")

hdierkers

When I use your formula, it says that cell is not a date (although the column itself is formatted as a date - it's a cell reference to another sheet, the source information is a date, however). The other 2 columns are formatted as dates.

Carson Penticuff

I would look closely at the source sheet. Would there be any need to enter non-date information into the source column? If not, you may want to set the "restrict to dates" option in the column properties. Any "non-date" dates already there would then need to be retyped to format them as dates.

hdierkers

That fixed it! The source sheet was using a formula to cell link, but that column was text. When I changed it to a date column (still a formula with a cell link), it fixed the formula in this particular column.

Thanks!

Carson Penticuff

I'm glad it worked. Dates formatted as text are an easy way to get tripped up.