IF/AND/OR Statement?

[Hortsy]

Hi,

I've got a cell with a due date in it = Cell C3 and a status column either set to Open or Closed = Cell C10.

I need to check if C10 is Open, and the due date is in the past then cell C5 displays 'Late' and highlighted red or if C10 is Open and due date is within 10 working days of today the due date cell C5 displays 'Due' and is highlighted amber and if the due date is over 10 working days away 'Not due' and coloured green.

If C10 is closed, then C5 displays Closed.

Please help, I don't know if all the above is possible!

 

[Fluff]

Hi & welcome to MrExcel.
How about

=IF(C10="Closed",C10,IF(C3<TODAY(),"Late",IF(WORKDAY(TODAY(),10)>=C3,"Due","Not Due")))

 

[Hortsy]

Hi & welcome to MrExcel.
How about

=IF(C10="Closed",C10,IF(C3<TODAY(),"Late",IF(WORKDAY(TODAY(),10)>=C3,"Due","Not Due")))

Hi,

Thanks, but it doesn't seem to take into account if Cell C10 is Open?

 

[Fluff]

Can C10 be anything other than closed or open?

 

[Hortsy]

Can C10 be anything other than closed or open?

No, but please ignore, it was me being a numpty!! It works perfectly, thanks very much for your help ?

 

[Fluff]

You're welcome & thanks for the feedback.