Nested IF statements using TODAY value

Question

Hi. I have a list in MS Lists with a number of columns whose information I want to use to provide a progress updates on each item in the list. I can't seem to get it to work properly. In the formulas current format, having no date in the 'due to AC' columns returns "overdue" in the progress column, despite a later nested IF statement that should make it 'pending'.

The Progress column is a calculated column that looks for inputs in other columns to return, "overdue", "on time", "no end date" or "pending".

I have tried moving the date calculation IF statement around to no avail.

The logic I want to achieve is:

If an item is 'active' in status column, I want to know whether it is 'overdue' or 'on time' based on today's date. If there is no due date, then I want the progress to be 'no end date'.

If an item is marked 'ACs office' or 'DCs office' in the status column, the 'Due to AC' date will be blank and I want the progress to be 'pending'

If an item is marked 'completed' in the status column, the 'due to AC' date will be blank and I want the progress to be 'completed'.

The formula I am currently using in the progress column is:

=IF([Due to AC]<TODAY(),"Overdue",IF(AND(ISBLANK([Due to AC]),Status="AC's Office"),"Pending",IF(AND(ISBLANK([Due to AC]),Status="DC's Office"),"Pending",IF(AND(ISBLANK([Due to AC]),Status="Active"),"No End Date",IF(AND(ISBLANK([Due to AC]),Status="Completed"),"Completed","On Time")))))

The error I am getting is when status is 'ACs office' or 'DCs office' and the date is blank, progress is shows as 'overdue' and not 'pending'. Its like the formula recognises a blank date as before today.

Thanks in advance.

Answer 1

Hi @Jon Watt ，

I tested it using the second formula you provided in your comment. You are brilliant, and the formula works perfectly, fulfilling your every need.

You mentioned that in "edit in grid view", remove the date and mark status 'DCs office' it correctly shows progress as 'pending' but when I exit editing the item and go back to the list the item is still showing 'overdue'.

In the edit mode, the Process column is correct, and I also tested the formula, which shows that there is no problem with the formula. I suspect that your list has too many items? Causes the response speed to be too slow? Or after a period of waiting, will it show normal again?

I suggest you create a new list to verify whether it is an error caused by the list, and tell me the answer, please?

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Thanks Haoyan, though not sure I can live up to the "brilliant" status you bestowed upon me lol.

My current list only has 30 items on it, so not sure if that blows out the max number allowed?

I also tried your suggestion of making a new list and experienced the same issues.

I did a bit of Googling and found this post in a chat:

It would seem my calculated column won't update itself each day and show whether an item is on time or overdue as I had hoped as it apparently is a design featue of calculated columns.

I had hoped that each day I opened the list it would refresh based on the calculated column formula and the associated date column.

This post then went on to talk about creating a flow, which I have no idea about.

Unless you or anyone else has other ideas, I'm out.

Thanks