+ Start a Discussion
sam_Adminsam_Admin 

How to add close date to this formula

Hello,

     Iam posting this again, but here is what iam trying to do , i just need this formula to work only if case is open, it should stop calculating the case age days once the case is closed , right now this formula excludes saturdays and sundays while calculating the age but even it keeps calculating while the case is closed any idea on how to avoid this ,here is what i have

 

CASE(
MOD(DATEVALUE(CreatedDate) - DATE(1900, 1, 7), 7),
0, (TODAY() - DATEVALUE(CreatedDate)) - 1 - FLOOR((TODAY() - DATEVALUE(CreatedDate))/7)*2,
1, (TODAY() - DATEVALUE(CreatedDate)) - FLOOR((TODAY() - DATEVALUE(CreatedDate))/7)*2,
2, (TODAY() - DATEVALUE(CreatedDate)) - FLOOR((TODAY() - DATEVALUE(CreatedDate))/7)*2,
3, (TODAY() - DATEVALUE(CreatedDate)) - FLOOR((TODAY() - DATEVALUE(CreatedDate))/7)*2,
4, (TODAY() - DATEVALUE(CreatedDate)) - FLOOR((TODAY() - DATEVALUE(CreatedDate))/7)*2,
5, (TODAY() - DATEVALUE(CreatedDate)) - 2 - FLOOR((TODAY() - DATEVALUE(CreatedDate))/7)*2,
6, (TODAY() - DATEVALUE(CreatedDate)) - 2 - FLOOR((TODAY() - DATEVALUE(CreatedDate))/7)*2,
null)


Any Help would be greatly appreciated!
Thanks

 

VJSFDCVJSFDC

hi,

 

Trying creating 3 formula fields:

 

first formula field>> Current Age: create the CASE formula you mentioned below using CreateDate field in it.

Secound Formula Field>> Closed Age:Create the CASE Formala mentioned below using the Case Closed Date field in it.

Third Formula field also the one you display on UI :

 

IF( ISNULL( ClosedDate ) , Current Age , Closed Age )

 

 

Hope this helps.