Apex Code Development You need to sign in to do that
Don't have an account?
thusker Formula help: workdays since a date/time--but with conditions?
Hoping someone can offer some help . . . we have a date/time field that tracks the "Last Status Change" for leads. When a Lead Status is updated, a trigger updates the date/time to "now" and then we have a formula field that calculates the number of days between now and that date/time of the last change. However, we only calculate that value for leads that have not been converted and where they don't have a Closed status. The current formula is below and it works great.
IF(AND(NOT( ISNULL( Date_Time_Last_Status_Change__c )),NOT(IsConverted),NOT(ISPICKVAL(Status,"7. Closed"))), (NOW()- Date_Time_Last_Status_Change__c ),NULL)
Now . . . here's the question. There is some talk about changing it to try and only show workdays since the change--to not calculate weekends. I have the following formula to show the workdays. BUT . . . how would I combine the conditions in the formula above and the formula below so I only get workdays since the date/time of change if the lead still meets the conditions of not being converted or closed???
CASE(MOD( DATEVALUE(Date_Time_Last_Status_Change__c) - DATE(1985,6,24),7),
0 , CASE( MOD( DATEVALUE(NOW()) - DATEVALUE(Date_Time_Last_Status_Change__c) ,7),1,2,2,3,3,4,4,5,5,5,6,5,1),
1 , CASE( MOD( DATEVALUE(NOW()) - DATEVALUE(Date_Time_Last_Status_Change__c) ,7),1,2,2,3,3,4,4,4,5,4,6,5,1),
2 , CASE( MOD( DATEVALUE(NOW()) - DATEVALUE(Date_Time_Last_Status_Change__c) ,7),1,2,2,3,3,3,4,3,5,4,6,5,1),
3 , CASE( MOD( DATEVALUE(NOW()) - DATEVALUE(Date_Time_Last_Status_Change__c) ,7),1,2,2,2,3,2,4,3,5,4,6,5,1),
4 , CASE( MOD( DATEVALUE(NOW()) - DATEVALUE(Date_Time_Last_Status_Change__c) ,7),1,1,2,1,3,2,4,3,5,4,6,5,1),
5 , CASE( MOD( DATEVALUE(NOW()) - DATEVALUE(Date_Time_Last_Status_Change__c),7),1,0,2,1,3,2,4,3,5,4,6,5,0),
6 , CASE( MOD( DATEVALUE(NOW()) - DATEVALUE(Date_Time_Last_Status_Change__c) ,7),1,1,2,2,3,3,4,4,5,5,6,5,0),
999)
+
(FLOOR(( DATEVALUE(NOW()) - DATEVALUE(Date_Time_Last_Status_Change__c) )/7)*5)
Now there are two ways of doing it, if your working day formula works fine then you can use it in place of
(NOW()- Date_Time_Last_Status_Change__c )
But syntax might give you a hard time, so you can create another formula to just have the working days and use that instead of putting whole calculation.
Well . . . I was able to make the change you suggested and I have the formula below which works . . . almost. No syntax errors, but the work days calculation seems to add a day or round up or something. If I update a record so the last status change was today . . . the claculation shows "1" even if I literally just changed the status (would think it should be "0") Shouldn't be "1" until tomorrow if it's a workday.
Below is the formula as it currently stands. Any thoughts as to why the claculation seems to be overstated by 1???
(CASE(MOD( DATEVALUE(Date_Time_Last_Status_Change__c) - DATE(1985,6,24),7),
0 , CASE( MOD( DATEVALUE(NOW()) - DATEVALUE(Date_Time_Last_Status_Change__c) ,7),1,2,2,3,3,4,4,5,5,5,6,5,1),
1 , CASE( MOD( DATEVALUE(NOW()) - DATEVALUE(Date_Time_Last_Status_Change__c) ,7),1,2,2,3,3,4,4,4,5,4,6,5,1),
2 , CASE( MOD( DATEVALUE(NOW()) - DATEVALUE(Date_Time_Last_Status_Change__c) ,7),1,2,2,3,3,3,4,3,5,4,6,5,1),
3 , CASE( MOD( DATEVALUE(NOW()) - DATEVALUE(Date_Time_Last_Status_Change__c) ,7),1,2,2,2,3,2,4,3,5,4,6,5,1),
4 , CASE( MOD( DATEVALUE(NOW()) - DATEVALUE(Date_Time_Last_Status_Change__c) ,7),1,1,2,1,3,2,4,3,5,4,6,5,1),
5 , CASE( MOD( DATEVALUE(NOW()) - DATEVALUE(Date_Time_Last_Status_Change__c),7),1,0,2,1,3,2,4,3,5,4,6,5,0),
6 , CASE( MOD( DATEVALUE(NOW()) - DATEVALUE(Date_Time_Last_Status_Change__c) ,7),1,1,2,2,3,3,4,4,5,5,6,5,0),
999)
+
(FLOOR(( DATEVALUE(NOW()) - DATEVALUE(Date_Time_Last_Status_Change__c) )/7)*5))
,NULL)