conditional and if statements

Public Schedule Face-to-Face & Virtual Instructor-Led Training - View dates & book

• Home
• Courses
• Promotions
• Schedule
• Formats
• Our Clients

Forum home » Delegate support and help forum » » Conditional and If statements using dates as a trigger for an ac

# Conditional and If statements using dates as a trigger for an ac

Resolved · Medium Priority · Version 2010

Paul has attended:

### 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

Edited on Fri 21 Jun 2013, 12:24

### 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

### 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

Fri 28 Jun 2013: Automatically marked as resolved.

Training courses

Training information:

 Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions What does 'Resolved' mean? Any suggestions, questions or comments? Please post in the Improve the forum thread.

### Deleting a range of cells using the autofill handle

Firstly, select the range of cells for which you would like to clear the contents. Then drag the autofill handle to the the top left corner of the selection whilst holding down the shift key. Your selected contents should then be deleted.

View all Excel hints and tips