Apex Code Development You need to sign in to do that
Don't have an account?
SamuelRobert Hours between two date time formula showing negative values
I have written a formula to calculate the difference between two Date/Time fields,
Please find the formula
Here I have a case whose created date is 12/12/2016 2:16AM and Firstresponse date is 12/12/2016 10:54 AM now the case response hour formula field shows me the difference between the above as -15.37 .
I tried to calculate the difference between two date Time for 24 hours not for a business hours, I only want to exclude weekends,
The hour calculation should be from 12:00 AM today to 11:59 PM the next day.
Please advise
Please find the formula
ROUND(24*(
(5*FLOOR((DATEVALUE(First_Response__c)-DATE(1996,01,01))/7) +
MIN(5,
MOD(DATEVALUE(First_Response__c)-DATE(1996,01,01), 7) +
MIN(1, 24/24*(MOD(First_Response__c-DATETIMEVALUE('1996-01-01 12:00:00'), 1)))
))
-
(5*FLOOR((DATEVALUE(CreatedDate)-DATE(1996,01,01))/7) +
MIN(5,
MOD(DATEVALUE(CreatedDate)-DATE(1996,01,01), 7) +
MIN(1, 24/24*(MOD(CreatedDate-DATETIMEVALUE('1996-01-01 12:00:00'), 1)))
))
), 2)
Here I have a case whose created date is 12/12/2016 2:16AM and Firstresponse date is 12/12/2016 10:54 AM now the case response hour formula field shows me the difference between the above as -15.37 .
I tried to calculate the difference between two date Time for 24 hours not for a business hours, I only want to exclude weekends,
The hour calculation should be from 12:00 AM today to 11:59 PM the next day.
Please advise
There is an error in the Salesforce documentation (1996 instead of 1900) but your formula seems OK and doesn't use the error of the documentation but I don't use the same reference hour.
Finding the Number of Business Hours Between Two Date/Times
https://developer.salesforce.com/docs/atlas.en-us.usefulFormulaFields.meta/usefulFormulaFields/formula_examples_dates.htm (https://developer.salesforce.com/docs/atlas.en-us.usefulFormulaFields.meta/usefulFormulaFields/formula_examples_dates.htm )
Here is my own formula: date_time1 - date_time2
ROUND(24*( (5*FLOOR((DATEVALUE(date_time1__c)-DATE(1900,01,01))/7) + MIN(5, MOD(DATEVALUE(date_time1__c)-DATE(1900,01,01), 7) + MIN(1, (MOD(date_time1__c-DATETIMEVALUE('1900-01-01 00:00:00'), 1))) )) - (5*FLOOR((DATEVALUE(date_time2__c)-DATE(1900,01,01))/7) + MIN(5, MOD(DATEVALUE(date_time2__c)-DATE(1900,01,01), 7) + MIN(1, (MOD(date_time2__c-DATETIMEVALUE('1900-01-01 00:00:00'), 1))) )) ), 2)ROUND(24*(
(5 * FLOOR((DATEVALUE(First_Response__c)-DATE(1900,01,01))/7) + => 5 x (number of days / 7) = 5 x days per complete week between the first date and the reference.
MIN(5, => no more than extra 5 more days (incomplete week)
MOD(DATEVALUE(First_Response__c)-DATE(1900,01,01), 7) + => the remainder of number of days = the incomplete week between the first date and the reference in days
MIN(1, (MOD(First_Response__c-DATETIMEVALUE('1900-01-01 00:00:00'), 1))) => the remainder of hours, MOD 1 so it is the decimal part of the substraction (figures after the period) => MIN
))
-
(5*FLOOR((DATEVALUE(CreatedDate)-DATE(1900,01,01))/7) +
MIN(5,
MOD(DATEVALUE(CreatedDate)-DATE(1900,01,01), 7) +
MIN(1, (MOD(CreatedDate-DATETIMEVALUE('1900-01-01 00:00:00'), 1)))
))
), 2)
Regards
Alain
Thank You for your response.
I tried the modification you have done.Now I still have the negative values. One of the scenario is the below Image.
I doubt is it because of the time is differentiated by AM and PM , as not mentioned as 00:00 to 23:59
Please advise
French format (24h = 0-12 (AM) 13-23 (PM)) and comma instead of a period.
Your formula should work yet (changing the hour of reference possibly).
You have a problem elsewhere. It is not an inversion of date because only the sign should change.
Are you sure that the hours shown are the dates used by the formula?
CreatedDate is not the Date/Time Opened probably.
Alain
I Did checked it the created date is the Data/Time Opened.
Additionally I found one , that when I MIN(1,(MOD(CreatedDate-DATETIMEVALUE('1900-01-01 00:00:00'), 1))) it shows me wrong calculation of Case response hours for date/time with PM and If I use.
MIN(1,(MOD(CreatedDate-DATETIMEVALUE('1900-01-01 12:00:00'), 1))) it shows me wrong Case response hour calculation for date/time with AM
Thank you.
France :
United States :
That works fine.
Alain
https://developer.salesforce.com/signup
Alain
I have tried and this is the scenario I finally getting now.
If in the the formula I use 00:00:00 then
Date_time1(First_response_Date) 12/12/2016 8:17 PM -
Date_time2(Created Date) 12/12/2016 1:16 PM = - 17.47 hours.
If In formula I use 12:00:00 then
Date_time1(First_response_Date) 12/12/2016 8:17 AM -
Date_time2(Created Date) 12/12/2016 1:16 AM = - 17.47 hours.
Apparently, I recognize when It is 00:00:00 the dates with PM showing - ve hours and if I use 12:00:00 in formulas then dates with AM are showing -ve values.
What is your time zone?
diff_date = date_time1 - date_times2
Formula:
ROUND(24*( (5*FLOOR((DATEVALUE(date_time1__c)-DATE(1900,01,01))/7) + MIN(5, MOD(DATEVALUE(date_time1__c)-DATE(1900,01,01), 7) + MIN(1, (MOD(date_time1__c-DATETIMEVALUE('1900-01-01 00:00:00'), 1))) )) - (5*FLOOR((DATEVALUE(date_time2__c)-DATE(1900,01,01))/7) + MIN(5, MOD(DATEVALUE(date_time2__c)-DATE(1900,01,01), 7) + MIN(1, (MOD(date_time2__c-DATETIMEVALUE('1900-01-01 00:00:00'), 1))) )) ), 2)Alain
None of the two formulas is correct.
diff_date2 = Oleksiy
http://salesforce.stackexchange.com/questions/152539/hours-between-two-date-time-formula-showing-negative-values/152598#152598
It is interesting because the published solution of Salesforce is also bugged.
Alain