conditional formatting based on time of day in excel. ?

Question

Ok, basically what I am trying to do is highlight a cell if the elapsed time from an appointment is 2 hours or greater. I have tried to do this two ways. First I have the appointment time in one column, the current time in the next and the next column is the running time total (hh:mm) of column b-column a. So I tried to set the conditional formatting as cell value is greater than 2:00. this turns the value into a decimal number and won't stay in the hh:mm format. I have also tried to do a conditional format on the column with the appointment time, and set the value as greater than 2 hours past the appointment time. Example,
if the appointment time was 08:00 am the value to be greater than is 10:00 am. This too changes the value to a decimal number. I am getting frustrated, and I'm sure there is a simple solution.

Answer 1

To be honest with you, don't use conditional formatting in excel to give appointment alert, even if it's set up, the fill color of the cell will only show when the sheet is open and calculated.

Conditions that setup would fail when worksheet is open for

1. viewing

2. click to select cell(s)

3. formatting certain cell(s)

4. running vba/macro with no code of calculate

May be give MS outlook a try, make a shortcut of it and put it in C:\Documents and Settings\< login name >\Start Menu\Programs ,then it will start up whenever you bootup the pc

Hope this helps.

Answer 2

Times are stored as decimals behind the formatting in Excel, so the conditional formatting is correct when it shows your time of 2 hours as a decimal.



As long as you have the decimal 0.0833333333333333, this is equal to 2 hours and your conditional formatting should work as you want it to.

Answer 3

As to your side note: NO. Doing what you want is called engaging in dialog and is against the rules. You CAN add "additional details" and respond to specific answers, but that means the person you are adding info FOR has to come back in order to see you added more information. So, NO, there is NO easy way. Simply post again and like you did here, simply add in the additional information. Now, for the formula question: I use Excel 97, but I think it will be mostly the same. I have never wanted to set colors based on a result, but in thinking about it, I think your answer is in using "formulas as formatting criteria". Here is what I found in the HELP (an excerpt) when I looked into formatting. Apparently you CAN set the color on the fly by use of a formula. They used a conditional format to set the color of the text in a cell (in an example I did not copy here because there is no way to include the illustrations) so I think there must be a way to set the background color as well. Here is what I copied for the help: ===== start block==== Formulas as formatting criteria You can compare the values of the selected cells to a constant or to the results of a formula. To evaluate data in cells outside the selected range or to examine multiple sets of criteria, you can use a logical formula to specify the formatting criteria. · If you select the Cell Value Is option and compare the values of the selected cells to the result of a formula, you must start the formula with an equal sign (=). · If you select the Formula Is option, the formula you specify must return a logical value of TRUE (1) or FALSE (0). You must start the formula with an equal sign (=). The formula can evaluate data only on the active worksheet. To evaluate data on another sheet or in another workbook, you can define a name on the active worksheet for the data on another sheet or workbook, or enter a reference to the data in a cell of the active worksheet. Then refer to that cell or name in the formula. For example, to evalute data in cell A5 on Sheet1 of the workbook Fiscal Year.xls, enter the following reference, including the equal sign (=), in a cell of the active sheet: =[Fiscal Year.xls]SHEET1!$A$5 The formula can also evaluate criteria that is not based on worksheet data. For example, the formula =Weekday("12/5/99")=1 returns a value of TRUE if the date 12/5/99 is a Sunday. Unless a formula specifically refers to the selected cells you are formatting, the cell values do not affect whether the condition is true or false. If a formula does refer to the selected cells, you must enter the cell references in the formula. ====END block===== Note above where they used the formula =Weekday("12/5/99")=1 in the conditional format. This evaluates to TRUE or FALSE and in the conditional format, TRUE or FALSE can then make an action happen, like set the background color to blue. At least, this is what I would be looking for to set the colors based on the value of the weekday() function. In the database languages I am familiar with, and also in something like javascript or BASIC used in Excel, looking at the cells with the dates and then using a formula instead of a fixed value is the way to go. Sorry for the incomplete answer, but I am at work and on my lunch hour and my time is up for personal business.