Negative times and conditional formating.

[edwardvandepol]

I want to display the difference between forecasted time and actual time on a dashboard. I also want conditional formatting on these cells so that all positive values (later than expected) become redder as they increase, and all negative values (earlier than expected) become greener as they decrease. I can't use the 1904 date format because I work in a large company where all files are linked to others.

Currently, I have four suboptimal solutions:

1. Use a formula to display the negative time as text (this results in losing conditional formatting).
2. Multiply by 24 and use a "number format" (e.g., 15 minutes becomes 0.25, which isn’t as easy to read).
3. Use a simple subtraction formula (this keeps conditional formatting, but negative values show as ########).
4. Create an elaborate macro that requires manual activation each time data is updated, which handles these tasks manually.

I feel there must be a cell format that would make the third option work. It correctly applies the colors but needs to show values in the format "-00:15" while keeping the underlying number (e.g., -0.104166666 for -15 minutes) as is.

 

[Micron]

I can't glean from that what it is you want to see in terms of values. Perhaps do the calculation and format the cell as General. This will make the data type of the values as Double and you should be able to drive your CF from that. If you don't want to see values like -1.00005787 (one day earlier subtracted from a date) then do the calculation using a data type that works for you but base the CF on the Double values (which you can hide). Getting a gradient for background colours will be a challenge that I think you'll only solve with VBA. Might as well just go there if it's that important.

EDIT - the vba code can probably run automagically. I believe there is an event that will fire upon calculation. Or perhaps sheet change event would suffice.

 

[Ludwig]

If finding time difference, then you must subtract the earlier from the later otherwise, as you've found, you get #'s. Hence you need to do an "IF" one is greater then the other to ensure you subtract the lesser from the greater to format it as time.

One option to avoid any VBA is as follows -

• Insert a hidden column which is for example B5 - B6. This will produce a negative or positive number formatted as a number.
• The second column is visible to the user and has a more complicated formula using the TEXT function to cheat the cell into producing a "-0:15" for example.

  IF( B5 < B6 , "-" & TEXT( B6 - B5 , "hh:mm" ) , TEXT( B5 - B6 , "hh:mm" )

The first column is then used in the conditional formatting formula for the second to change to colour as required, but because it's hidden the user won't see it.