0

I have a calculated column called DaysDifference which calculates days difference between two date fields: [Due Date]- [DateToday], I am updating datetoday field everyday using Flow to set it to current date.

I am using below calculated formula for another calculated column called Due in Days:

=IF(DaysDifference<=7,"7",IF(DaysDifference<=15,"15",IF(DaysDifference<=30,"30",IF(DaysDifference<0,"Overdue"))))

Then I have grouped the list view by Due in Days column and it works great for 4, 15, 30 days scenario but for the last condition its not working. For example I have a task for which (Due Date: 2/14, todays date: 3/14) DaysDifference shows -27. So this is an overdue task but from the formula I am using, its falling under "7". Can someone help me with the updated formula.

I just want to log overdue in calculated column if DaysDifference column shows negative value.

enter image description here

Improve this question
1

2 Answers 2

Reset to default
2

Change the order of your tests. Start with the "over due" test. You may also want a value if >30 days.

=IF(DaysDifference<0,"Overdue",
   IF(DaysDifference<=7,"7",
     IF(DaysDifference<=15,"15",
       IF(DaysDifference<=30,"30",
        "Over 30")
       )
     )
   )
Improve this answer
3
  • works, thanks Mike. For some reason though after grouping by the calculated column, I am not seeing sorting in proper order, see the screenshot attached in question description, thoughts?
    – mdevm
    Commented Mar 16, 2019 at 18:11
  • Grouping is being done on "text", not numbers, so add a space or zero in front of the "7". I.e. IF(DaysDifference<=7," 7",
    – Mike Smith - MCT
    Commented Mar 16, 2019 at 18:34
  • Adding space worked great, thanks a bunch mike. Sometimes simple solutions like these make our life so much easier.
    – mdevm
    Commented Mar 18, 2019 at 20:04
0

try this : 

=IF( AND ( DaysDifference<=7 ,DaysDifference>=0) ,"7",IF(DaysDifference<=15,"15",IF(DaysDifference<=30,"30",IF(DaysDifference<0,"Overdue"))))
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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