Date is Military time(string Var) need to convert and get difference

[TerriO]

I am using CR8

Have a field that is a string in military time (no seconds). I need to
get the difference for the timein and the timeout then get the difference from the last time out to the next timein.
I have looked through the posts and getting more confused. Please help if you can

In Out Duration Delay
3:00 3:15 .15 (none on 1st rec)
3:30 3:40 .10 .15 (last outtime to next InTime)




Terri

 

[lbass]

For {@duration} try:

(timevalue({table.out}) - timevalue({table.in}))/60

For {@delay} try:

If onfirstrecord or
{table.group} <> previous({table.group}) then 0 else
(timevalue({table.in}) - timevalue(previous({table.out})))/60

This last formula assumes you have a group field. If not, leave out the "or" clause.

-LB

 

[TerriO]

I am getting a Bad Format String Error when I run the report on the first formula


Terri

 

[synapsevampire]

Try posting what you tried, an example of the data, and the data type for the field (right click it and select browse data to get the data type and paste an example of the data).

LB assumed best case scenario as you didn't post any technical information.

-k

 

[TerriO]

The data is a string(4) 0500 = 5:00 AM for Time In and Out
I tried the suggestion above and was able to use the following formula (from tek tips)
Time(iif(length({SCHEDULE.TIMEIN})=4,
Val(Left({SCHEDULE.TIMEIN},2)),
Val(Left({SCHEDULE.TIMEIN},1))),
Val(Right({SCHEDULE.TIMEIN},2)),
00);
to get it to read the time on the report its the computing that I am having difficulty with



Terri

 

[lbass]

Try the following formulas:

//{@timeout}:
time(val(left({table.out},2)),val(mid({table.out},3,2)),00)

//{@timein}:
time(val(left({table.in},2)),val(mid({table.in}, 3,2)),00)

//{@duration}:
({@timeout} - {@timein})/60

//{@delay}:
If onfirstrecord or
{table.group} <> previous({table.group}) then 0 else
({@timein} - previous({@timeout}))/60

-LB

 

[TerriO]

formula for duration is returning zero for all..
({@TimeOUTT} - {@TimeINN})/60
Delay isnt working at all


Terri

 

[lbass]

My formulas assume that your strings appear as "0500", "0515", etc., for 05:00 AM, 05:15 AM, etc., and they all work when I test them here. Does the field ever appear with more than 4 numbers? I noticed you used iif() to allow for a length of 3 (I guess), but your examples show a leading zero, which suggests that the field always appears with 4 digits. To troubleshoot, try each formula in the report to make sure it is appearing correctly.

If you still can't identify the issue, then please provide a sample of the time fields that shows all the various ways it can appear, if other than a length of four.

-LB

 

[synapsevampire]

Let's create a worst case scenario defense

datediff("s",
cdatetime(1970,1,1,val(left({table.out},2)),val(mid({table.out,3,2)),0)
,
cdatetime(1970,1,1,val(left({table.in},2)),val(mid({table.in,3,2)),0)
)

This will net the difference in seconds.

Now use the following to obtain the display differences replacing the variable dur with the above:

Faq: faq767-3543

The following formula will return the difference between 2 dates in HH:MM:SS. It is easily modified to return the HH:MM:SS for a seconds field by replacing the dur value with your seconds field/formula.

numberVar dur := datediff("s",{Orders.Order Date}, currentdate); //get the seconds between 2 dates
numberVar hrs;
numberVar min;
numberVar sec;
stringVar hhmmss;

hrs := Truncate(Truncate(dur/60)/60);
min := Remainder(Truncate(dur/60),60);
sec := Remainder(dur,60);

hhmmss := totext(hrs, "0") + ":" + totext(min, "00") + ":" + totext(sec, "00");

hhmmss

You would use the previous as in LB's solution to handle the delay.

The problem with this solution is if it crosses midnight. Storing data with just times and NOT a date and crossing midnight is unlikely or a sign of a really bad design.

It also assumes that time means 24 hours, and not elapsed time.

-k

 

[synapsevampire]

I should have clarified that the Previous in LB's solution would also use formulas akin to mine.

-k

 

[caddy36]

Using Crystal 8.5

I'm trying to get a difference between my create and complete timestamp. The statement works fine except when my statement is > 24 Hours. I then get an error. Here is my statement. How should I change it to get the statement to calculate correctly?

time(DateDiff("h",{reservation_request.rr_create_stamp},{reservation_request.rr_completestamp})-(if datepart("n",{reservation_request.rr_create_stamp}) < datepart("n",{reservation_request.rr_completestamp}) then 0 else 1),DateDiff("n",{reservation_request.rr_create_stamp},{reservation_request.rr_completestamp}) mod 60,00)