Calculating Time Difftence on the same Day

system · January 30, 2007, 1:59am

Hey,

I am using the Web XI in creating reports. I have two objcets, one is login time ang other one is logout time on the same day. So how can you calculate the time diffenrece between these two objects in reporter in HH:mm:ss format ?
I am trying to calculate the calculate the the time difference between login and logout that customer using. Please suggest me to resolve this problem.

Thanks
RJ

rj_srnth (BOB member since 2006-12-09)

system · January 30, 2007, 3:50am

What database are you using and in what format the tow objects appear in the report.

KhoushikTTT (BOB member since 2005-02-24)

system · January 31, 2007, 12:06am

Hey,

We are using the ORACLE database andthe both the objects are in HH:mm:ss format.

Thanks
RJ

rj_srnth (BOB member since 2006-12-09)

system · January 31, 2007, 1:20am

Here you go.

login hr=(ToNumber(FormatDate([LogIn Time] ;"HH")))

login min=(ToNumber(Right(FormatDate([LogIn Time] ;"HHmm") ;2)))

login sec=(ToNumber(FormatDate([LogIn Time] ;"ss")))

logout hr=(ToNumber(FormatDate([LogOut Time] ;"HH")))

logout min=(ToNumber(Right(FormatDate([LogOut Time] ;"HHmm") ;2)))

logout sec=(ToNumber(FormatDate([LogOut Time] ;"ss")))

Hr Diff=[logout hr]-[login hr]

Min Diff=[logout min]-[login min]

Sec Diff=[logout sec]-[login sec]

Time Difference=[Hr Diff]+":"+[Min Diff]+":"+[Sec Diff]

Hope this helps.

KhoushikTTT (BOB member since 2005-02-24)

system · February 2, 2007, 10:02pm

Hey Neo,

Thanks for your help. But with this formula, we will get problem sometimes( If i am npt wrong with the following analysis).
If
Login time : 8:30:45
LogOut Time: 10:15:45

With your formula, we’ll get (10-8) hr: (30-15) Min:(45-45)Sec = 2 hr:15 min:00 sec.

But the actual diff is 1 hr:45 min:00 sec.

Please look at it.

Thanks
RJ

rj_srnth (BOB member since 2006-12-09)

system · February 3, 2007, 3:20am

hmmm yep!
I will work on it and update you.

KhoushikTTT (BOB member since 2005-02-24)

system · February 3, 2007, 6:38am

Here is what i came up with.

Sec Diff=If ([logout sec]>=[login sec];[logout sec]-[login sec];(60-([login sec]-[logout sec])))

Min Diff=If (([logout sec]>=[login sec] AND [logout min]>=[login min]);[logout min]-[login min];If (([logout sec]<[login sec] AND ([logout min]-1)>=[login min]);([logout min]-1)-[login min];If (([logout sec]<[login sec] AND ([logout min]-1)<[login min] AND ([logout Min]-1)>=0);(60-([login min]-([logout min]-1))); If (([logout sec]<[login sec] AND ([logout min]-1)<[login min] AND ([logout Min]-1)<0);((([logout min]+59)-[login min]));toNumber("Something Wrong")))))

Hr Diff=If (([logout sec]>=[login sec] AND [logout min]>=[login min] AND [logout hr]>=[login hr]);[logout hr]-[login hr];If (([logout sec]<[login sec] AND ([logout min]-1)>=[login min] AND [logout hr]>=[login hr]);([logout hr])-[login hr];If (([logout sec]<[login sec] AND ([logout min]-1)<[login min] AND ([logout hr]-1)>=[login hr]);([logout hr]-1)-[login hr];toNumber("Something Wrong"))))

You will have to note that, the above formula will work if the login and log out is done on the same day and time format follows military time (01hrs to 24 hrs)

KhoushikTTT (BOB member since 2005-02-24)

system · February 3, 2007, 7:57am

If the time format is in AM/PM format. Here is the solution. The above code is for military time format. But for both the solution the log-in and log-out should be on the same day.

login hr=If (Right([LogIn Time],2)="PM";(ToNumber(FormatDate([LogIn Time] ;"HH")))+12;(ToNumber(FormatDate([LogIn Time] ;"HH"))))

login min=(ToNumber(Right(FormatDate([LogIn Time] ;"HHmm") ;2)))

login sec=(ToNumber(FormatDate([LogIn Time] ;"ss")))

logout hr=If (Right([LogOut Time],2)="PM";(ToNumber(FormatDate([LogOut Time] ;"HH")))+12;(ToNumber(FormatDate([LogOut Time] ;"HH"))))

logout min=(ToNumber(Right(FormatDate([LogOut Time] ;"HHmm") ;2)))

logout sec=(ToNumber(FormatDate([LogOut Time] ;"ss")))

Sec Diff=If ([logout sec]>=[login sec];[logout sec]-[login sec];(60-([login sec]-[logout sec])))

Min Diff=If (([logout sec]>=[login sec] AND [logout min]>=[login min]);[logout min]-[login min];If (([logout sec]<[login sec] AND ([logout min]-1)>=[login min]);([logout min]-1)-[login min];If (([logout sec]<[login sec] AND ([logout min]-1)<[login min] AND ([logout Min]-1)>=0);(60-([login min]-([logout min]-1))); If (([logout sec]<[login sec] AND ([logout min]-1)<[login min] AND ([logout Min]-1)<0);((([logout min]+59)-[login min]));toNumber("Proved Me Wrong")))))

Hr Diff=If (([logout sec]>=[login sec] AND [logout min]>=[login min] AND [logout hr]>=[login hr]);[logout hr]-[login hr];If (([logout sec]<[login sec] AND ([logout min]-1)>=[login min] AND [logout hr]>=[login hr]);([logout hr])-[login hr];If (([logout sec]<[login sec] AND ([logout min]-1)<[login min] AND ([logout hr]-1)>=[login hr]);([logout hr]-1)-[login hr];toNumber("Proved Me Wrong"))))

KhoushikTTT (BOB member since 2005-02-24)

system · February 3, 2007, 7:59am

Ask your universe designer to create this as a measure object (in seconds) defined in the universe as:

SUM ( TableName.LogoutDateTime - TableName.LoginDateTime) )*24*60*60

This will take into consideration if a day passes between login and logout, from example: login at 23:55:00, logout the following day at 00:07:00

This is way easier than to create all those Webi formulas, is it not?

Andreas (BOB member since 2002-06-20)

system · February 4, 2007, 6:28am

Hey Neo,

Thanks a lot. Yes creating in universe in better than craeting formula in webi. I will try with your formulas and i will update u.

Thanks
RJ

rj_srnth (BOB member since 2006-12-09)

system · May 1, 2009, 7:07pm

Hi Andreas,

Your solution will work but it won’t give the information in HH:MM:SS format.

Can you please help me with this.

sam4u (BOB member since 2008-01-09)