Question about Microsoft Office Standard for PC

1 Answer

Conditional formating to highlight problems.

I need some help with formating a spreadsheet. I need column "a" to reflect the time it take to solve a query, if its longer that 03:00 to change colour to red. if the query is complete for the column to have no colour.
I have dropdowns on the spreadsheet. i also want colour to change when a certain status was selected and it took longer that 24:00 to also change but when its complete to change to no colour.

Posted by on

  • 2 more comments 
  • Melanie Mafolo Mar 21, 2009

    Not real time Lapsed Time.I Have a starting date and the current date. I need the colour to change when the lapsed time is 24hours and 72hours(diffrent colours for the two lapsed times) excluding weekends and holidays.

  • Dave Herard
    Dave Herard May 11, 2010

    What version of excel?

  • Gerald Russell
    Gerald Russell May 11, 2010

    are you saying you want the colour to change real time during the execution of the query in Excel?

  • bsevinc May 11, 2010

    On the column a how are you reflecting the time. Is there a start time and end time in any other cell?

×

1 Answer

  • Level 2:

    An expert who has achieved level 2 by getting 100 points

    MVP:

    An expert that got 5 achievements.

    Governor:

    An expert whose answer got voted for 20 times.

    Hot-Shot:

    An expert who has answered 20 questions.

  • Expert
  • 78 Answers

In Excel 2007 and if you have a cell (say C2) with the value of elapsed time (for example set = A2 which contains start time plus B2 which contains end time - or current time) - click in the cell (C2) then on the Home tab click on Styles >> Conditional formatting choose Highlight Cells Rules>>Greater Than... set your value in the dialog to your respective value and choose your highlight. This is a start - you can then add new rules.

to then apply this same conditional formatting to every cell in that column Ctrl-C to copy the cell you just formatted - then click on the column header to highlght the entire column - Right-click and choose Paste Special - then choose Formatting for what to paste.

It's slightly different in Excel 2003 so let us know what version you are using.

Cheers.

Posted on Mar 21, 2009

Add Your Answer

Uploading: 0%

my-video-file.mp4

Complete. Click "Add" to insert your video. Add

×

Loading...
Loading...

Related Questions:

2 Answers

IS THERE A FORMULA IN EXCEL THAT WILL HIGHLIGHT CELLS THAT MATCH A NUMERICAL INVENTORY LIST?


One way would be to create a colunn C and use the formula: =COUNTIF(B$2:B$200,A2) in cell C2. Then copy the formula down to C200. (assuming you have 200 entries in B...). This will put a "1" next to every number in Column A that has a match in column B. Then you could put a conditional formula in column A that will highlight the cell if the value in column C is equal to 1. Alternatively you could also simply filter for 1's in column C and then manually highlight the cells that show up in A!

Sep 16, 2014 | Microsoft Excel for PC

1 Answer

Excel Conditional Formatting: how do I make one cell highlight based on whether or not another cell is blank?


option: formula is
formula: =(datecell="")
select formatting of choice

Highlight the order cells, go to >format>conditional formatting, choose "formula is" instead of "cell is". When you refer to the datecell, use the first date cell in the column only, and do not use $ signs.

Jan 09, 2013 | Microsoft Excel Business & Productivity...

3 Answers

Transforming birthdates into year codes


Here is a solution that might work for you. Please be mindful that there are several different solutions that will achieve the same thing in Excel (I am assuming Excel is your software).

1) Make sure each column in your spreadsheet has a heading.

2) Highlight (select) the column of birthdays.

3) Choose Format from the menu bar.

4) Choose Cells... from the drop-down menu.

5) For the "category", choose Custom.

6) In the "type" box, type yyyy

7) Click OK and when you return to your spreadsheet, you will see only the years displayed. However, when you try to edit a cell, you will see that the entire birth date is stored and preserved.

8) Now, highlight the entire table

9) Then, choose Data from the menu bar

10) Choose Subtotals... from the drop-down menu

11) In the "At each change in" box, choose the name of the column with the birth dates

12) In the "Use function" box, choose Count

13) Leave all other choices at their defaults

14) Click OK, and you are done.

Using this method, you can continue to enter the data as you always have. In that way, the birth date info is never actually destroyed or converted. What you are doing is simply deciding what is displayed.

