# Best way to handle IF/THEN formula



## rck (Jan 3, 2023)

Good morning,
I am using an IF/THEN statement, but I didn't think it all the way through before I started using it.

=IF(AND(A2>=TODAY(),B2=$O$2), $P$2, IF(AND(A2>=TODAY(),B2=$O$3), $P$3, IF(AND(A2>=TODAY(),B2=$O$4), $P$4,  "N/A")))


DateShiftTotal
StaffMinimum
StaffingOpen
PositionsScheduledBC (+1)Average
UnscheduledProjected
ResidualProjected Above/Below
Minimum StaffingActual
UnscheduledActual
ResidualActual Above/Below
Minimum StaffingShiftOpen
Positions01/01/2023A5646N/A603471504A001/02/2023B5646N/A603471504B101/03/2023C5646260345-1482C2


The problem is that the formula is doing exactly what it is being told to do. However, I would also like for the cell to contain historical values. If the value in $O$2 was "0" on 1/1/2023, then keep the one. If the value in $O$2 changes to 3 on 1/4/2023, store "3" for that date, but keep the "0" on 1/1 (don't change 1/1 to "3").

Is there a better way to handle this by only making changes to $O$2:$O$4?

Thank you in advance!


----------



## hajiali (Jan 4, 2023)

I’m a bit confused. Seems like your comparing column B and O which contain letters. In your explanation you said if Column O is “0”.


----------



## Fluff (Jan 4, 2023)

rck said:


> I would also like for the cell to contain historical values.


That is not possible. You would need to have an expanding list that included the date & then refer to that.


----------

