 ShowAll Questionssorted byDate Posted RahulSFDCTSH

# Exclude Weekends from formula to Countdown Remaining Days

I currently have a fomula field (Returns remaining days as Text ) that displays Remnaing days (Starts from 5  and keep counting down each passing day when a new Case is created). Here is the formula I used:

IF(
TODAY() > ( DATEVALUE(CreatedDate)  + 5),
TEXT(-1*(TODAY()- ( DATEVALUE(CreatedDate) + 5 ))) ,
TEXT(( DATEVALUE(CreatedDate)+ 5 ) - TODAY() )
)
Currently this formula considers weekends days as well. I would like to modify the formula to exclude weekend days from TODAY and CreatedDate both.
For example: If a Case is created on Oct 11, 2017 (Wed) ,Remaining Days value on Oct 23, 2017 (Mon) should be -3 not -7. Best Answer chosen by RahulSFDCTSH RahulSFDCTSH
Hi Shamsi,

Thanks for your reply. Solution mentioned by you will calculate number of days or Business Days between dates but my requirement was bit tricky in a way that i needed to countdown from 5 excluding weekends for two dates, however i got it working with the below formula.

TEXT(5-((5 * ( FLOOR( ( TODAY() - DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD( TODAY() - DATE( 1900, 1, 8), 7 ) ) )
-
(5 * ( FLOOR( ( DATEVALUE(CreatedDate) - DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD( DATEVALUE(CreatedDate) - DATE( 1900, 1, 8), 7 ) ) ))) Shamsi 110

CASE(MOD( StartDate__c - DATE(1985,6,24),7),
0 , CASE( MOD( EndDate__c - StartDate__c ,7),1,2,2,3,3,4,4,5,5,5,6,5,1),
1 , CASE( MOD( EndDate__c - StartDate__c ,7),1,2,2,3,3,4,4,4,5,4,6,5,1),
2 , CASE( MOD( EndDate__c - StartDate__c ,7),1,2,2,3,3,3,4,3,5,4,6,5,1),
3 , CASE( MOD( EndDate__c - StartDate__c ,7),1,2,2,2,3,2,4,3,5,4,6,5,1),
4 , CASE( MOD( EndDate__c - StartDate__c ,7),1,1,2,1,3,2,4,3,5,4,6,5,1),
5 , CASE( MOD( EndDate__c - StartDate__c ,7),1,0,2,1,3,2,4,3,5,4,6,5,0),
6 , CASE( MOD( EndDate__c - StartDate__c ,7),1,1,2,2,3,3,4,4,5,5,6,5,0),
999)
+
(FLOOR(( EndDate__c - StartDate__c )/7)*5)

Mark my answer If it helps you.

Thanks,
Shamsi RahulSFDCTSH
Hi Shamsi,

Thanks for your reply. Solution mentioned by you will calculate number of days or Business Days between dates but my requirement was bit tricky in a way that i needed to countdown from 5 excluding weekends for two dates, however i got it working with the below formula.

TEXT(5-((5 * ( FLOOR( ( TODAY() - DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD( TODAY() - DATE( 1900, 1, 8), 7 ) ) )
-
(5 * ( FLOOR( ( DATEVALUE(CreatedDate) - DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD( DATEVALUE(CreatedDate) - DATE( 1900, 1, 8), 7 ) ) )))

This was selected as the best answer