Showing posts with label task age. Show all posts
Showing posts with label task age. Show all posts

Sunday, September 11, 2011

Calculate Case or Task Age in Business Hours

This requirement which popped up a few weeks back - a field which can give the case age in business hours [excluding saturdays and sundays]. There was no function in salesforce which provided me the day given the date. I came about with a round about logical solution - a formula(number with 0 digits after decimal point) field which excludes saturdays and sundays when calculating the case age.

CASE(
MOD(DATEVALUE(CreatedDate) - DATE(1900, 1, 7), 7),
0, (TODAY() - DATEVALUE(CreatedDate)) - 1 - FLOOR((TODAY() - DATEVALUE(CreatedDate))/7)*2,
1, (TODAY() - DATEVALUE(CreatedDate)) - FLOOR((TODAY() - DATEVALUE(CreatedDate))/7)*2,
2, (TODAY() - DATEVALUE(CreatedDate)) - FLOOR((TODAY() - DATEVALUE(CreatedDate))/7)*2,
3, (TODAY() - DATEVALUE(CreatedDate)) - FLOOR((TODAY() - DATEVALUE(CreatedDate))/7)*2,
4, (TODAY() - DATEVALUE(CreatedDate)) - FLOOR((TODAY() - DATEVALUE(CreatedDate))/7)*2,
5, (TODAY() - DATEVALUE(CreatedDate)) - 2 - FLOOR((TODAY() - DATEVALUE(CreatedDate))/7)*2,
6, (TODAY() - DATEVALUE(CreatedDate)) - 2 - FLOOR((TODAY() - DATEVALUE(CreatedDate))/7)*2,
null)