0

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.

Improve this question

2 Answers 2

Reset to default
0

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.

Improve this answer
4
  • isn't this formula like Expected_Date_Time__C - Today() ? I was getting the same result. but whats the difference between today vs now? now captures current date and time, correct?
    – Adeep
    Mar 10, 2018 at 17:40
  • @Adeep If you're using date/time, now is more appropriate, as today evaluates to midnight of the day. This will show slightly different results depending on time zones, etc. On the other hand, you may want to use DATEVALUE to get just the date from each field, instead of being time dependent.
    – sfdcfox
    Mar 10, 2018 at 18:43
  • When I use NOW() the result is in XX.XXXXX format. I just want numbers and 0 decimals.
    – Adeep
    Mar 11, 2018 at 1:11
  • @Adeep Use FLOOR, or DATEVALUE, or change the number of decimals the formula uses.
    – sfdcfox
    Mar 11, 2018 at 1:13
0

this might help.

 IF( ISPICKVAL(Status,'CLOSED'),
            0,
            Datevalue (Expected Date Time) - Today()
        )
Improve this answer
2
  • mind explaining?
    – glls
    Mar 10, 2018 at 0:47
  • Thanks but come to find out Sla status is a Text field with formula in itself so I am not sure how to use it.
    – Adeep
    Mar 10, 2018 at 17:44

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .