I have a report which has workers time of arrival and time of departure in it and I need to add a variable which shows how many hours and minutes the worker was there for.
I have tried =- but I just get an error message - Incorrect data type. 
The fields are date/time in the format HH:mm
Can anyone help with this?
Sue 
(BOB member since 2003-07-31)
What do you get if you use Daysbetween(,) ? Do you get a fraction of a day, if so could you manipulate this into the desired HH:mm format?
Paul Shovlar (BOB member since 2002-09-05)
Same problem, I still get the error message Incorrect data type 
Sue (BOB member since 2003-07-31)
This post provides a good solution
Paul Shovlar (BOB member since 2002-09-05)
I am still struggling with this.
My understanding of this posting is that inorder to get how many hours there are between two times in BO I have to turn each of the times into text, separate the hours from the minutes and multiply the hours by 60 and then do the calculation. It all seems very odd , and I can’t get it to work anyway
Sue (BOB member since 2003-07-31)
Basically Yes 
If you have access to the Business Objects support site a similar solution is put forward in Resolution Entry 11374
Paul Shovlar (BOB member since 2002-09-05)
I don’t have access to this site.
Sue (BOB member since 2003-07-31)
Hello Blackburn, ah the wrong side of the Pennines, never mind 
You could try FAQ: Reporter but perhaps it might be better saying what problems you have when you split the time into its individual components as per the previous link that Paul posted. Try building it up a bit at a time…thats how I do it.
Nick Daniels (BOB member since 2002-08-15)
I think I’ve cracked it at last. Using the formula below I was able to get the number of hours between the two times:
=(((ToNumber(SubStr(<SBK_To> ,1 ,2))*60)+(ToNumber(SubStr(<SBK_To> ,4 ,2))))-((ToNumber(SubStr(<SBK_From> ,1 ,2))*60)+(ToNumber(SubStr(<SBK_From> ,4 ,2)))))/60
Thanks for your help. 
Sue (BOB member since 2003-07-31)
Sue,
Have a look at the report that I have sent.
If it works, I’ll post the code to this thread.
Regards,
Mark
Mark,
This works a treat once I take out the format date bit in the depart and arrivemins formulas
Thanks my report is working well now.
Sue (BOB member since 2003-07-31)
Example and code below.
Emplyoeeid Id Arrive Departmins ArriveMins Depart TimeDiff
bob 1 09:44 1023 584 17:03 07:19
dick 3 14:00 1196 840 19:56 05:56
harry 4 11:09 1029 669 17:09 06:00
tom 2 08:12 803 492 13:23 05:11
Employeeid and ID are dimension to identify staff
Arrive is arrival time on the same day as Depart, the departure time
Now the good bits:
Departmins
=ToNumber(SubStr(FormatDate(<Depart> ,"HH:mm") ,4 ,2))+((ToNumber(SubStr(FormatDate(<Depart> ,"HH:mm") ,1 ,2)))*60)
Arrivemins
=ToNumber(SubStr(FormatDate(<Arrive> ,"HH:mm") ,4 ,2))+((ToNumber(SubStr(FormatDate(<Arrive> ,"HH:mm") ,1 ,2)))*60)
TimeDiff
=FormatNumber( (Floor((<Departmins>-<ArriveMins>)/60)) ,"00")&":"&FormatNumber( (Mod((<Departmins>-<ArriveMins>) ,60)) ,"00")
CAVEAT: I provide no guarantee that this will work if Depart is not greater than AND ON THE SAME DAY AS Arrive.
Regards,
Mark
You could also use the datediff function if it is available. You can specify hours hour minutes.
datediff(minute,arrivaltime,departuretime) or
datediff(hour,arrivaltime,departuretime)
timberjack 
(BOB member since 2003-07-09)
Hi,
the solution already suggested has worked fine for me. It’s only drawdack is it is a bit long winded, but then again I don’t need to do this much so it’s not too bad.
I have never heard of the datediff function and it does not appear on the list of available functions. I am using 5.1.2 is this available only in later versions of BO or is there something missing from my current installation of BO???
Sue (BOB member since 2003-07-31)
As far as I know Sue, datediff is a SQL Server function. If you are using Oracle, it won’t help you.
Or take a look at these thread: Variance calculation and Can I create a Variable, and use a Function?.
In these threads Ken Gaul has written a DLL that you can add to BO, which will add a new BO Reporter function to calculate date/time differences.
Ken can and do you want to share your code with BOB?
Andreas 
(BOB member since 2002-06-20)