Conditional formula and dates

[Kingmaker]

Thanks for taking the time to read and think.

I'm building a financial model and have a simple date row at the top of every tab. The formatting is Date, US, Mar-12 (example).

I have a few tabs that deal with Headcount and Roll Out and in these tabs I'm using a conditional formula to return a simple 1.

It goes like this - =IF(C$2>=$B$3,1,0)

C$2 is the date field and $B$3 is a cell that contains the relevant date the individual becomes 'live' in the model.

The formula works fine at first glance, but if I delete the date in $B$3 the formula still returns a 1. So with no date in there, the conditional formatting returns the wrong answer.

This is just a big gap in my knowledge but hopefully not yours.

Thoughts?

 

[jammoca]

Maybe something like this ...

=IF($B$3="","Add a Date",IF(C$2>=$B$3,1,0))

Kind regards,

Chris

 

[Kingmaker]

Works where the date is correct but returns "add a date" if not, whereas I just need a 0 in the cell if it fails the test.

Also when I go backwards it doesn't revert to the correct answer, just keeps "add a date" in there.

Maybe something like this ...

=IF($B$3="","Add a Date",IF(C$2>=$B$3,1,0))

Kind regards,

Chris

 

[jammoca]

Try this ...

=IF(OR($B$3="",C$2<$B$3),0,1)

Kind regards,

Chris

 

[Kingmaker]

Kudos - you're a boss

Thank you so much #win

Try this ...

=IF(OR($B$3="",C$2<$B$3),0,1)

Kind regards,

Chris

 

[jammoca]

no problem King,

Kind regards,

Chris

 

[Kingmaker]

Kingmaker - never King