function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Madhura BMadhura B 

Subtracting DateTime fields and disaplying in hh:mm:ss

Hi,

 

I have to subtract Login time from Logout to determine how long the user was online.

For this I have a formula field which is working fine except for a small glitch.

 

The formula is as follows.

 

IF ( LogoutSubLogin__c > 0,
TEXT(FLOOR(LogoutSubLogin__c *24 ) )& " hours " &
TEXT(FLOOR(MOD(LogoutSubLogin__c *1440,60))) & " mins " &
TEXT(FLOOR(MOD(LogoutSubLogin__c *1440,1)*60) )& " secs"
, "Not Logged Out ")

 

LogoutSubLogin is a Formula number field which holds the difference between Logout and Login (both DateTIme fields)

 

The issue I am facing is; if

LoginTime = 20/02/2013 06:25

LogoutTime= 20/02/2013 06:49

LogoutSubLogin= 0.016516

Result= 0 hours 23 mins 47 secs

 

which should actually be 24 minutes. As I am using the Floor function to calculate minutes, 23.78304 is rounded to 23 which should actually be 24. However if I use CEILING then in another cases where the time might be

LoginTime =20/02/2013 05:47

LogoutTime=20/02/2013 05:52

LogoutSubLogin=0.003866

Result= 0 hours 6 mins 34 secs  as 5.56704 minutes would be rounded to 6 minutes. (CEILING)

 

Please can someone suggest me a better way to calculate the minutes such that both the cases mentiones above are satisfied.

 

 

Shannon HaleShannon Hale

I don't think your minutes calculation is wrong. If the start time was 06:25:59 and the end time was 06:49:46, the elapsed time would be 23 minutes 47 seconds. The formula I came up with is similar to yours (mine includes days because I used an existing formula and just appended the seconds):

 

IF( 
  LogoutSubLogin__c > 0 , 
  TEXT(FLOOR(LogoutSubLogin__c)) & " days " 
    & TEXT(FLOOR(MOD(LogoutSubLogin__c * 24, 24))) & " hrs " 
    & TEXT(FLOOR(MOD(LogoutSubLogin__c * 24 * 60, 60))) & " mns " 
    & TEXT(ROUND(MOD(LogoutSubLogin__c * 24 * 60 * 60, 60), 0)) &" secs", 
  "Not logged out"
)

 

The only thing that I would do differently in yours is ROUND() the seconds calculation, rather than FLOOR() it.