0

# Subtract dates with timestamp in Webi report

Mar 24, 2017 at 10:13 AM

230

Former Member

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

AMIT KUMAR
Mar 24, 2017 at 10:22 AM
0

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.

Show 5 Share
Former Member

Hello Amit,

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.

Former Member

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.

Former Member

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.

Pavan Kumar Pullapanthula

days difference is 1 not 2.