Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Elapsed Time returned from query
I am using excel to query DB2. The field in DB2 Elapsed_Time has been
calculated from a End_Date_Time Start Date Time. The Problem is that excel treats this value 00:00:00 as mm/dd/yyyy 00:00:00 AM/PM and there is nothing I can do to change the format once it is in Excel. Right now I have reverted to querying the Start/End Date Time and performing the calculation in excel and formating as an elapsed time. I am using the Microsoft Query Wizard from excel. Unfortunately that will not work for some of the data as CPU Elapsed Time is calculated and there is no CPU Start/Stop Date Time values it has been summarized in the database. I am totally stumped :( We have a query gateway (Web tool) and if I use that to query DB2 and export to excel it works fine (elapsed time can be formatted as elapsed time). So the issue seems to be in the query return from query manager but I cannot find any way to format using the query manager application. Any help would be awesome. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Elapsed Time returned from query
Hi Bob,
-- While in MsQuery, double-click the affected field header. The Edit Column dialog should appear. -- Concatenate a custom string before the field name. The syntax, depending on DB2, is probably: <apostrophe<your string<apostrophe<plus sign<field name for example: 'DeleteMe'+Elapsed_Time Important: It should NOT start with an equals sign. -- Return that to Excel. Excel will treat it as a string. -- In another column, parse out the custom string with RIGHT, wrap it in VAL, and apply the number formatting you want. Another approach: Depending on DB2, it might recognize functions similar to Excel's such as Hour, Minute, Second (the DB2 documentation should say, or you could ask your DB2 admin). In MS Query's Edit Column dialog you could try something like MINUTE(Elapsed_Time) or maybe it's MINUTE [Elapsed_Time]. Then repeat as needed for hour and second. Then, in Excel, use the TIME function to bring them together, and then number format as needed. Good luck, Greg Lovern http://PrecisionCalc.com On Feb 1, 6:09*pm, Bob wrote: I am using excel to query DB2. *The field in DB2 Elapsed_Time has been calculated from a End_Date_Time Start Date Time. *The Problem is that excel treats this value 00:00:00 as mm/dd/yyyy 00:00:00 AM/PM and there is nothing I can do to change the format once it is in Excel. *Right now I have reverted to querying the Start/End Date Time and performing the calculation in excel and formating as an elapsed time. *I am using the Microsoft Query Wizard from excel. Unfortunately that will not work for some of the data as CPU Elapsed Time is calculated and there is no CPU Start/Stop Date Time values it has been summarized in the database. *I am totally stumped :( We have a query gateway (Web tool) and if I use that to query DB2 and export to excel it works fine (elapsed time can be formatted as elapsed time). *So the issue seems to be in the query return from query manager but I cannot find any way to format using the query manager application. Any help would be awesome. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
No Results returned from Access query | Excel Discussion (Misc queries) | |||
How do I sort the information returned from a query? | Excel Discussion (Misc queries) | |||
Calculate Ending time using Start Time and Elapsed Time | Excel Worksheet Functions | |||
Query returned more data than will fit on a worksheet | Excel Discussion (Misc queries) | |||
This query returned no data | Excel Worksheet Functions |