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?
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.
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 "