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

conditional formattingif functio

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Conditional formatting/IF function

Conditional formatting/IF function

ResolvedVersion 2010

Katy has attended:
Excel Advanced course
PowerPoint Intermediate Advanced course

Conditional formatting/IF function

Hello

Thank you for all your help so far, this is the last question (I promise!).

I currently have 4 columns:
- A start date
- An end date
- A duration in working days
- A duration in weeks worked

The last two columns contain formulas. Ideally I would like to put some conditional formatting in so that when "13" weeks and "26" weeks are hit, these are some how highlighted in a different colour.

I would also like to add another column, whereby those that are at 13 and 26 weeks have the word "Evidence" pop up.

Is this at all possible?

Many thanks

Katy

RE: Conditional formatting/IF function

Hi Katy,

Thank you for your question and welcome to the forum.

Highlight all the cells containing the week numbers and click on Conditional Formatting on the Home Tab.

Choose Highlight cell rules and select equals. Enter 13 and then choose the colour from the drop down box on the right hand side. Use one of the defaults or select Custom Format to set your own. Repeat the procedure for 26.

Add a new column. In the first cell, start an IF function.

Let's say the cell containing the first week number is D2:

=IF(OR(D2=13,d2=26),"Evidence","")

So if D2 is equal to 13 or 26 put evidence in cell otherwise leave blank.

I hope this answers your questions.

Regards

Simon

RE: Conditional formatting/IF function

Hi Katy,

Thank you for your question and welcome to the forum.

Highlight all the cells containing the week numbers and click on Conditional Formatting on the Home Tab.

Choose Highlight cell rules and select equals. Enter 13 and then choose the colour from the drop down box on the right hand side. Use one of the defaults or select Custom Format to set your own. Repeat the procedure for 26.

Add a new column. In the first cell, start an IF function.

Let's say the cell containing the first week number is D2:

=IF(OR(D2=13,d2=26),"Evidence","")

So if D2 is equal to 13 or 26 put evidence in cell otherwise leave blank.

I hope this answers your questions.

Regards

Simon

RE: Conditional formatting/IF function

Hi Simon

Unfortunately I tried this and it isn't working.

Is this because in the "weeks worked" column, the cells have formulas in them rather than numbers and therefore the conditional formatting isn't recognising the numbers 13 and 26?

Thanks again for all your help.

Katy

RE: Conditional formatting/IF function

Hi Katy,

What is the formula you are using to work out the number of weeks? It may be that the answer is formatted as a date instead of a number.

Try this formula to work out the number of weeks:

=WEEKNUM(END DATE)-WEEKNUM(START DATE)

The answer then is the number of the weeks between those two dates and then the conditional formula and If(Or works.

Regards

Simon

RE: Conditional formatting/IF function

Hi Katy,

The last formula will only work with dates in the same year. The following formula will calculates the weeks between any two dates.

=DATEDIF(F1,G1,"d")/7

The DateDif function is undocumented but you can still use it. The first argument is the start date, then the finish date and then the time interval is days. The divide by 7 results in weeks.

The conditional formatting and IF will then work.

Regards

Simon

Thu 23 May 2013: Automatically marked as resolved.

 

Training courses

Training information:

See also:

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.

Excel tip:

Adding Rows or Columns in an Excel 2010 Worksheet

If you want to add a row to an Excel spreadsheet, these are the simple steps you should take:

With your mouse, right click on the row header below where you want the new row to be added. Then, click Insert.

Follow exactly the same steps if you want to add a column to an Excel worksheet, right click on the column header, choose Insert and the new column will be inserted to the left of the selected column.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.12 secs.