Update Formula to Check for Blank Value and Use Static Value in its Place

Bethany Garcia

I have a sheet that my company uses to track when a resident is being moved from their apartment into a temporary apartment and a permanent apartment while we renovate our apartment buildings. Each resident has one row, with columns to track their original unit, original unit move out date, temp unit, temp unit move in and move out date, perm unit, and perm unit move in and move out date. The purpose of the sheet is to flag any situation where we put more than one resident into the same unit at the same time.

To do this check I have a series of three formulas that look at the move in/out dates across all three of the unit types (original, temp and perm) to determine if the same unit is used for any resident for the same amount of time.

The formula I have works great when all three sets of units and dates are populated. What I have discovered though is that if a Perm Unit and Perm Unit Move In/Out date are not populated for a resident, if that resident is in a Temp Unit that overlaps with when we are putting another resident into that unit the formula isn't identifying it. The reason is that the formula is only comparing the specific date in the cell to look for the overlap, it doesn't account for the move out date of the Temp Unit being blank.

What I need is for each of my formulas to account for the Temp Move 1 Move Out Date to be blank, and if it is blank, use 1/1/5000 instead of the cell at the row to do the calculations to determine overlap.

Does anyone know of an easy way to add logic to these three sets of formulas to use 1/1/5000 instead of Temp Move 1 Move Out Date when the Temp Move 1 Move Out Date is blank? I think I need a statement to go through the logic with an "If Temp Move 1 Move Out Date is blank then" and have the entire formula with 1/1/5000 in place of Temp Move 1 Move Out Date, then if it is not true that Temp Move 1 Move Out Date is blank it uses the formula as it currently stands. But, I wanted to reach out to see if anyone had an easier way to do that before making that change. The series of the three formulas are below.

Original Unit Overlap Formula

=IF((COUNTIFS([Original Unit Move Out Date]:[Original Unit Move Out Date], >[Original Unit Move In Date]@row, [Original Unit Move In Date]:[Original Unit Move In Date], <[Original Unit Move Out Date]@row, [Original Unit]:[Original Unit], CONTAINS([Original Unit]@row, @cell)) + COUNTIFS([Temp Move 1 Move In Date]:[Temp Move 1 Move In Date], AND(@cell > [Original Unit Move In Date]@row, @cell < [Original Unit Move Out Date]@row), [Temp Move 1 Unit]:[Temp Move 1 Unit], CONTAINS([Original Unit]@row, @cell)) + COUNTIFS([Temp Move 1 Move Out Date]:[Temp Move 1 Move Out Date], AND(@cell > [Original Unit Move In Date]@row, @cell < [Original Unit Move Out Date]@row), [Temp Move 1 Unit]:[Temp Move 1 Unit], CONTAINS([Original Unit]@row, @cell)) + COUNTIFS([Temp Move 1 Move In Date]:[Temp Move 1 Move In Date], <[Original Unit Move In Date]@row, [Temp Move 1 Move Out Date]:[Temp Move 1 Move Out Date], >[Original Unit Move Out Date]@row, [Temp Move 1 Unit]:[Temp Move 1 Unit], CONTAINS([Original Unit]@row, @cell)) + COUNTIFS([Perm Unit Move In Date]:[Perm Unit Move In Date], AND(@cell > [Original Unit Move In Date]@row, @cell < [Original Unit Move Out Date]@row), [Perm Unit]:[Perm Unit], CONTAINS([Original Unit]@row, @cell)) + COUNTIFS([Perm Unit Move Out Date]:[Perm Unit Move Out Date], AND(@cell > [Original Unit Move In Date]@row, @cell < [Original Unit Move Out Date]@row), [Perm Unit]:[Perm Unit], CONTAINS([Original Unit]@row, @cell)) + COUNTIFS([Perm Unit Move In Date]:[Perm Unit Move In Date], <[Original Unit Move In Date]@row, [Perm Unit Move Out Date]:[Perm Unit Move Out Date], >[Original Unit Move Out Date]@row, [Perm Unit]:[Perm Unit], CONTAINS([Original Unit]@row, @cell))) > 1, 1, 0)

Temp Unit Overlap Formula

