98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » Conditional formatting/IF function
Conditional formatting/IF function
Resolved · High Priority · Version 2010
Thu 16 May 2013, 14:01Delegate Katy said...
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
Thu 16 May 2013, 15:53Trainer Simon said...
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
Thu 16 May 2013, 16:21Trainer Simon said...
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
Thu 16 May 2013, 16:51Delegate Katy said...
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
Thu 16 May 2013, 17:26Trainer Simon said...
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
Thu 16 May 2013, 18:14Trainer Simon said...
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 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
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Difference between Two DatesThe DATEDIF function computes the difference between two dates. The DATEDIF function is as follows: |
