hh:mm:ss conditional formatting

[stanl]

I query SQL Server through Access and into an Excel Pivot. Many of the fields are summaries of various call times and these are calculated as decimal hours. In the Pivot one column for Idle Time I gave a conditional format for values >.5 (30 minutes). The users asked the time be shown as hh:mm:ss, so I added a format() function in my Access query so the data arrived in Excel in that format. I then tried to re-do the conditional format for values > 00:30:00 and it seems no matter what I try all the cells are highlighted, or seem to meet the condition. I am missing something obvious, but would appreciate any replies.


TIA

Stan

[Kenneth Hobs]

First off, let's get the math right. Use > 0.5/24.

[stanl]

The math is correct; SQL query is Format([table].[field]/86400,"hh:mm:ss") - original query to get decimal hours was [table].[field]/3600. As I wrote, the data is now calculated and presented in Excel in hh:mm:ss (general format), then Pivoted. Sorry if I wasn't more clear with I query SQL Server through Access and into an Excel Pivot

[Kenneth Hobs]

I would check that the import is coming in as a time value rather than a text value. If text value, then use TimeValue() to convert the text to a value. Otherwise, use math to convert a value to what you are comparing it to. Your first comparison was to a half hour (0.5) I guess. As such, I guess one would do: =(A1*24)=0.5.

[stanl]

Again, maybe I'm not being clear, but this is not an import per se. The SQL Server Tables keep times in unix timestamps saved as doubles. These are linked to An Access table as I need data for both calculations and formatting. So I perform the query from access and the data is linked to Excel as a background to the Pivot. Everything is calculated correctly, it is just the appearance has changed, So rather than a conditional format highlighting cells with a value >.5 (a numeric decimal hour) I need a way to highlight a general cell with values > 00:30:00. So, again without you trying another math lesson - the data is presented in its LCD - it just looks different (based on what the end users want) - so I need to adjust the conditional formula for the new appearance.

[Kenneth Hobs]

I don't know how else to help. One would use math and maybe a TimeValue() to create the proper conditional format. It is either a number or a string. If you can attach a simple pseudo file, I can help better.

e.g.
=A1> TimeValue("00:30:00")

[stanl]

Turns out a little bit of Occam's Razor was needed >"0:30:00" did the trick. I was close with some other tries, but as you probably know Excel often puts extra " in conditional formatting. NOTE: I reformatted the SQL Query to "h:mm:ss" as the deviant times have never exceeded 3 hours. Again, thanks - sorry if I appeared difficult in my replies.

Stan

[Kenneth Hobs]

No problem, I have been "cut" before. Nice to see you still around.