Date/Time Difference Formula 1

[mjjks]

Hi All.
This thing drives me nuts. Maybe because it's a Monday, or because I work with Crystal about once a year.

Here's an issue. I have start/end date fields. Need to get DateDiff for day, hour, munites for further calculations (Count/Sum). Formula I have gives me correct output for some records (when time is on the same day) and bad for others (when hours span overnight).
So, for example StartDateTime="1/10/2007 8:33:10PM" and EndDateTime="1/11/2007 4:33:00AM" gives me "00 Days,-16 Hours,00 Minutes". Should be just 8 Hours. Any ideas what I'm doing wrong? Thanks

[blue]if[/blue] {usp_RPT_RW_CONDS_TIME;1.TIRE_CHAIN_RSTRCT_DESC} [blue]like[/blue] "Condition A*" [blue]then[/blue]
  
    [blue]NumberVar[/blue] DaysDiff := [blue]DateDiff[/blue]("d",{usp_RPT_RW_CONDS_TIME;1.ENTRY_DTTM},{usp_RPT_RW_CONDS_TIME;1.EXP_DTTM});
    [blue]NumberVar[/blue] HourStart := [blue]Hour[/blue]({usp_RPT_RW_CONDS_TIME;1.ENTRY_DTTM});
    [blue]NumberVar[/blue] MinStart := [blue]Minute[/blue]({usp_RPT_RW_CONDS_TIME;1.ENTRY_DTTM});
    [blue]NumberVar[/blue] HourEnd := [blue]Hour[/blue]({usp_RPT_RW_CONDS_TIME;1.EXP_DTTM});
    [blue]NumberVar[/blue] MinEnd := [blue]Minute[/blue]({usp_RPT_RW_CONDS_TIME;1.EXP_DTTM});

    [blue]NumberVar[/blue] HourDiff:= 0;
    [blue]NumberVar[/blue] MinDiff := 0;
 
    [COLOR=green]// get days and hours[/color green]
    [blue]If[/blue] DaysDiff >= 1 [blue]then[/blue]
        (
        [blue]If[/blue] HourStart > HourEnd [blue]Then[/blue]   
            (
             HourDiff := (HourEnd - HourStart) + 24;//adjust number of hours
             DaysDiff := DaysDiff - 1;[COLOR=green]//adjust the number of days[/color green]
            )
        [blue]else[/blue]
            HourDiff := HourEnd - HourStart;
        )
    [blue]else[/blue]
       HourDiff := HourEnd-HourStart;

    /[COLOR=green]/get minutes[/color green]
    [blue]If[/blue] HourDiff >= 1 [blue]then[/blue]
        (
        [blue]If[/blue] MinStart > MinEnd [blue]Then[/blue]   
            (
            MinDiff := 60 + (MinEnd - MinStart);[COLOR=green]//adjust minute[/color green]
            (HourDiff := HourDiff - 1)[COLOR=green]//adjust hour[/color green]
            )
        [blue]else[/blue]
            MinDiff := MinEnd - MinStart
         )
    [blue]else[/blue]
         MinDiff := MinEnd - MinStart;

    [COLOR=green]// output[/color green]
    [blue]Totext[/blue](DaysDiff,"00") + " Days," + [blue]Totext[/blue](HourDiff,"00") + " Hours," + 
    [blue]ToText[/blue](MinDiff,"00") + " Minutes";

 

[lbass]

Why not just start out with minutes?

numbervar datetimediff;
numbervar hrs;
numbervar mins;

if {usp_RPT_RW_CONDS_TIME;1.TIRE_CHAIN_RSTRCT_DESC} like "Condition A*" then
datetimediff := datediff("n",{usp_RPT_RW_CONDS_TIME;1.ENTRY_DTTM},{usp_RPT_RW_CONDS_TIME;1.EXP_DTTM});

days := datetimediff/1440;
hrs := remainder(datetimediff,1440)/60;
mins := remainder(datetimediff,60);

if datetimediff > 0 then
totext(days,0,"")+" Days " +totext(hrs,0,"") + " Hours "+ totext(mins,0,"") + " Minutes"

-LB

 

[mjjks]

lbass,
Thank you so much.
It works nicely and you get a star.

 

[mjjks]

lbass,
I'd like to ask you another question which is sort of related to I asked initially.

I had to modify your formula to get just minutes so I can sum them for a group. Once summed, I will brake it into days,hours, minutes.

numbervar mindiff; // minutes

if {usp_RPT_RW_CONDS_TIME;1.TIRE_CHAIN_RSTRCT_DESC} like "Condition A*" then
    mindiff := datediff("n",{usp_RPT_RW_CONDS_TIME;1.ENTRY_DTTM},{usp_RPT_RW_CONDS_TIME;1.EXP_DTTM});
    if mindiff > 0 then
        mindiff
    else
        0;

I duplicated this formula for conditions B and C, but if I place them side by side into details section, they display the same number. To get counts for different sections, do I need to create 3 detail sections?

I tried to do that and suppressed each details section, so there's no repeats but my sum doesn't come out right.

What approach would you suggest?
Thank you so much.

To clarify, this is my data layout:

section_name   condition     start_dttm        exp_dttm
------------   -----------   ----------        ----------
Section 1      Condition A   1/1/2007 02:00PM   1/1/2007 08:00PM
Section 1      Condition A   1/8/2007 11:00AM   1/9/2007 03:00 PM
Section 1      Condition B   1/3/2007 02:00PM   1/7/2007 08:00PM
Section 2      Condition C   1/12/2007 11:00AM   1/12/2007 03:00 PM

From above, I'm trying to get this:

          Cond. A Time    Cond. A Count   Cond. B Time
          ------------    -------------   -------------
Section 1  3 days 1 hr          35          12 hours 57 min
Section 4  3 hours              12          2 days 37 mins
Section 7  1 hour                8          8 hours 30 min

 

[lbass]

You need to use different variable names in each formula, e.g., mindiffA, mindiffB, mindiffC. They can be in the same section.

-LB

 

[mjjks]

It works. Many thanks.
I thought that if formula has a different name, variable is isolated, but seems not.