Time Formating for Conditional Formating

**harryhoudini66** · 02-12-2013, 04:13 PM

Hello, I am trying to apply conditional formating to a couple cells based on the value found in them. I can get them to work half of the time. The value in these cells is being pulled in via Macro from another system.

The problem is that some information gets changed to a different time format. For example, the conditional formating says if the value is greater than :40, then highlight red. This works fine if the value is :41-:59.

Any value beyond that will not highlight. For example, if I enter 1:34 or 1:01:34. When I look at the formating for the cell, it shows as 1:34:00 am. How do I fix this so it does not change format and the conditional formating applies? I hope this makes sense.

Thanks,

**harryhoudini66** · 02-13-2013, 04:08 PM

I was able to resolve this but I am not sure it was the best method. I had the macro bring in the values in seconds to sheet2. I then made a reference to this cell from sheet1 and multiplied it by 0.00001159. This converted them all into time duration. I also formatted all the cells to h:mm:ss. So now the conditional formatting works 100%. For example:

237.792678833*0.00001159=0:03:58

If there is a better method, please let me know.

**ChemistB** · 02-13-2013, 04:29 PM

I assume we are looking at D3? That value is being seen as Text and not a true time.
I would do a Replace : with 0: to convert that to time.
In your conditional formatting
":40" is also text. You can say > 0:40, or Between 0:30 and 0:40
Does that help?

**Norie** · 02-13-2013, 04:33 PM

You've used text strings in the conditional formattin, and you've enter the times without the hour part, which means that Excel doesn't see them as times.

Try entering the times with the hour part and use the TIME worksheet function in the conditional formatting.

**harryhoudini66** · 02-13-2013, 07:14 PM

Originally Posted by ChemistB

I assume we are looking at D3? That value is being seen as Text and not a true time.
I would do a Replace : with 0: to convert that to time.
In your conditional formatting
":40" is also text. You can say > 0:40, or Between 0:30 and 0:40
Does that help?

Thanks ChemistB. Not exactly. All the cells that show time with the exception of D3 look weird also. For example, look at Cell C3. You will see it reads 3:58:00 AM. It should be 0:03:58. Relating to cell D3, I would have to add 0:0: as the value is actually seconds i.e. 0:00:16. Hope this makes sense.

**harryhoudini66** · 02-13-2013, 07:17 PM

Originally Posted by Norie

You've used text strings in the conditional formattin, and you've enter the times without the hour part, which means that Excel doesn't see them as times.

Try entering the times with the hour part and use the TIME worksheet function in the conditional formatting.

Thanks, I did not actually enter the values myself. The values came across that way from the CMS system it pulled it from. I am pretty sure this is the reason the CMS system allows the info to be transmitted in seconds as well.

If I add the 0:00 to the value, it will show 16 seconds as 12:00:16 AM.

**Norie** · 02-13-2013, 07:41 PM

Format the cell as hh:mm:ss.

**harryhoudini66** · 02-13-2013, 08:03 PM

Another thing I see is that dividing the 237.792678833 by 86400 will also equal 0:03:58. Why does Excel take a value in seconds and converts it to decimal seconds?

**Norie** · 02-13-2013, 08:12 PM

Where does 237.792678833 come from?

**ChemistB** · 02-14-2013, 09:33 AM

A little bit about time and Excel. Excel takes the unit of time to be 1 day. 1 = 1 day, if you format it to time, you'll get 12 midnight or 0:00:00. An hour = 1/24 or 0.041667, a minute 1/(24*60) or 0.000694.

237.792678833 by 86400 = 0.0027522 or a little over 3 minutes. The cell is formatted as h:mm:ss so gives
0:03:58. It does not have anything to do with decimal seconds.

If your program is importing time as text, then the first thing you need to do is convert this text to number format. Is the example that you uploaded showing exactly how data is imported?