Date to stop calculating after certain time

Question

I have a situation,just an admin here. we have Expected finish date which is a formula field that gets calculated based on SLA days which is defined as no of days to close the case. I am trying to create a formula field called Total Remaining Days(To close). My formula is; Datevalue (Expected Date Time) - Today(). this does return the value i need but I need it to stop when the case is closed (when the expected date time is less than today). How can i do that? Appreciate all the help I can get.

Answer 1

You can use the ClosedDate field to show the stopping point:

Expected_Date_Time__c - BLANKVALUE(ClosedDate, NOW())

This formula basically uses the ClosedDate, if set, or today's date otherwise. It will count down towards the expected date until closed, at which point the remaining time will become fixed. Note that this formula can go negative, which is what you'd expect if the expected date was not met (SLA was violated, or whatever), which you can create dashboards and reports for to show when/how many times an SLA was violated.

Answer 2

this might help.

 IF( ISPICKVAL(Status,'CLOSED'),
            0,
            Datevalue (Expected Date Time) - Today()
        )