Paul has attended:
Excel Advanced course
Conditional and If statements using dates as a trigger for an ac
Hi,
I have created a schedule which has dates of the month (...like a calendar) going accross the worksheet, a driver date which is essentially the date of cinema release of a title in column B. Column a has the name of the titles.
What I would like to achieve, is based on the release dates in column B, I would like to have a deadline date which falls exactly 6 weeks after the release date in column B.
Perhaps using a combination of conditional and if functions, as the deadline day approaches to give a warning by colouring the calendar cells different colours. Say for example 1 month away the calendar cells are higlighted a certain colour, changing colour to a different one once two weeks away, and then in the last week to once again a different colour. Once the dealine day is reached and the activity has not been completed colouring the cell red. However at any point once the activity is completed - typing an 'x' would change the colour of the cell to green.
I hope this makes sense, a sample formula and explantion of the parameters to select in conditional formatting which does this would be very much appreciated.
If easier, I already have the spreadsheet but can't seem to attach it here using this post.
Many thanks,
Paul
RE: Conditional and If statements using dates as a trigger for a
Hi Paul
Thanks for getting in touch. This requires use of the Formula-driven conditional formatting. You create these by going to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.
Your formula will then take the following template:
=$A1>TODAY()+7
Which says, starting with the value in A1, see whether the contents are greater than today's date plus seven days ("is the value more than a week away from today?"). Your other conditions and values are permutations of that.
One thing you should be aware of is that the criteria must be laid out in a specific order. e.g. Check for dates greater than 14 before you check for dates greater than 7, otherwise the logic will slip through the cracks.
I've attached a sample workbook which has some examples in for you to examine. I hope it helps.
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Attached files...
RE: Conditional and If statements using dates as a trigger for a
Hello Paul,
It's Rodney here.
I see one of our other trainers has answered your post. If you need more then you may send your file to me at:
rl@stl-training.co.uk
Let me know if you are happy with Gary's solution.
If you require further assistance, please reply to this post. Or perhaps you have another Microsoft Office question?
Have a great day.
Regards,
Rodney
Microsoft Office Specialist Trainer