Elapsed time calculations

[DanSleep]

Hi,

I am new to CR, using V8.5. I am striving to get some actual training, but I have an immediate problem I need to resolve. I did a search here first, but I didn't find anything that helped. I may not have been using the correct search words. I apologize if this has been covered in another thread.

My goal is to calcuate response times, or the number of hour, minutes, and seconds between the time an event was assigned to a resource, and the time that resource arrived at that event.

I actually have 4 time stamps that I will eventually need to deal with: Event Creation, Event Dispatch, Event Arrival, Event Clear.

The time is stored in a string in military format like so:

20090606071633ED

I need to use the 071633 for my time calculations. I was trying to use MID but I could not get it to do what I wanted. Now that I think about it, there will be instances where the event will begin prior to midnight, and continue into the next day. Will I need to use the entire date/time data?

Eventually I will be exporting this data to Excel for further massaging as needed.

Any help would be greatly appreciated.

Thanks

 

[IanWaterman]

First you need to convert your strings to datetimes

Do this for your 4 events one formula each

@EventCreation
datetime(tonumber(left('20090606071633', 4)), tonumber(mid('20090606071633', 5,2)), tonumber(mid('20090606071633', 7,2)),
tonumber(mid('20090606071633', 9,2)), tonumber(mid('20090606071633', 11,2)), tonumber(mid('20090606071633', 13,2)))

Replace number string with your field eg {EventCreation}

Then use datediff to find difference in seconds

@diff1
datediff("s", @EventCreation, @EventDespatch)

Ian

 

[hilfy]

Because you have the possibility of an event spanning midnight, you'll need to work with the date portion of this in addition to the time portion. Try something like this:

NumberVar y;
NumberVar m;
NumberVar d;
NumberVar h;
NumberVar m;
NumberVar s;

y := ToNumber(Left({table.datefield}, 4));
m := ToNumber(Mid({table.datefield}, 5, 2));
d := ToNumber(Mid({table.datefield}, 7, 2));
h := ToNumber(Mid({table.datefield}, 9, 2));
m := ToNumber(Mid({table.datefield}, 11, 2));
s := ToNumber(Mid({table.datefield}, 13, 2));

DateTime(y, m, d, h, m, s)

Convert the relevent fields to datetime fields and then subtract the creation date from any of the others to get the elapsed time since the event was created.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[DanSleep]

Thanks for the prompt replies. I will have to give it a go and see if I can get this to work.

 

[DanSleep]

I am still struggling with this a bit.

Just to confirm, I want to put this formula in the Record Selection Formula Editor?

I tried that, and I modified it to look like so:

datetime(tonumber(left({aeven.ds_ts}, 4)),

tonumber(mid({aeven.ds_ts}, 5,2)),

etc...

I get an error that states: A number, currency amount, boolean, date,time,date-time, or string is expected here.

 

[IanWaterman]

YOu need to show what exactly you have put in your select expert.

Such a formula will be slow to execute in the select as it will not parse to the database. All records will be brought back to Crystal abd filtered there. Not a problem if you have hundreds of records but could be slow if you have 10s of thousands.

If you are going to filter I would just use the strings, for example

to bring back all dates since beginning of August

left({aeven.ds_ts}, 6)) >= '20100801'

This will parse to database and execute much more quickly.

Ian