I have a column where the user inputs a date for when they wish an employee to end on a project. I am checking to see whether it is within the project dates or if the employee is due to leave the company and they're not going to be available for the inserted date. If either then I'd like to both conditionally format the cell and apply data validation with an error message. I want the data validation incase the project date or emplyment date is changed so when the user comes back to the sheet with the employee project data on, it still shows there's a visible error, rather than at the time the user enters the end date for the project.
The conditional formatting is working and highlighting the cell if the entered end date is after the project end date or after the employee end date.I use the followng for that:
=OR($I2>ProjectEndDate,IF(NOT(ISBLANK(StaffEndDate)),$I2>StaffEndDate,FALSE))
For the Data Validation I use :
=AND($I2<=ProjectEndDate,IF(NOT(ISBLANK(StaffEndDate)),$I2<=StaffEndDate,TRUE))
Which unfortunately doesn't work. So if I have a project end date of 31/03/2023 and enter 01/04/2023 it highlights the cell red but doesn't bring up the data validation error message. If I remove the IF condition and have :
=AND($I2<=ProjectEndDate, TRUE)
The data validation will work fine and pop up the error message, stopping the user entering an incorrect value. In the example, ISBLANK(StaffEndDate) evaluates to TRUE giving
IF(NOT(TRUE),$I2<=StaffEndDate,TRUE))
Which in turn evaluates to
IF(FALSE,$I2<=StaffEndDate,TRUE))
Which then evaluates to TRUE, effectively giving the same result as the working validation:
=AND($I2<=ProjectEndDate, TRUE)
Any ideas why =AND($I2<=ProjectEndDate,IF(NOT(ISBLANK(StaffEndDate)),$I2<=StaffEndDate,TRUE)) doesn't work and how I can fix it?