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

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")
       )
     )
   )
  • 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 Mar 16 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 - MVP Mar 16 at 18:34
  • Adding space worked great, thanks a bunch mike. Sometimes simple solutions like these make our life so much easier. – mdevm Mar 18 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"))))

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.