Weekly Totaling of Time.

web

Hi Experts,

I have created a report that is taking the total times worked by each employee within a filter date and is summing up the total times for all the employees at the bottom.

Now i wanted it to total for each day up to sunday for each employee.

Something like in cloumn form.

Employee No. Employee name TotTime Mon. TotTime Tue. and so on to TotTime Sun.

Also the total time is giving something like 70.83 which is not logical in time. The .83 should not exit .60. Total time is a decimal datatype. I tried changing it to time so that the minutes time should not exceed 60min but its gives an error which runs like this:

The defination of the Total time was changed to
Old type:Decimal
NewType: Time.

When i complied it it still persist.

Also i have error on the formula that is subtracting the time in from the time out so that i can get the total time work for each day.
The error reads " time conversion error Decimal:=Time"
See my formula below which i placed in the time sheet table:

Time In - OnValidate()
IF "Time Out" <>0T THEN
"Total Time" :=("Time Out" - "Time In")/3600000;

Time In - OnLookup()

Time Out - OnValidate()
"Total Time" :=("Time Out" - "Time In")/3600000;

Any idea please.

Shedman

Try using DataType Duration for "Total Time" to show the time between the "Time In" and "Time Out". That'll probably solve your rounding problem as well.

web

If i change the datatype of "Total Time" to duration and trying compiling i have this error
"Type conversion is not possible because one of the operetors contains an invalid type" which shows Time:=decimal or Time:=Integer
This error will point to the below formula

"Total Time" :=("Time Out" - "Time In")/3600000;

Any ideas why?

web

When i change the "Total Time" From decimal to Duration i gives an error when you are keying in the time in and time out like this:

"Reference to the member Total time of the variable could not be solved"
Its seems subtracting two time variables could only give a results of a decimal or interge type which will not be in line with the time rule. Will end up having more than 60mins in the minutes parts.
Any ideas please.

web

I am still having problem with the formula which is subtracting two times.
Changing datatype seems not to be working for me.
Any help Please?

Shedman

To calculate the total time as duration try:

"Total Time" := "Time Out" - "Time In"

Then you get the duration you need.

P.S. Patience is a virtue ...

web

I still have an error after changing the datatype of "Total time" to duration which says

" Type conversion is not possible because 1 of the operators contains an invalid type i.e Time := integer

I used your formula which runs thus:

"Total Time" := "Time Out" - "Time In"

Shedman

Are "Time Out" and "Time In" both Time-variables?

web

Are "Time Out" and "Time In" both Time-variables?

Both "Time Out" and "Time In" have datatype called time.

CobaltSS

Doesn't Duration require a DateTime variable type?

cheers,

web

Duration is also a Data type. The Field Called "Total time" is presently Decimal which enables me to have difference between to times. But the results is in Decimal. I wanted the difference to be in time not decimal.

I have changed the data type of "Total time" to Time and Duration but its not allowing the subtractions of two times to take place. Its gives me the Errors i mentioned in my previous replies.

Right now the Subtraction could only take place when the "Total time" data type is decimal or integer and i wanted my results to be in time so that it can make sense.

Any idea please?

CobaltSS

Hi,

I do this:

  IF "WDH Posted Service Header"."Created Date-Time" = CREATEDATETIME(0D, 0T) THEN
    CurrReport.SKIP;

  PostingDateTime := CREATEDATETIME("WDH Posted Service Header"."Posting Date", 120000T);
  IF PostingDateTime < "WDH Posted Service Header"."Created Date-Time" THEN
    PostingDateTime := CREATEDATETIME("WDH Posted Service Header"."Posting Date", 170000T);
  TAT := ROUND(PostingDateTime - "WDH Posted Service Header"."Created Date-Time",1000);
  TotalDuration := TotalDuration + TAT;
  CountInv := CountInv + 1;
  IF CountInv <> 0 THEN BEGIN
    AvgDuration := ROUND(TotalDuration/CountInv,1);
    AvgDuration := ROUND(AvgDuration,1000000);
  END;


Name	DataType	Subtype	Length
TAT	Duration		
PostingDateTime	DateTime
TotalDuration	Duration		
AvgDuration	Duration

And "Created Date-Time" is also a DateTime variable.

TAT as an output gives x Days, x Minutes, x seconds, x milliseconds. You can use the ROUND function to trim the output to your needs.

cheers,

web

