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)
Good.
ReplyDeleteI was just wondering what will be case age if i have created a case on friday 7Pm and check the value of the formula field on Monday morning @8 AM
if the defined business hours are from Monday to friday 9AM to 6AM.
Will we able to include the odd business hours like Thursday to Tuesday 10AM to 9PM etc in a single formula without crossing the 5k compilation characters size limit on salesforce...
Food for thought :)
Siddhu!
ReplyDeleteThis formula will show 1 day to be the case age if a case was opened on friday 7 PM and if its value was checked on Monday morning 8 AM or any time on Monday.
If its just within the time limit 9 AM to 6 PM for all weekdays and exclude only weekends, the formula can be tweaked: by considering the created date to be the next weekday for cases created after business hours.
If salesforce business hours [which might include odd hours and exclusion of holidays] need to be considered - scheduled class will be an option.
Siddhu!
ReplyDeleteCheck out my new post for a solution considering odd business hours
http://salesforce-shruthi.blogspot.com/2011/09/calculate-case-age-considering.html
Shruthi,
ReplyDeleteThx for the Formula,got a question:
Even though the case is closed i guess this formula keeps continuing calculating the days , can this be implemented only for open cases ?
Venu
ReplyDeleteYes. You are right! You can use the below logic for case closure!
Replace today in the above formula with the closed date if the case is closed else use the above formula as such!
@shruthi...
ReplyDeleteYou mention that the formula could be 'tweaked' for 9am-6pm for weekdays. I can't seem to figure this out. don't suppose you could provide an example?
Want to avoid apex sloution if possible.
Thanks for your help
Formula is great but give me 2,500 characters...any way to get less than 2000?
ReplyDeleteI am creating a formula image field and getting
Error: Compiled formula is too big to execute (6,010 characters). Maximum size is 5,000 characters
@Oscar
ReplyDeleteCreate a formula field with case age first. And then create another formula field referencing the case age formula field to avoid this error.