Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Todd F.
 
Posts: n/a
Default Imported Date & Time format with calcs. managed in excel from imrp

Sorry for lengthy question but need to master this topic - thanks for time
also I need to do ongoing date work so i wan to know the best way ongoing not
a quicky solution if possible.

I will make use of any external sites or resources you point me tward.


THE QUESTION:

I have reports coming out of cognos imromptu that have the following format
in the interval or time field - I need to use case created and case closed
(impromptu) allot but these are also general excel date time format questions:

In impromptu the date field is formatted like "000 00:00:00.000"
day hr
min sec etc.

When I export to excel I get the following in the field and in the formula bar

Create date cell " 1/2/05 17:04" , in formula bar I get "1/2/2005
5:04:30pm"

Close date cell "1/2/2005 20:35" , in formula bar I get "1/2/2005
8:35:10 pm"

The calculated field of close date less the create date from report writer (
impromptu gives the following in excel

cell " 0 3:30" , in formula bar I get "1/0/1900 3:30:40 AM"

Now in Excel if I do a calculation on the two dates I get the following:

cell " 0 3:30 , in formula bar I get "c11-b11"

Something you should know - I can create afield in report writer that will
dump the count of days from 1900 so i get a cleannumber calculation. Then I
can do celan calcs in excel.

However I would like to know how to handle dates and not rely on the days
from 1900 method

? is there a way I should try to format in report writer - limted!

? what is the soundest way to handle dates with time in them so I can get
clean hours and minutes - some time I need to knwo hours been created and
closed , sometimes I need count of days open.

I do need to get clean fields with year, month, and day so I guess zi need
to format so a calc or parse can be done - any thoughts. I would even be
interested in calculating the week of the year.

I work in a 24/7 tech support company - working as an analyst for a point of
reference so the pressure is off on week days & weekend issues but I would
liek to learn about this as well as handeling holidays.

Again thank you for your personal time on this.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
imported impromtu report date issue - fix by format or formula Todd F. Excel Discussion (Misc queries) 3 July 7th 05 09:57 PM
Conditonal Format with a date format Kevin Excel Discussion (Misc queries) 2 April 27th 05 10:20 PM
Excel enters date as a text format Kane Excel Discussion (Misc queries) 3 March 22nd 05 10:20 PM
Problem with Date format from VBA code twig Excel Discussion (Misc queries) 3 December 7th 04 07:01 PM
Date and Time Macro m.j.anderson Excel Discussion (Misc queries) 1 December 1st 04 01:35 AM


All times are GMT +1. The time now is 08:22 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"