I have three fields - Start_Time__c(Date/Time), End_Time__c(Date/Time), Time_Spent__c(Formula(Number)). The field Time_Spent__c calculates time difference of Start_Time__c and End_Time__c in hours (Only 5 business days excluding Saturday and Sunday ie., 24*5). Below is the formula:
ROUND(24*( (5*FLOOR((DATEVALUE(End_Time__c)-DATE(1996,1,1))/7)+ MIN(5, MOD(DATEVALUE(End_Time__c)-DATE(1996,1,1),7)+ MIN(1,MOD(End_Time__c-DATETIMEVALUE(St__c),1)) ) )-(5*FLOOR((DATEVALUE(Start_Time__c)-DATE(1996,1,1))/7)+ MIN(5, MOD(DATEVALUE(Start_Time__c)-DATE(1996,1,1),7)+ MIN(1,MOD(Start_Time__c-DATETIMEVALUE(St__c),1)) ) ) ), 0)
It is working fine for almost 90% records, but for the rest the value is negative.
For example, Start Time=20/2/2019 1:00 PM, End Time=20/2/2019 6:30 PM. Correct value of Time Spent should be 5 Hours 30 minutes or 6 Hours (After round off), but it is actually showing -18. It is only happening when both Start Time and End Time is in PM.
I think it has something to do with local time zone as Start Time value on querying in developer console shows 2019-02-20T07:30:00.000+0000 instead of 2019-02-20T13:00:00.000+0000. Same goes for End Time or any other Date/Time field.