Problem in Time Based Workflow triggering using custom formula field

Question

I have a formula field (Hours_After_Case_Updated__c) which calculates hours after the the case was last modified excluding weekends (Saturday and Sunday). I want to trigger a time based workflow which triggers if the formula field is greater than or equal to 24 hours and if the case is open. Workflow action : Email Alert

I tried using the following rule criteria:

AND( 
    OR (ISPICKVAL(Status, 'New'), ISPICKVAL(Status, 'In Progress')),
    Hours_After_Case_Updated__c >=24
)

And used Time Trigger as:

But workflow is not working.

Formula for calculating Hours_After_Case_Updated__c is as follows :

ROUND( 24 * (
( 5 * FLOOR( ( DATEVALUE( NOW() ) - DATE( 1900, 1, 8) ) / 7) +
MIN(5,
MOD( DATEVALUE( NOW() ) - DATE( 1900, 1, 8), 7) +
MIN( 1, 24 / 24 * ( MOD( NOW() - DATETIMEVALUE( '1900-01-08 7:00:00' ), 1 ) ) )
)
)
-
( 5 * FLOOR( ( DATEVALUE( LastModifiedDate ) - DATE( 1900, 1, 8) ) / 7) +
MIN( 5,
MOD( DATEVALUE( LastModifiedDate ) - DATE( 1996, 1, 1), 7 ) +
MIN( 1, 24 / 24 * ( MOD( LastModifiedDate - DATETIMEVALUE( '1900-0107:00:00' ),       
1) ) )
)
)
),
0 )

Answer 1

What are you doing to make your formula recalculate? I don't think it will just keep adding an hour onto the number every hour and then checking if it needs to prompt the workflow.
1. The formula will only be calculated when you view the record
2. The workflow will only fire when you save the record

So you could maybe run a batch that UPDATEs all the relevant records every hour, as this would kick the workflow.

But I think a better way is to remove Hours_After_Case_Updated__c from the criteria, and change your Timed Workflow so that it runs 24 hours after the LastModifiedDate. When the record is modified, a task will be queued for "24 hours from now". If the record is modified again before the 24 hours is up, the existing task goes away and a new one is queued for "24 hours from now". So there is always a task waiting to happen if the 24 hours do ever expire. There will only ever be one task waiting to happen for any particular Case. As soon as the status is changed so it is not 'New' or 'In Progress' the record does not meet the criteria any more, so the waiting task goes away.