Differentiating between a Formula and Hard Entered (Static) Data - Excel

Occasional Visitor

I have the problem of trying to conditional format a cell based upon whether the contents of each cell are hard entered (static) data or if there is a formula in the cell. 

Example of this is when I am calculating dates and I want to actualise this information:

 

 1234
AItem IDLeaving DateDurationArrival Date
B Item A18-Oct-211401-Nov-21
C Item B01-Aug-212324-Aug-21

 

Cell contents:

 1234
AItem IDLeaving DateDurationArrival Date
B Item A18-Oct-2114=B2+B3
C Item B01-Aug-212324-Aug-21

 

I want cells B2, C2 and C4 to be highlighted by conditional formatting as they are hard entered, however, due to the fact B4 is a formula calculated by leaving date + expected duration then it shouldn't be highlighted until actualised.

 

Any further information required please advise, thanks.

 

Kind regards,

Bailey

 

1 Reply

@Bailey_Stiles_Epiroc 

I don't understand what you want (and your tables have switched the row numbers and column letters), but you could create rules of type 'Use a formula to determine which cells to format'. In the formula for the rule, you can use the ISFORMULA function.

ISFORMULA(B2) will return TRUE if B2 contains a formula and FALSE if it doesn't.