Apex Code Development You need to sign in to do that
Don't have an account?
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.
Can someone please help?
Thanks in advance.
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.
Can someone please help?
Thanks in advance.
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 ) ) )))
All Answers
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
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 ) ) )))