date formula that will total up hours 1

[Charley71]

Here is my dilema-this is an export from a third party software package that is a .csv which I changed to a .xls.

The cells contain this data (all in one column) looks like this: (example)
1 day 00:02:07
1 day 01:00:57
1 mon 10 days 03:12:16

If I use crystal to report against this worksheet is there a formula that can convert all the different combinations(sometimes just months and hours, sometimes just days and hours, sometimes all three as above) into total hours? Because there isn't a way to tell what month this is I would just like to assume a month is 30 days.

 

[lbass]

What do you expect the results to be for the above sample data?

-LB

 

[Charley71]

24:02:07
25:00:57
963:12:16

 

[lbass]

stringvar array x := split({@string},":");
stringvar array y := split(x[1]," ");
numbervar calc1;
numbervar calc2;
numbervar calc3;

if ubound >= 2 then
calc1 := (
select y[2]
case 'day', 'days' : val(y[1])*24
case 'mon','mon' : val(y[1])*30*24
));
if ubound >= 4 then
calc2 :=
(
select y[4]
case 'day', 'days' : val(y[3])*24
case 'mon','mon' : val(y[3])*30*24
));
calc3 := val(right(x[1],2));

if ubound(x) >= 2 then
totext(calc1 + calc2 + calc3,0,"") + ":"+
mid({@string},instr({@string},":")+1) else
{@string};

Substitute your field for {@string}.

-LB

 

[Charley71]

sorry Lbass, I just noticed that it isn't quite calculating correctly. Here were my results. The first three are correct but after the one that says 1 mon 1 days 03:12:16 they start doing this:

1 day 00:03:01 264:03:01
1 day 00:06:30 264:06:30

 

[beltmanjr]

Similar idea I just finished

numberVar dayHours:=0;
numberVar monHours:=0;
numberVar hours:=0;
numberVar totalHours:=0;

stringVar Array weirdArray:=''; // this being your field with weird dates

//stringVar weirdDate:='1 day 00:00:00';
//stringVar weirdDate:='10 day 00:00:00';
//stringVar weirdDate:='1 mon 3 day 00:00:00';
stringVar weirdDate:='1 mon 30 day 10:43:40';
//stringVar weirdDate:='12 mon 1 day 01:00:00';
//stringVar weirdDate:='12 mon 30 day 00:00:00';

weirdArray := split(weirdDate, " ");

Select weirdArray[2]

    Case "mon" :
    (
        monHours := ToNumber(weirdArray[1])*30*24;
        if(weirdArray[4]='day') then
            dayHours := ToNumber(weirdArray[3]) * 24;
        "monHours"
    )
    Case "day" :
    (
        dayHours := ToNumber(weirdArray[1]) * 24;
        "dayHours"
    )
    Default :
        "hours";

hours:= ToNumber(left(weirdArray[count(weirdArray)],2));
totalHours := monHours + dayHours + hours;
toText(totalHours,0) & right(weirdArray[count(weirdArray)],6);

 

[beltmanjr]

You can obviously delete all test data

 

[beltmanjr]

OK, small error in the comments fields

stringVar Array weirdArray:=''; // this being your field with weird dates

this is wrong. It is the weirdDate variable that should be replaced by your table field.

 

[lbass]

Sorry. I forgot to set the calcs to be zero at the beginning of the formula. Should start:

stringvar array x := split({@string},":");
stringvar array y := split(x[1]," ");
numbervar calc1 := 0;
numbervar calc2 := 0;
numbervar calc3 := 0;

-LB

 

[beltmanjr]

Funny, how 2 different solutions would work just fine.
Though the only real difference is the use sequence of IF & SELECT CASE and the use of UBound vs Count.

Curious if it resolved the issue