Calculating Time from Two Time Columns 1

[fionama]

Hi,
I've got two columns in my Business Objects report, both containing time fields. One is 'Planned Start Time' and the other is 'Actual Start Time'. The 'Actual Start Time' field is taken from a template, so there is no actual date (just the default 30/12/1899).
I want to calculate, in a third column, the difference in minutes between the planned start time and the actual start time.
Is there a way to do this in Business Objects? I would be very grateful of any help.

 

[jobjoris]

This will calculate the time difference in seconds for two datetime columns:

[tt]= DaysBetween(<DateColumn1> ,<DateColumn2>)*24*60*60 +
(ToNumber(FormatDate(<DateColumn2> ,"h"))*24*60 +
ToNumber(FormatDate(<DateColumn2> ,"m"))*60 +
ToNumber(FormatDate(<DateColumn2> ,"s"))
) - (ToNumber(FormatDate(<DateColumn1> ,"h"))*24*60 +
ToNumber(FormatDate(<DateColumn1> ,"m"))*60 +
ToNumber(FormatDate(<DateColumn1> ,"s"))
)[/tt]

 

[skrandel]

good answer. BO doesn't do time math, so you have to do the conversions outlined in jobjoris' post.

Steve Krandel
VERITAS Software

 

[dbomrrsm]

Is daysbetween a function of BO or is it assuming an oracle back end ?

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[fionama]

Hi again, when I use this code to calculate between my two date,time fields :-
= DaysBetween(<Clinic Stop Time> ,<Clinic Start Time>)*24*60*60 +
(ToNumber(FormatDate(<Clinic Start Time> ,"h"))*24*60 +
ToNumber(FormatDate(<Clinic Start Time> ,"m"))*60 +
ToNumber(FormatDate(<Clinic Start Time> ,"s"))
) - (ToNumber(FormatDate(<Clinic Stop Time> ,"h"))*24*60 +
ToNumber(FormatDate(<Clinic Stop Time> ,"m"))*60 +
ToNumber(FormatDate(<Clinic Stop Time> ,"s"))
)

I am getting an answer 7200. If this is seconds, this is giving me 120 minutes, which is incorrect as the times are 09:30 and 16:30. Am I doing this correctly?!

 

[fionama]

Sorry, another thing I forgot to mention.....
when I am calculating the number of minutes difference between the clinic start time and the ACTUAL start time, the dates are completely different.
The clinic start time is taken from the template, so will have the default 30/12/1899, but the actual start time will have the date that the clinic occurred, i.e. in this case 3/3/05. Therefore, I would need the date to be excluded from the calculation, and just strictly the time to be used. For example,
Cinic Start Time 14:00
Actual Clinic Start Time 16:00
Time Difference 120 minutes

Thanks very much for all your help so far.