I have two dates i.e., User_IN_Date and User_OUT_Date with timestamp in webi report. I need to subtract the two dates and show result in minutes.
Suppose I have dates as -
User_IN_Date - 12/03/2017 03:53:23 and
User_OUT_Date - 14/03/2017 07:36:89
Could anyone please help me in calculating difference between the dates ?
try this.
Calculating difference between two date/time fields
=((((ToNumber(SubStr(FormatDate([Date2] ,"HH:MM:SS") ,1 ,2))*60*60) +
(ToNumber(SubStr(FormatDate([Date2] ,"HH:MM:SS"), 4, 2))* 60) +
ToNumber(SubStr(FormatDate([Date2] ,"HH:MM:SS"), 7, 2))) - ((ToNumber(SubStr(FormatDate([Date1] ,"HH:MM:SS") ,1 ,2))*60*60) +
(ToNumber(SubStr(FormatDate([Date1] ,"HH:MM:SS"), 4, 2))* 60) +
ToNumber(SubStr(FormatDate([Date1] ,"HH:MM:SS"), 7, 2)))) / 3600) + (DaysBetween([Date1];[Date2])*24)
You will get difference in seconds.After that you can convert seconds in mintues.
Hello Amit,
thanks for the reply !
Can you please explain with an example how this formula gives result ? When I try to apply this formula I get some strange result which is incorrect.
this formula is first converting hour/minutes in the seconds for subtraction.
you can try with break the formula in parts and see where is the issue.
check the values are correct in this?
=ToNumber(SubStr(FormatDate([Date2] ,"HH:MM:SS") ,1 ,2))
similar for other.might be substring function getting the different values.
or try this.
=((((ToNumber(SubStr(FormatDate([Date2] ,"HH:MM:SS") ,1 ,2))*60*60) +
(ToNumber(SubStr(FormatDate([Date2] ,"HH:MM:SS"), 4, 2))* 60) +
ToNumber(SubStr(FormatDate([Date2] ,"HH:MM:SS"), 7, 2))) - ((ToNumber(SubStr(FormatDate([Date1] ,"HH:MM:SS") ,1 ,2))*60*60) +
(ToNumber(SubStr(FormatDate([Date1] ,"HH:MM:SS"), 4, 2))* 60) +
ToNumber(SubStr(FormatDate([Date1] ,"HH:MM:SS"), 7, 2)))))+((DaysBetween([Date2];[Date1])*24)*60*60)
Above formula give you the total differne in seconds.
Hello Amit,
the formula worked. I had to do few modifications in the formula like using "hh:mm:ss" instead of "HH:MM:SS" and it worked perfectly fine.
thanks a lot !
Hi Amit Kumar,
For Example:
User_IN_Date ( [Date1] ) - 28/03/2017 18:53:23 ( 18*60=1080 + 53 = 1133 min)
User_OUT_Date ( [Date2] ) - 29/03/2017 05:36:89 ( 5*60=300 + 36 = 336 min )
Number of Days: 2 (2*24*60 = 2880)
So by using your formula: 336 - 1133 +2880 = 2083 min (34 hours)
In this scenario formula will not work properly.
Regards,
Pavan Kumar PSN.
days difference is 1 not 2.