I have seen your `code. But how can i let the code below perform the subtraction without error:

Time In - OnValidate()
IF "Time Out" <>0T THEN
"Total Time" :=("Time Out" - "Time In")/3600000;

Time In - OnLookup()

Time Out - OnValidate()
"Total Time" :=("Time Out" - "Time In")/3600000; 

Name            DataType   Subtype   Length 
"Time In"         Time                    
"Time Out"        Time
Total Time        Duration/Time

CobaltSS

I would change your TIME variables to type DATETIME. You can use something like:

newDateTimeIn := CREATEDATETIME(WORKDATE, "Time In");
newDateTimeOut := CREATEDATETIME(WORKDATE, "Time Out");

"Total Time" := newDateTimeOut - newDateTimeIn;

You'll need to check if Time Out is "before" Time In, then you'll need to change the date part of the Createddatetime function to one day later.

"Total Time" is a DURATION
newDateTimeIn and newDateTimeOut are DATETIME variables.

web

Thanks. But i have one more problem. How can i round the time to hours and minutes only. I am having total time as 733462 days 17 hours 1 millisecond. how can i have it as let say 17hours 30minutes?

CobaltSS

You'll need to play with the ROUND function. Like this:
ROUND("Total Time",1000000); try changing by factors of 1000 (i.e. 1, 1000, 1000000, etc.).

I haven't actually got it to work the way I want to either, but the users are OK with it, so I stopped....

web

I have played arround with you suggestion but still the same output. I just can't get rid of the day part of it. How can i get rid of the day part and leave hours and minutes.

"Time In":=09:00:00 "Time Out" := 19:00:00 and "Total Time := 729166 days 16 hours

Is the above Correct?
This is what your suggestion is giving me.
Any idea.

CobaltSS

newDateTimeIn := CREATEDATETIME(WORKDATE, "Time In"); 
newDateTimeOut := CREATEDATETIME(WORKDATE, "Time Out"); 

"Total Time" := newDateTimeOut - newDateTimeIn; 

If you're using WORKDATE in the CREATEDATETIME function as I've outlined above, then Total Time should not have any days in it's value. If you've coded it like that, them I am as confused as you.

Are you sure "Time Out" > "Time In"? If it's not, then you'll need to increase Workdate by 1 in your CREATEDATETIME statement (code is untested):

newDateTimeIn := CREATEDATETIME(WORKDATE, "Time In"); 
IF "Time Out" < "Time In" THEN
  newDateTimeOut := CREATEDATETIME(CALCDATE('1D',WORKDATE), "Time Out")
ELSE
  newDateTimeOut := CREATEDATETIME(WORKDATE, "Time Out");


"Total Time" := newDateTimeOut - newDateTimeIn; 

web

Thanks very much its very close now. The only thing left is summing the total time in hours. Its giving me something like 1day 1hour as total. I want something like 25hours.
Any other idea?

CobaltSS

Check out the last post in this thread:
http://www.mibuso.com/forum/viewtopic.php?t=15655

cheers,

web

I have checked the thread but is giving it in hh:mm:ss. I want it in hh.
How can i round it in hours only.
Thanks.

CobaltSS

I've never done it, and I couldn't find an example. But I'm sure either the ROUND or FORMAT function (or maybe both together in some way?) will answer your needs. If you get a chance to play with it and make it work, please post your solution.

Or maybe one of the other more experienced developers has a solution?

cheers,

web

i have played around with the ROUND function but no luck.

web

Hi can someone help me with this Thread please.
Thanks.

web

Can someone help me how to round total time in hours.? say 1day 1hour to 25hours.
Thanks.

Shedman

Duration represents the diffrence between two datetimes in milliseconds.

So if you divide the Duration by 1000*60*60 (milliseconds*seconds*minutes), you effectively get the number of hours.

web

This is the code i have in the "staff time sheet" table.

Time In - OnValidate()
newDateTimeIn := CREATEDATETIME(WORKDATE, "Time In");
IF newDateTimeOut <>CREATEDATETIME(0D, 0T) THEN
//"Total Time" := ROUND(newDateTimeOut - newDateTimeIn);
//"Total Time" := ROUND("Total Time",1000000000000);

//IF "Time Out" <>0T THEN
//"Total Time" :=("Time Out" - "Time In")/3600000;

newDateTimeIn := CREATEDATETIME(WORKDATE, "Time In");
IF "Time Out" < "Time In" THEN
  newDateTimeOut := CREATEDATETIME(CALCDATE('1D',WORKDATE), "Time Out")
ELSE
  newDateTimeOut := CREATEDATETIME(WORKDATE, "Time Out");


"Total Time" := newDateTimeOut - newDateTimeIn;

Time In - OnLookup()

Time Out - OnValidate()
newDateTimeOut := CREATEDATETIME(WORKDATE, "Time Out");
//"Total Time" := ROUND(newDateTimeOut - newDateTimeIn);
//"Total Time" := ROUND("Total Time",1000000000000);

//"Total Time" :=("Time Out" - "Time In")/3600000;

newDateTimeIn := CREATEDATETIME(WORKDATE, "Time In");
IF "Time Out" < "Time In" THEN
  newDateTimeOut := CREATEDATETIME(CALCDATE('1D',WORKDATE), "Time Out")
ELSE
  newDateTimeOut := CREATEDATETIME(WORKDATE, "Time Out");


"Total Time" := newDateTimeOut - newDateTimeIn;

Time Out - OnLookup()

Total Time - OnValidate()

Total Time - OnLookup()

I also wrote this on my Report:

TotalTime += "Total Time";

I do have the "Total Time" in hours, but when i some the in the report as shown above i get 1 day 9hours when actually i what it to give me the sum in hours say 33 hours. i have tried things like
TotalTime += "Total Time"*24; it gave me 33 days instead of hours
TotalTime += "Total Time"/24; this give me 1hour 22minutes
TotalTime += "Total Time"/(1000*60*60); this give me 33miliseconds.
I still did not have what i want.
Any suggestions Please?
Thanks

Savatage

just jumping in at the end & not reading the whole thing. :oops:

Have you tried using the datatype of TIME instead of DATETIME?

or is it possible to convert the datetime into a time???

2 days = 48 hours :-k

web

I have tried using datatype of Time for all, but i have problem of subtracting times.
I.e "Total Time" := "Time out" - "Time In"; Gives errors. Its not allowing the subtraction. The "Total time" has to be either decimal datatype or integer datatype.

This is why i took the suggestion of CobaltSS which is to use datatime, which is close to my solution, but having problems of converting to hours only.
Any idea?

web

I have tried all of the above suggestions but no luck yet.
Can somebody help me out with this Please.

Alex_Chow

Change the Total Time field to a decimal datatype.

If you still can't get it to work, ask a senior programmer at your company to help you. If you're an end user, ask your NAV partner to help you. It will save you a ton of frustration and time.