=IF((COUNTIFS([Temp Move 1 Move Out Date]:[Temp Move 1 Move Out Date], >[Temp Move 1 Move In Date]@row, [Temp Move 1 Move In Date]:[Temp Move 1 Move In Date], <[Temp Move 1 Move Out Date]@row, [Temp Move 1 Unit]:[Temp Move 1 Unit], CONTAINS([Temp Move 1 Unit]@row, @cell)) + COUNTIFS([Original Unit Move In Date]:[Original Unit Move In Date], AND(@cell > [Temp Move 1 Move In Date]@row, @cell < [Temp Move 1 Move Out Date]@row), [Original Unit]:[Original Unit], CONTAINS([Temp Move 1 Unit]@row, @cell)) + COUNTIFS([Original Unit Move Out Date]:[Original Unit Move Out Date], AND(@cell > [Temp Move 1 Move In Date]@row, @cell < [Temp Move 1 Move Out Date]@row), [Original Unit]:[Original Unit], CONTAINS([Temp Move 1 Unit]@row, @cell)) + COUNTIFS([Original Unit Move In Date]:[Original Unit Move In Date], <[Temp Move 1 Move In Date]@row, [Original Unit Move Out Date]:[Original Unit Move Out Date], >[Temp Move 1 Move Out Date]@row, [Original Unit]:[Original Unit], CONTAINS([Temp Move 1 Unit]@row, @cell)) + COUNTIFS([Perm Unit Move In Date]:[Perm Unit Move In Date], AND(@cell > [Temp Move 1 Move In Date]@row, @cell < [Temp Move 1 Move Out Date]@row), [Perm Unit]:[Perm Unit], CONTAINS([Temp Move 1 Unit]@row, @cell)) + COUNTIFS([Perm Unit Move Out Date]:[Perm Unit Move Out Date], AND(@cell > [Temp Move 1 Move In Date]@row, @cell < [Temp Move 1 Move Out Date]@row), [Perm Unit]:[Perm Unit], CONTAINS([Temp Move 1 Unit]@row, @cell)) + COUNTIFS([Perm Unit Move In Date]:[Perm Unit Move In Date], <[Temp Move 1 Move In Date]@row, [Perm Unit Move Out Date]:[Perm Unit Move Out Date], >[Temp Move 1 Move Out Date]@row, [Perm Unit]:[Perm Unit], CONTAINS([Temp Move 1 Unit]@row, @cell))) > 1, 1)

Perm Unit Overlap Formula

=IF((COUNTIFS([Perm Unit Move Out Date]:[Perm Unit Move Out Date], >[Perm Unit Move In Date]@row, [Perm Unit Move In Date]:[Perm Unit Move In Date], <[Perm Unit Move Out Date]@row, [Perm Unit]:[Perm Unit], CONTAINS([Perm Unit]@row, @cell)) + COUNTIFS([Temp Move 1 Move In Date]:[Temp Move 1 Move In Date], AND(@cell > [Perm Unit Move In Date]@row, @cell < [Perm Unit Move Out Date]@row), [Temp Move 1 Unit]:[Temp Move 1 Unit], CONTAINS([Perm Unit]@row, @cell)) + COUNTIFS([Temp Move 1 Move Out Date]:[Temp Move 1 Move Out Date], AND(@cell > [Perm Unit Move In Date]@row, @cell < [Perm Unit Move Out Date]@row), [Temp Move 1 Unit]:[Temp Move 1 Unit], CONTAINS([Perm Unit]@row, @cell)) + COUNTIFS([Temp Move 1 Move In Date]:[Temp Move 1 Move In Date], <[Perm Unit Move In Date]@row, [Temp Move 1 Move Out Date]:[Temp Move 1 Move Out Date], >[Perm Unit Move Out Date]@row, [Temp Move 1 Unit]:[Temp Move 1 Unit], CONTAINS([Perm Unit]@row, @cell)) + COUNTIFS([Original Unit Move In Date]:[Original Unit Move In Date], AND(@cell > [Perm Unit Move In Date]@row, @cell < [Perm Unit Move Out Date]@row), [Original Unit]:[Original Unit], CONTAINS([Perm Unit]@row, @cell)) + COUNTIFS([Original Unit Move Out Date]:[Original Unit Move Out Date], AND(@cell > [Perm Unit Move In Date]@row, @cell < [Perm Unit Move Out Date]@row), [Original Unit]:[Original Unit], CONTAINS([Perm Unit]@row, @cell)) + COUNTIFS([Original Unit Move In Date]:[Original Unit Move In Date], <[Perm Unit Move In Date]@row, [Original Unit Move Out Date]:[Original Unit Move Out Date], >[Perm Unit Move Out Date]@row, [Original Unit]:[Original Unit], CONTAINS([Perm Unit]@row, @cell))) > 1, 1)

Paul Newcome

I will use a quick example to show how you can use an IF to output anywhere that it is needed.

=IF((COUNTIFS([Perm Unit Move Out Date]:[Perm Unit Move Out Date], >[Perm Unit Move In Date]@row, [Perm Unit Move In Date]:[Perm Unit Move In Date],.................................

=IF((COUNTIFS([Perm Unit Move Out Date]:[Perm Unit Move Out Date], @cell > IF([Perm Unit Move In Date]@row <> "", [Perm Unit Move In Date]@row, DATE(5000, 1, 1)), [Perm Unit Move In Date]:[Perm Unit Move In Date],.................................

Basically swap out the single cell reference with the IF function:

IF([Perm Unit Move In Date]@row <> "", [Perm Unit Move In Date]@row, DATE(5000, 1, 1))

Bethany Garcia

@Paul Newcome thank you! This all makes sense to me, but the single cell reference I will need to replace is the Temp Move 1 Move Out Date, since that's the one that would be blank and need to be utilized in the logic. I've tried adjusting the If logic you provided but I'm getting errors. I think I just need your insight into how to structure that. Do I need to include the @cell reference before the greater than/less than sign of the cell reference in every situation? Is that @cell in front of the greater than/less than indicating the formula should use the value in the cell if it is not blank, and if it is blank to use 1/1/5000 instead?

Paul Newcome

I do recommend using the @cell reference. The structure of the IF statement is in my last post. Wherever you see a single cell reference, you will want to replace it with the IF.

The only thing you should have to adjust in the IF is the actual column name to reflect whichever column your cell reference is in. The structure/syntax/logic/etc. should all remain the same including the parenthesis.