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.


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 | |
  • 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 '18 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 '18 at 18:43
  • When I use NOW() the result is in XX.XXXXX format. I just want numbers and 0 decimals. – Adeep Mar 11 '18 at 1:11
  • @Adeep Use FLOOR, or DATEVALUE, or change the number of decimals the formula uses. – sfdcfox Mar 11 '18 at 1:13

this might help.

            Datevalue (Expected Date Time) - Today()
| improve this answer | |
  • mind explaining? – glls Mar 10 '18 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 '18 at 17:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.