BusinessObjects Board

[BOBJ 6.x] Difference between Dates - New Twist

I have An Actual Start Date and Actual Finish Date that are both formatted as dates mm/dd/yyyy HH:mm. I initially wanted the difference (in hours) between the two dates which I found the solution for on this board. See below. However, the problem that I have discovered is if the Actual Finish Date is prior to the Actual Start Date (yes I know it shouldn’t be that way but it is).

I found the solution to find the number of seconds between the two days with a variable called SECONDS BETWEEN ASD AND AFD The formula is: =DaysBetween( ,) * 86400 +( ToNumber(FormatDate( ,“HH”)) * 3600 + ToNumber(Left(FormatDate( ,“mm:ss”) ,2)) * 60 + ToNumber(FormatDate( ,“ss”)))-( ToNumber(FormatDate( ,“HH”)) * 3600 + ToNumber(Left(FormatDate( ,“mm:ss”) ,2)) * 60 + ToNumber(FormatDate( ,“ss”)))

And then to calculate the hours between the two dates, I have a variable called HOURS BETWEEN ASD AND AFD. The formula is: =FormatNumber(Floor(Mod( ,86400)/3600) ,“00”)

Both formulas work correctly as long as the Actual Start Date is prior to the Actual Finish Date but I can’t seem to figure out how to make it work if the opposite holds true.

Suggestions?


Lisa Rose
Progress Energy
(919) 362-2717
lisa.rose@pgnmail.com


sage12 (BOB member since 2018-09-10)

Use an If Statement?

Pseudo code of:

=IF([AFD]>=[AFS];Normal formula logic; swap AFD and AFS around in the normal formula logic)