I hope this helps.

Aug 02, 2009 | Microsoft Office Excel 2007

2 Answers

Tab key on keyboard will not work for moving across columns excel


You need to go into options, open the transition box and un tick the "transitions navigations key" box... This same problem has been ignoring me... You can also fix the double clicking problem in here too... under "edit"

Feb 02, 2009 | Microsoft EXCEL 2004 for Mac

1 Answer

To be notified 7 days before todays date


You can write a macro for that and needs to run every time after opening of the excel sheet.

Jan 23, 2009 | Microsoft Excel for PC

1 Answer

Creating vacation accrual spreadsheet


Here is one way to do it:
1. In column A enter the name
2. In column B enter their start date in the format day-month-2008 (31-12-2008 for Dec. 31, 2008); if a day is less than 10, put zero in front (03, 04, etc.)
3. In column C put 31-12-2008 (December 31, 2008, the last day of the year)
4. In column D, put C1-B1 and format as a number with 2 places after the period (10.25 for example) if you want to track partial days, other format for no zeroes after the period (13)
5. In column E, put this formula:
(D2/30)*1.25 (assuming this is line 2)
You can then copy these lines as many times as you need to, total them, etc.

Dec 27, 2008 | Microsoft Office Standard for PC

1 Answer

Conditional formatting


http://chandoo.org/wp/2008/03/13/want-to-be-an-excel-conditional-formatting-rock-star-read-this/

Go down to the section about highlighting where there's an error. That's the basics of what you want to do. Now you need the code:

This will set a value of "True" if the string is -not- found inside. So if you want to flag it for finding the word NOT, you want the conditional formatting to kick in if the value of this is FALSE.
=iserr(find("NOT",A1))

Dec 06, 2008 | Business & Productivity Software

1 Answer

If statement that will change cell color in excel


conditional formatting is alright.

you just have to remove the $ signs in the cell reference when you set a condition.

then, copy-paste

Nov 20, 2008 | Microsoft Excel for PC

2 Answers

Help


go to page setup and under scaling you can adjust to your required percentage

Jul 22, 2008 | Microsoft Excel for PC

1 Answer

Average handle time


I have created a spreadsheet for you to a) use and b) to learn from.

It is an Automated spreadsheet (as they should be) which calculates the number of minutes in a working week or month and calculates the average time per email giving Daily, Weekly and Monthly Outputs. It takes into account Public Holidays (or for time off). You can use the Output to create Graphs etc to visually display the Output.

It also allows you to calculate a Part Month average.

I have displayed it as it was CONSTRUCTED and as it would be USED.

The As Used worksheet is Protected and the only Inputs that can be done are in the Green Boxes (also the Saturday and Sunday boxes but you will need to Unhide the Validation List to include these and then to add 2 more columns titled Is Saturday? and Is Sunday? with the appropriate If Statement.

To unprotect the sheet go to Tools - Protection - Unprotect. There is no password so leave this blank.

All the workings are still there, the columns are just Hidden. To Unhide them, highlight the columns to the left and right of the hidden columns, click on Format - Columns - Unhide. To hide them again, highlight the columns that you want hidden, click on Format - Columns - Hide.

The LOGIC used (as in Functions) may seem complex but if you read the Descriptions in the first row you should be able to work out what and why it was done that way. Click on a cell to see what Function was used where.

You said that your spreadsheet was becoming a real mess, well I have created a monster for you (but not a mess).

I have uploaded the file to here:

http://users.tpg.com.au/lesliecl/

Hope this gives you the push to really start using Excel.

Apr 04, 2008 | Microsoft Excel for PC

Not finding what you are looking for?
Microsoft Office Standard for PC Logo

113 people viewed this question

Ask a Question

Usually answered in minutes!

Top Microsoft Business & Productivity Software Experts

Brian Sullivan
Brian Sullivan

Level 3 Expert

27725 Answers

Les Dickinson
Les Dickinson

Level 3 Expert

18298 Answers

Tony

Level 3 Expert

2598 Answers

Are you a Microsoft Business and Productivity Software Expert? Answer questions, earn points and help others

Answer questions

Manuals & User Guides

Loading...