BusinessObjects Board

Calculating the # of minutes between 2 datetime fields

building reports webintelligence
#1

I am trying to find the number of minutes from the time a patient enters a room until they leave. I found this formula through a google search that lead me to another forum here:

https://bobj-board.org/t/117814

=DaysBetween([Actual Start Date and Time];[Actual Stop Date and Time]) * 86400 + ( ToNumber(FormatDate([Actual Stop Date and Time] ;"HH")) * 3600 + ToNumber(Left(FormatDate([Actual Stop Date and Time] ;"mm:ss") ;2)) * 60 + ToNumber(FormatDate([Actual Stop Date and Time];"ss"))) - (ToNumber(FormatDate([Actual Start Date and Time];"HH")) * 3600 + ToNumber(Left(FormatDate([Actual Start Date and Time] ;"mm:ss") ;2)) * 60 + ToNumber(FormatDate([Actual Start Date and Time] ;"ss")))

I had to make a few adjustments so that it would be accepted… “;” instead of “,” “and” instead of “&”…

I have been trying to make similar adjustments to the other code that was in that thread, but am running into the wall.

=FormatNumber(Floor(<number of seconds>/86400) ,"0") &amp; " day(s) " &amp; 
FormatNumber(Floor(Mod(<number of seconds> ,86400)/3600) ,"00") &amp; ":" &amp; 
FormatNumber(Floor(Mod(Mod(<number of seconds> ,86400) ,3600)/60) ,"00") &amp; ":" &amp; 
FormatNumber(Mod(Mod(Mod(<number of seconds> ,86400) ,3600) ,60) ,"00")

I can write Crystal coding with my eyes closed, but I have had no real training with this system and have not been able to find anything so far that I can use to learn the coding language…

Can someone please help me translate the last code into something that WebIntelligence will accept?
Any help will be appreciated…
Shannon

cmpgeek :us: (BOB member since 2014-09-16)

#2

First of all, you don’t actually need all those nested Mod functions. Since each order of magnitude is an even multiple of the orders below it, Mod([Time];60) will always return the seconds.

I did some similar calculations in this post: Converrt HH:MM:SS into MM

Lugh (BOB member since 2009-07-16)