• Status: Solved
• Priority: Medium
• Security: Public
• Views: 255

# Calculate intervals

Whats the correct way to write a formula to calculate the difference between

(Occ_date & occ_time ) - charted_date & charted_time when the data appears as below?

OCC_DATE      OCC_TIME      CHARTED_DATE      CHARTED_TIME
20110808      14:00      20110808      15:41
20110808      14:00      20110808      15:41
20110808      14:00      20110808      15:41
20110808      16:00      20110808      19:18
20110808      21:00      20110808      20:13
0
hwassinger
• 4
• 4
• 2
1 Solution

Commented:
Do you have 4 fields or 2 fields

Are they Strings or Date and Time fields?

Crystal has a DateDiff function.  You can get the difference in many different intervals (seconds, minutes, hours, days, etc)

DateDiff("s",{StartDateTimeField}, {EndDateTimeField})

In your case if the fields are strings with the data shown, try this formula

``````Local StringVar strStartDate := {Occ_date};
Local StringVar strStartTime := {Occ_Time};
Local StringVar strEndDate := {charted_date};
Local StringVar strEndTime := {charted_time};

Local DateTimeVar StartDateTime;
Local DateTimeVar EndDateTime;
StartDateTime := DateTime(Date(Picture(strStartDate,'xxxx/xx/xx')),Time(strStartTime));
EndDateTime := DateTime(Date(Picture(strEndDate,'xxxx/xx/xx')),Time(strEndTime));

DateDiff('s',StartDateTime,EndDateTime)
``````

mlmcc
0

Author Commented:
Thanks, I'll validate tis in the AM.
0

Commented:
First of all, for the record, we are assuming that your date field is yyyymmdd.  Since your examples are all 0808, it's impossible to be sure whether that's mmdd or ddmm, so I wanted to mention it.

FWIW, assuming that you have 4 string fields, here are a couple of alternatives.  They both do the same thing as mlmcc's formula.  Just in a slightly different way, and are a bit shorter.

DateDiff ("s", DateTime (Picture ({OCC_DATE}, "xxxx/xx/xx") + " " + {OCC_TIME}),
DateTime (Picture ({CHARTED_DATE}, "xxxx/xx/xx") + " " + {CHARTED_TIME}))

Or, breaking the date fields down instead of using Picture:

DateDiff ("s",
DateTime (Left ({OCC_DATE}, 4) + "/" + Mid ({OCC_DATE}, 5, 2) + "/" +
Right ({OCC_DATE}, 2) + " " + {OCC_TIME}),
DateTime (Left ({CHARTED_DATE}, 4) + "/" + Mid ({CHARTED_DATE}, 5, 2) + "/" +
Right ({CHARTED_DATE}, 2) + " " + {CHARTED_TIME}))

I like the Picture one myself.  Much simpler.  But thought I'd post the longer version too, in case you wanted to be able to see how the string date was being broken down and the "/"s added.  FWIW, if I was trying to use this in a record selection formula, like to only select the records with more than X days between those dates, then I'd probably use the last formula.  It seems most likely to be passed to the server.  In particular, I don't know if CR would be able, or try, to translate the Picture function into something that the server could understand.  Maybe it would.  Left/Mid/Right just seems "safer".

James
0

Author Commented:
Mimcc This returns an error"Bad time format string.
0

Commented:
Is the field a string?

It worked in CR XI

Can any of the fields be NULL?

mlmcc
0

Author Commented:
The field is stored as a string and there should be no null fields
0

Commented:
Could some have a bad time like 24:30 or 15:65

Might try this

``````Local StringVar strStartDate := {Occ_date};
Local StringVar strStartTime := {Occ_Time};
Local StringVar strEndDate := {charted_date};
Local StringVar strEndTime := {charted_time};

Local DateTimeVar StartDateTime;
Local DateTimeVar EndDateTime;
If IsTime(strStartTime) then
StartDateTime := DateTime(Date(Picture(strStartDate,'xxxx/xx/xx')),Time(strStartTime))
Else
StartDateTime := DateTime(Date(Picture(strStartDate,'xxxx/xx/xx')),Time(0,0,0));

If IsTime(strEndTime) then
EndDateTime := DateTime(Date(Picture(strEndDate,'xxxx/xx/xx')),Time(strEndTime))
Else
EndDateTime := DateTime(Date(Picture(strEndDate,'xxxx/xx/xx')),Time(0,0,0));

DateDiff('s',StartDateTime,EndDateTime)
``````

You can also test dates the same way with IsDate

mlmcc
0

Author Commented:
Mimcc is this returning in seconds? If so how to convert to minutes but this looks correct
0

Commented:
Yes, it is returning seconds.
Interval is the first argument.  Use n for minutes

DateDiff('n',StartDateTime,EndDateTime)

mlmcc
0

Commented:
Or, if you want a little more precision, get the number in seconds and then divide by 60 to convert it to minutes.

As for the error that you were getting, if you have some bad date or time strings, you should try to figure out what they are and why they're there.  If users are entering them, maybe there are issues there that you need to look at.  For example, maybe they're just sometimes entering the dates as yyyyddmm, instead of yyyymmdd.

James
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.