Conditional formatting and Strings

Question

Hello,

I have a problem with conditional formatting in a sheet I am working on. I have created the rules but the formatting did not appear so I searched the web and found an article where someone had somehow a similar problem and he was asked to remove (strings) from the beginning of the text. I clicked on of the cells and there were no extra leading spaces, however I just hit the back space button and enter. The formatting worked. In the example below, both columns August 21st and 22nd have the same values. However, in Aug 21st I double clicked the cell, went to the beginning of the value and then hit the backspace key and the conditional formatting worked as I have mentioned.

The problem is that I will need to do the same for each cell which is time consuming. Is there a way to do that for the whole sheet without having to copy/paste to additional columns/rows (like trimming)?

The second problem is in the 4th row which is supposed to display the break time of an agent. In the below example a certain agent's break time is 44 minutes. The rule is to highlight the cell in case the break times exceeds 1 hour. Excel here reads the value as a time not a duration so it reads 12:44:22 AM. I need to make this cells reads as a duration.  

Tariq

Answer 1

One: In this case, you should set your cell formatting as General. To do this, before applying the conditional formatting, Select cell range where you want to apply the Conditional Formatting and then set the cell formatting type as GENERAL (Press Ctrl+1, Click Number tab, Select General and Click Ok).

Two: You can use mathematical numbers for time duration, like =(1/24) (1 day divided by 24 hours).  Apply conditional formatting with a new rules like: =A1>(1/24) where A1 considered as your time duration column.

Answer 2

a) Convert text to numbers

Select the cells to convert

Click Data \ Text to Columns \ Finish

b) time duration

Format the cell using this number format

or

[m]

Andreas.