Calculated formula not showing correct behavior

Question

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.

Be aware 'today' in Formulas has some issues: https://sharepoint.stackexchange.com/questions/151144/how-to-use-today-and-me-in-calculated-column/151336#151336 — Danny '365CSI' Engelman, May 21 '19 at 09:21

score 2 · Accepted Answer · answered Mar 16 '19 at 03:04

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")
       )
     )
   )

answered Mar 16 '19 at 03:04

Mike Smith - MCT

6,807
2
12
21

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 '19 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 Mar 16 '19 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 '19 at 20:04

score 0 · Answer 2 · edited Mar 16 '19 at 00:16

0

try this :

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

edited Mar 16 '19 at 00:16

Ganesh Sanap - MVP

44,918
21
30
61

answered Mar 15 '19 at 22:37

José Manuel Trejos Atencio

1

Calculated formula not showing correct behavior

2 Answers2