Time subtraction 2

[madsstiig]

I am trying to get the time diff from two fields. I tried{field1}-{field2} but the time difference it gives me is not the proper calculation (of course...)
can someone help me??? I'd greatly appreciate it.

example:

startime: endtime: difftime:
715pm 745pm 00:30:00

The time fields are:
{VWDEPSTAT.REQTIME}
{VWDEPSTAT.FINTIME}

When I use some of the solutions to similar requests I get an error that says 'A date is required here'.

Best regards Mads, Copenhagen - Denmark

 

[HowardHammerman]

You have to account for the fact that the time fields in your case are character fields. You will need to use variables as follows:

//first get the length of the fields to see if the hour is one digit or two digits.

numbervar lenst := len((VWDEPSTAT.REQTIME});
numbervar lenen := len({VWDEPSTAT.FINTIME});
//find out if it is am or pm

booleanvar ispmst := "pm" in (VWDEPSTAT.REQTIME};
booleanvar ispmen := "pm" in (VWDEPSTAT.FINTIME};

//now get the proper hours for start and end

numbervar hrst := IF lenst = 5 then
if ispmst then val({VWDEPSTAT.REQTIME}[1] + 12 else
val({VWDEPSTAT.REQTIME}[1];
numbervar hren := IF lenen = 5 then
if ispmen then val({VWDEPSTAT.ENDTIME}[1] + 12 else
val({VWDEPSTAT.ENDTIME}[1];
//now get the minutes
numbervar minst := if lenst = 5 then val((VWDEPSTAT.REQTIME}[2 to 3]) else val((VWDEPSTAT.REQTIME}[3 to 4]);
numbervar minend := if lenen = 5 then val( (VWDEPSTAT.FINTIME}[2 to 3]) else val((VWDEPSTAT.FINTIME}[3 to 4]);
//now get the number of min after midnight
numbervar totminstart := hrst*60+minst;
numbervar totminend := hren*60+minend;
//now subtract
numbervar totmin := totminend-totminstart;

All this assumes that both start and end are on the same day, if not you will have to add logic to account for that.

I have not checked the spelling or syntax of the above code.
I may have left out a comma or parenthesis.

best regards,

Howard

else if lenen = 5 then

numbervar sthr := IIF(ispm,

Howard Hammerman,
Crystal Training and Crystal Material
On-site classes and one-on-one coaching
Low-cost telephone/email support
FREE independent Crystal newsletter
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[lbass]

If your two fields are in fact of time datatype and the start and end times are always within the same date, then you can use the following:

datediff("s",datetime(currentdate,{table.starttime}),datetime(currentdate,{table.endtime}))

Then you can convert the seconds to the string format using faq767-3543.

-LB

 

[HowardHammerman]

Lori's solution would be great if the fields were of the time datatype. I assumed that the fields were strings.

Howard Hammerman,
Crystal Training and Crystal Material
On-site classes and one-on-one coaching
Low-cost telephone/email support
FREE independent Crystal newsletter
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[madsstiig]

Hi again.

Hmmm ... when I try Lori's solution (I did because that was easiest ) then I get an 'A time is requried here'.
Does that suggest that it IS in fact a character field?

Regards...

 

[madsstiig]

Just to add...
In the Field Explorer it says that it's a Number value - does that mean character (one of the other fields - that I don't use - is a DateTime value...)

Best regards...

 

[lbass]

If it actually displays like 715pm it cannot be a number datatype. Place the fields on the report and run your mouse over them, and observe what the tooltip text says re: datatype.

-LB

 

[HowardHammerman]

Maads, what you have determined is that IN FACT you have a character field not a time field, not a number field. So proceed with the solution I suggested.

howard

Howard Hammerman,
Crystal Training and Crystal Material
On-site classes and one-on-one coaching
Low-cost telephone/email support
FREE independent Crystal newsletter
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[lbass]

If they are strings, you could convert each to an actual time by using a formula like this:

stringvar x := totext(val({table.stringtime}),"0000");
time(left(x,2)+":"+right(x,2)+right({table.stringtime},2))

Then you could use my earlier suggestion by substituting the formulas for the times in the datediff formula.

-LB

 

[HowardHammerman]

Lori, accoring to his example the character field holding tme is of the form:
h:mm AM
or
h:mm PM
or
h:mm AM
or
hh:mm PM

That is why I went through the trouble of finding the length of the string and then parsing out the various components and adding 12 where needed.

Often the data comes to us in strange ways.....

Howard Hammerman,
Crystal Training and Crystal Material
On-site classes and one-on-one coaching
Low-cost telephone/email support
FREE independent Crystal newsletter
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[lbass]

Howard,

The OP showed it in the following form for the actual fields:

###PM (no colon)

And my formula also accounted for the varying lengths.

P.S. My name is Linda.

-LB