Right so my spreadsheet for planning and reporting on one of our work programmes is taking shape nicely (thanks to the help of you guys) but I have run into another problem.

At the moment I have a column (M) where the formula Returns the word “Overdue” if the date a job is “Closed” (column I) is after the date that the job is due to be done (column H)

here is the formula im currently using:

=IF(SUM(H152-I152)<0,"Overdue","")

It find the number of days difference between when the job is due to be done and when it was done, if it’s a negative figure then the job went overdue.

However working this way will only show me if the job went overdue if the job is closed. If a job was due to be completed by say 15th July but it is now the 17th July and the job is still not closed it won’t show as overdue.

One more thing. If we went to look at a job and couldn’t do it at that time then I put the word “inspected” in the “Closed” column (I)

Is there a way of adding to my formula so it looks at the “Closed” column (I) and if the word “inspected” appears or the cell is blank then it will use today’s date (not sure if you can add that sort of command) to compare with the “Due by” column (H)?

I have a few other questions about how to do other things with my spreadsheet, like how to average the difference between the numbers in 2 different columns but only if another column has certain data in it but im not sure if I should ask them in a separate thread or just post them here?

Thanks for all your help peeps. Your all making me look very clever at work!!!