Hi Guys,
I was wondering if you could help me with the following issue. I'm struggling with a requirement to show which policies are overdue.
I have a SharePoint list with the following fields:
- 'Today's date'
- 'Policy due date': the date when policy document is going to expire
- 'Date Diff - Today's vs. Due': calculated field which shows the difference between today's date and due date. Function used: =DATEDIF([Today's date],[Policy due date],"d")
- 'Policy due time': this field should return different single lines of text based on the number of days in 'Date Diff - Today's vs. Due' field. For example: if it's more than 60 days, it should display 'Accurate'. I'm getting errors when the 'Today's date' is greater than 'Policy due date'.
Instead of displaying overdue, I'm getting #Value! error.
In 'Policy Due Time' I used the following function:
=IF([Date Diff - Today's vs. Due]>60,"Accurate",IF([Date Diff - Today's vs. Due]<30,"Due in one month",IF([Policy due date]<[Today's date],"Overdue")))
From what I've read on different forums, I should use IFERROR function, but I don't really know how to use it to get 'Overdue' value instead of #Value!.
Apart from that, can you please let me know which function should be used to return 'Due in two months' text? It should calculate ony the values which are less than 60 and greater than 30.
I'd really appreciate your help with the above matter. Thanks in advance. :)