Tuesday, November 9, 2010

Number of business days between two dates

Well it's been a few months since I've posted, mainly down to a change of role - I cut down on my remote working (day after day in the 2nd bedroom was getting a bit tedious) and ended up working in Sydney for a finance company, who are doing a lot of cool force.com stuff. Has been great getting back into the full-scale dev work, and I've had a chance to work with some technologies that I hadn't really used in any great depth - one being Hudson which is ultra-cool when combined with the force.com migration tool. This has therefore inspired me to get back into blogging and given me some nice starter topics... More on those in the coming weeks.

Thought I'd start off with a pretty simple post though, to make sure I remember how ;)

A lot of the stuff I've been doing is around transaction clearing, where SLA's are important based on the number of business days to settle a transaction. Cases obviously have the concept of 'age' but this is the only place that I could find in salesforce.com where this is automatically handled, based on the current date vs the date opened. But I needed to calculate this on a custom object, and not always for the current date (ie. some times between 2 fixed dates).

So I came up with the following formula field to determine the number of business days. It's pretty straight forward, and I'm mostly writing this to document it for my own use in the future. But it might come in handy for you also.

First I need to ensure the two dates I'm comparing do in fact fall on a business day (M-F). So there's a simple MOD call that will determine this.

Field: Process_Date_Working_Date__c
Formula: CASE( MOD(Process_Date__c - DATE(2009,1,4),7), 0, Process_Date__c+1,1, Process_Date__c, 2, Process_Date__c, 3, Process_Date__c, 4, Process_Date__c, 5, Process_Date__c, 6, Process_Date__c+2,Process_Date__c ) 

Field: Current_Working_Date__c
Formula: CASE( MOD(TODAY() - DATE(2010,7,18),7),0,TODAY()-2,1, TODAY(), 2, TODAY(), 3, TODAY(), 4, TODAY(), 5, TODAY(), 6, TODAY()-1, TODAY() )

You can see I can play around with dealing with Sat or Sun - either default them back to the previous week or forward to the start of the next week. The current working date I push out to start of the next week; the process date (the earlier of the 2 dates) I also push to the start of the week after the Sat/Sun.

Then my actual business days formula (a number) is the following:

Field: Days_Unallocated__c
Formula: IF (ISPICKVAL( Status__c , "Allocated"), 0 ,
IF ( Current_Working_Date__c - Process_Date_Working_Date__c <= 4,
Current_Working_Date__c - Process_Date_Working_Date__c,
((Current_Working_Date__c - MOD(Current_Working_Date__c - DATE(2009,1,5),7)) -
(Process_Date_Working_Date__c - (MOD(Process_Date_Working_Date__c - DATE(2009,1,5),7))) ) /7
+ MOD(Current_Working_Date__c - DATE(2009,1,5),7)
- MOD(Process_Date_Working_Date__c - DATE(2009,1,5),7)

This formula is a little more tricky; there are 3 scenarios:
  1. If the status is Allocated, it's 0
  2. If the difference between the 2 dates is up to 4 days, it means they can be calculated as a simple difference
  3. If the difference is >4, then the calculation is harder - I have to calculate the number of weeks (* 5 for 5 working days per week) between the 2 dates, and then deal with where in each week the 2 dates are, and either add to the start of the week or subtract from the end of the week.
It's not perfect - for a start it obviously doesn't handle holidays or anomalies, but it provides a good enough indication for the user as to how many working days the transaction is unallocated for.

Let me know if it helps you, or if you have any similar formulas.