Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
ShowAll Questionssorted byDate Posted
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)

Shashikant Sharma

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.

thusker

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???

 IF(AND(NOT( ISNULL( Date_Time_Last_Status_Change__c )),NOT(IsConverted),NOT(ISPICKVAL(Status,"7. 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)) ,NULL)

﻿