Comparing dates

Hi


I am new to the forum and a relative novice to excel. I am looking for some advice, I have a spread sheet (action tracker) I want to show at a glance what has been completed, what is on track, what is a month behind schedule and what is a month over schedule. But am struggling. Any ideas or prompts?

Each action will have a due date and I have put the =today in there for comparision reasons ideally I would to show
anything which is on schedule a cell turns green and has "on track" in the text
it turns amber if it is a less than a month overdue and has "behind "schedule" in the text
it turns red if is more than a month overdue and has "incomplete" in the the text
and it turns blue when complete

Any tips or help would be greatly appreciated.


Regards


Noyman

Hi Noyman,

welcome to the Forum. You can do this with conditional formatting, but in Excel 2003 you can only have 3 conditions per cell. Can you post an example workbook (remove/change any confidential information) - the FAQ describes how to attach a file to your posts.

Hope this helps.

Pete

Hi Pete


Thanks for the reply. I have attached the file - as I say I am a novice - I don't think it is a 2003 version think it is more up to date.


Regards and thanks


Noyman

Okay, so you should change the details in your profile to read XL2007 (or XL2010), so that in future you will get solutions that are tailored to your version.

Can you tell me which dates you want to compare with TODAY()? You have the Date Raised in column D and a Due Date in column L.

Presumably you want a formula in column M (RAG Status) to return the text.

How will you indicate that the action has been completed - is it when column N has been filled with a date?

Pete

Pete - thanks I will change the profile to avoid confusion.

Basically if I see a problem on site I want to log the day I saw it (date raised in col d) and i want to set a due date (col L) for them to close the action. When they have closed it I would like them to enter the date in column N (date closed). But I want to be able to track this easily so that the bosses can look down the RAG status column and see what is complete, on track, a bit behind schedule (less than a month late) or significantly overdue (ie over a month), hence I was using =today in there. So I want to compare todays date with the due date and then make that change the RAG status. Sorry if that is confusing, as you can see a bit of a novice

Regards and thanks


Noy

Noy,

I've set this up in the attached file for you. I've put this formula in M3:

Formula:

Please Login or Register  to view this content.

which is then copied down. I've used these words as the basis of the conditional formatting, which I have applied to column L as well as column M. Some test dates show it working.

Hope this helps.

Pete

Pete


That is spot on I spent 2 hours going nowhere on Friday with this. Thank you for kind help and support.


Regards

Noy

You're welcome, Noy, thanks for feeding back.

As a new user, you might need to be advised that you can set the Thread as Solved if you consider it to be so - just click on Thread Tools above your first post, then Mark as Solved.

Also, you can pass on thanks to any contributor who has helped you (not just on this thread) by clicking on the "star" icon in the bottom left corner of any post that you have found to be helpful.

It has stopped snowing here in Warrington - whereabouts are you in Cheshire?

Pete