Examples of addition and subtraction of time intervals.What time will it be in X hours or minutes.The same thing applies when the time is changed from daylight saving to standard time, but the result will be 1 hour more. If a case was opened before the daylight saving began and it was closed after the daylight saving time, the formula result would be 1 hour less. It doesn’t support Daylight Saving Time changes.If you want to see elapsed time while the case is still open, you should create a new formula for Elapsed Time and replace ClosedDate with Now().This formula is only for time duration between Opened and Closed (not elapsed time).From the previous example (Date/Time Opened is 6:13 AM and Date/Time Closed is 12:05 PM), we should get the Duration to look like this 9 Days: 5 Hrs: 52 Mins. I used “((MOD(((ClosedDate – CreatedDate) – (FLOOR(ClosedDate – CreatedDate))), 1) * 24) – (FLOOR(((ClosedDate – CreatedDate) – (FLOOR(ClosedDate – CreatedDate))) * 24)))” to find the remainder after the hours and multiplied that by 60 to get minutes. Line 6 is to find Mins when Days is less than 0. I used “((MOD(((ClosedDate – CreatedDate) – (FLOOR(ClosedDate – CreatedDate))), (FLOOR(ClosedDate – CreatedDate))) * 24) – (FLOOR(((ClosedDate – CreatedDate) – (FLOOR(ClosedDate – CreatedDate))) * 24)))” to find the remainder after the hours and multiplied that by 60 to get minutes. Line 5 is to find Mins when Days is more than 0. I had to split the logic in two because if ClosedDate and CreatedDate are on the same day, that would return days in 0, which would cause an error in the formula when it tried to divide by 0. Line 3- 6 is for Mins: IF( FLOOR(ClosedDate – CreatedDate) > 0, TEXT(ROUND((((MOD(((ClosedDate – CreatedDate) – (FLOOR(ClosedDate – CreatedDate))), (FLOOR(ClosedDate – CreatedDate))) * 24) – (FLOOR(((ClosedDate – CreatedDate) – (FLOOR(ClosedDate – CreatedDate))) * 24))) * 60),0)) & ” Mins”, TEXT(ROUND((((MOD(((ClosedDate – CreatedDate) – (FLOOR(ClosedDate – CreatedDate))), 1) * 24) – (FLOOR(((ClosedDate – CreatedDate) – (FLOOR(ClosedDate – CreatedDate))) * 24))) * 60),0)) & ” Mins” Then I multiplied the remainder with 24 to get the number in hours.
I used the integer from Days “FLOOR(ClosedDate – CreatedDate)” and subtracted it from the total of “ClosedDate – CreatedDate” to get the remainder. Line 2 is for Hrs: TEXT(FLOOR(((ClosedDate – CreatedDate) – FLOOR(ClosedDate – CreatedDate)) * 24)) & ” Hrs: “ I used FLOOR to round the number to the nearest integer and I used it for the number in days. Line 1 is for Days: TEXT(FLOOR(ClosedDate – CreatedDate)) & ” Days: “ TEXT(ROUND((((MOD(((ClosedDate – CreatedDate) – (FLOOR(ClosedDate – CreatedDate))), 1) * 24) – (FLOOR(((ClosedDate – CreatedDate) – (FLOOR(ClosedDate – CreatedDate))) * 24))) * 60),0)) & ” Mins”) TEXT(ROUND((((MOD(((ClosedDate – CreatedDate) – (FLOOR(ClosedDate – CreatedDate))), (FLOOR(ClosedDate – CreatedDate))) * 24) – (FLOOR(((ClosedDate – CreatedDate) – (FLOOR(ClosedDate – CreatedDate))) * 24))) * 60),0)) & ” Mins”, TEXT(FLOOR(((ClosedDate – CreatedDate) – FLOOR(ClosedDate – CreatedDate)) * 24)) & ” Hrs: ” & TEXT(FLOOR(ClosedDate – CreatedDate)) & ” Days: ” & So how can we get a formula to express the duration in Days, Hours, and Minutes? First, we have to select “Text” for the Formula Return Type, then use the following formula: 1 A lot of people might mistranslate the numbers to mean 9 days and 24 hours, which could be interpreted as 10 days, but in fact 9.24 days is actually 9 days 5 hours and 52 minutes. For example, if Date/Time Opened is 6:13 AM and Date/Time Closed is 12:05 PM, the duration will be 9.24 days. When using a number formula with 2 decimal places to create a Duration field, Salesforce will give the result in numbers of days, including a fractional amount. It sounds like a quick and easy formula to subtract Date/Time Opened from Date/Time Closed, but the result might not satisfy many users. It would be nice to be able to see Case Duration on the Case page layout, so we can keep track of the time that the support rep has been working on the case. Get Time Duration in “Days: Hours: Minutes” Format! photo by zigazou76 on Flickr
0 kommentar(er)
