Microsoft Excel: How do I highlight a row based on two columns, each with a range of values?
-
I have an Excel spreadsheet with call data which has a Day col (Mon, Tue, Wed...) and a Time col (00:00AM). I'd like to highlight rows from Mon-Fri between 8:00AM and 3:00PM.
-
Answer:
Assuming that your columns are A and B, you can use the following formula in the conditional formatting dialogue (make sure that you highlight the range you want to format starting with cell A1): =AND(OR($A1="Mon",$A1="Tue",$A1="Wed",$A1="Thu",$A1="Fri"),AND($B1>=(1/3),$B1<0.625))
JJ Olsen at Quora Visit the source
Other answers
Form a table with days of week (Mon - Fri) and 1 next to the them. I have called it weekday.Then assuming the day of the week is column A and time in B, use the following formula in Column C=IF(AND(IFERROR(VLOOKUP(A2,weekday,2,FALSE),FALSE),B2>=0.3333332,B2<=0.625),1,0)(of course it it better to write =value("08:00 PM") and =value("03:00 PM") in separate cells and use that in the formula instead of 0.33 and 0.625, as it would be more precise.)Now use conditional formatting based on the value in column C. If value = 1, highlight / colour the background of the entire row.
Sachin Bhat
Best way is by using "Conditonal Formatting" function on Excel. Short cut -> Alt+o+d.
Mehul Srivastava
Related Q & A:
- How do I create a web based msn?Best solution by Yahoo! Answers
- How do I open a Microsoft outlook email account?Best solution by Yahoo! Answers
- How do I make a home based server?Best solution by lifehacker.com
- How can I get a bahrain transferable visa in order to find a job?Best solution by evisa.gov.bh
- How can I get a home based online job?Best solution by forbes.com
Just Added Q & A:
- How many active mobile subscribers are there in China?Best solution by Quora
- How to find the right vacation?Best solution by bookit.com
- How To Make Your Own Primer?Best solution by thekrazycouponlady.com
- How do you get the domain & range?Best solution by ChaCha
- How do you open pop up blockers?Best solution by Yahoo! Answers
For every problem there is a solution! Proved by Solucija.
-
Got an issue and looking for advice?
-
Ask Solucija to search every corner of the Web for help.
-
Get workable solutions and helpful tips in a moment.
Just ask Solucija about an issue you face and immediately get a list of ready solutions, answers and tips from other Internet users. We always provide the most suitable and complete answer to your question at the top, along with a few good alternatives below.