I am trying to create a variable in Desktop Intelligence XI that will give me the diff between two dates. I used “=DaysBetween(<Admit Date & Time> ,<Order Date & Time>)” and I get just days. Is there a way to get his broke down to hours, minutes and seconds?
Unfortunately no, DaysBetween() function gives the result only in days.
A possible workaround would be to convert both dates to the numbers of seconds from some date&time in past (for instance January 1 2008 00:00:00) then do the difference of these 2 numbers (to get the difference between date1 and date2 in seconds) and then convert the result back to hh:mi:ss for instance.
=FormatNumber(Floor(<number of seconds>/86400) ,"0") & " day(s) " &
FormatNumber(Floor(Mod(<number of seconds> ,86400)/3600) ,"00") & ":" &
FormatNumber(Floor(Mod(Mod(<number of seconds> ,86400) ,3600)/60) ,"00") & ":" &
FormatNumber(Mod(Mod(Mod(<number of seconds> ,86400) ,3600) ,60) ,"00")
I tried the variable and it did not work. I get the error not enough parameters.
I noticed my commands are Admit Date and time and Order Date and Time. So I changed it and I get another error. Incorrect Data type DMB0003. When I look at the variable it has the + highlighted. I will copy upto where it is erroring out.
=DaysBetween(<Admit Date & Time> ,<Order Date & Time>) * 86400
+
( ToNumber(FormatDate(<Order Date & Time> ,“HH”)) * 3600 +
ToNumber(Left(FormatDate(<Order Date & Time> ,“mm:ss”) ,2) * 60 + (here).
=DaysBetween(<Admit Date & Time> ,<Order Date & Time>) * 86400 + ( ToNumber(FormatDate(<Order Date & Time> ,"HH")) * 3600 + ToNumber(Left(FormatDate(<Order Date & Time> ,"mm:ss") ,2)) * 60 + ToNumber(FormatDate(<Order Date & Time> ,"ss"))) - (ToNumber(FormatDate(<Admit Date & Time> ,"HH")) * 3600 + ToNumber(Left(FormatDate(<Admit Date & Time> ,"mm:ss") ,2)) * 60 + ToNumber(FormatDate(<Admit Date & Time> ,"ss")))
It is in fact the same formula but written in 1 line instead of more. I used more lines above for the formula be more readable.
I hate to give good news and bad news in the same reply but… first thank you for your help.
It works great and all looks good. When I created the second variable i get an error message. "Not enough paramerters DMB0006 My curser starts flickering here
How about the same without carriage returns (no extra “enters”):
=FormatNumber(Floor(<number of seconds>/86400) ,"0") & " day(s) " & FormatNumber(Floor(Mod(<number of seconds> ,86400)/3600) ,"00") & ":" & FormatNumber(Floor(Mod(Mod(<number of seconds> ,86400) ,3600)/60) ,"00") & ":" & FormatNumber(Mod(Mod(Mod(<number of seconds> ,86400) ,3600) ,60) ,"00")
Did you give the first variable the name “number of seconds”? If you used any other name then you need to change the above formula accordingly.
This has been fantastic advice and I have managed to recreate it for my use as specified. I have one question that puts a slightly different slant on it in that I would like to display my elapsed time in hours, minutes and seconds. Therefore if it goes over 24 hours, I’d still like my results to show something like 27:16:23. I don’t really understand Mod so any pointers would be really appreciated.
This is a very interesting article, many thanks for posting it however, is there an easy way to add a time field to a date field to give a date time field?
I would like to thank you for submitting this and other related posts. Using them as a guide, I have been able to develop a solution that works i.e. determine whether contractors are meeting specified targets.