Difference between 2 DateTime fields in Hours and Minutes with timezones

Question

I am having trouble in finding the difference between 2 datetime variables and reducing the offset timezone difference in order to get the local time from the salesforce generated GMT time. I have a requirement to find the difference between 2 datetime field in terms of hours and minutes. My time zone is EST and sometimes i was asked to calculate for CST as well, how can this be done?

Answer 1

Here is what i did, I created a formula field "DateNow__c". In this formula field keep this as datetime field. In order to get EST or CST time zones here you have to find out the time differences in terms of hours. For example, EST time difference would be 4 hours , so i simply made DateNow__c as "NOW()-0.1667" where 0.1667 = 4/24 Similarly for CST, DateNow__c as "NOW()-0.2083" where 0.2083 is 5/24. From this timings i get my local time zones value,

Why i did this is because When i used something like this "TEXT((NOW()-0.2083) - LastModifiedDate)" did not give me the desired output.

Step 2: Now to determine the diff in hours and minutes. Normally difference will give some decimal values like 5.52 where 5 is the no. of days , 0.5 in terms of hours and 0.02 in terms of minutes. The below formula will give me the difference in hours and minutes. For example, Date A is 9/22/2014 5:40 AM and Date B is 9/22/2014 8:00 AM My result will be : 2.20 where 2 is the hours and .20 is the minutes. For example, Date A is 9/21/2014 5:40 AM and Date B is 9/22/2014 8:00 AM My result will be : 26.20 where 26 is the hours and .20 is the minutes. (even day wil be converted to hours)

(FLOOR(( DateNow__c - SomeDatetime__c)*24)) 
+
ROUND((((
((DateNow__c - SomeDatetime__c)*24)-
FLOOR((DateNow__c - SomeDatetime__c)*24))
*60)/100),2)

here FLOOR(..) - will give me the hours. ROUND(..,2) will give me the minutes in 2 decimal places. Hope this will do good to you in some way.

Answer 2

Please have a look at moment.js which is a great production grade library for all date related operations. I have used it extensively in performing this operation. The only downside to this is that it can only be used on client side code, visual force pages etc.

Answer 3

First Solution

  First Calculate Minutes between two dates. Then convert hours and minutes . using formula field  
    

    Calculate Minutes Formula

              ( Second_Date__c - Frist_Date__C )*24*60


    Convert Minutes in Hours

                    TEXT(FLOOR( Duration__c / 60))
                    &' Hours '&
                    TEXT(MOD( Duration__c ,60))
                    &' Minutes'

Second Solution Use Formula Field And Use Return Type is Text

             IF(FLOOR(MOD((First_Date_Time_c- Second_Date_Time_c )* 24, 24 )) > 0,

             TEXT(FLOOR(MOD((First_Date_Time_c- Second_Date_Time_c )* 24, 24 ))) & " Hours ","")

            & TEXT(ROUND(MOD((First_Date_Time_c- Second_Date_Time_c )* 24 * 60, 60 ), 0))

            & " Minutes "