Forum Discussion
Long IF formula to addon if more than 24 months, then TRUE is "Aged"
Hi,
Currently working on an excel formula to eventually put up on SharePoint. Unfortunately, SharePoint only provides me with 1 line for an excel formula and I'm trying to automate, in a sense, a status column based on dates.
So far the Yes, At Risk, and No has been working, now I have to add an "Aged" as a return.
This is the formula I have so far
=IF(DATEDIF(TODAY(),E4,"Y")>2,"Aged",IF(E4<TODAY(),"No",IF(E4<(DATE(YEAR(TODAY()),MONTH(TODAY())+3,DAY(TODAY()))),"At Risk",IF(TRUE,"Yes",""))))
OneDrive link: https://contracostahsd-my.sharepoint.com/:x:/g/personal/jmoral2_cchealth_org/EZKpM6GVFhZGkX37Fwoqyo8BUqu8AdhPtEqOtVayts8aig?e=vv6ANh
Any help will be appreciated.
MANY thanks.
10 Replies
I'm sorry to jump in, but why DATEDIF(TODAY(), E4,"Y"), not DATEDIF( E4,TODAY(),"Y") ? Earlier date shall be first here.
- No need to apologize at all, I should be the one doing that. Especially considering I've kind of been confusing mathetes about my organization. I have done that, but now the At Risk and Yes true values are showing a #NUM error.
So, actually we don't care about Renewal Date, we check how many months today since Approval Date and return notification depends on that. If so that could be
=IF( TODAY() <= EDATE(D4, 8), "Yes", IF( TODAY() <= EDATE(D4, 12), "At Risk", IF( TODAY() < EDATE(D4, 24), "No", "Aged" ) ) )or, in SharePoint notation which doesn't support EDATE
=IF( TODAY() <= DATE(YEAR(D4),MONTH(D4)+8,DAY(D4)), "Yes", IF( TODAY() <= DATE(YEAR(D4),MONTH(D4)+12,DAY(D4)), "At Risk", IF( TODAY() < DATE(YEAR(D4),MONTH(D4)+24,DAY(D4)), "No", "Aged" ) ) )With that logic I didn't catch initial sample with At Risk on next date after Approval Date.
Or we have to ignore Approval Date and calculate only how many months are from today to Renewal Date or after that. IMHO, when it looks like the logic shall be different. If today we are at 2 months after Renewal Date, is that "Aged" or what? Or it could be one more logic if the take into account all three - Approval Date, Renewal Date and TODAY. Have no idea which one.
I'm sorry to jump in, but...
No need to apologize, Sergei. Your comments and insights are always valuable. Always.
I hadn't gotten to that level of detail yet; was trying first to address the IF vs IFS issue, along with clarity of the conditions and responses.
Without the actual spreadsheet, I'm not going to try to edit (without testing) a revised formula. I will be bold to suggest, however, that you research the IFS function, which is cleaner and clearer that deeply nested IF functions. You just need to be aware that basically it moves from left to right through the conditions and stops once a condition is met, so make sure you sequence the various combinations of condition/consequence accordingly.
It would, in your case, look something like this (broken up into multiple lines solely for clarity)
=IFS(
DATEDIF(TODAY(),E4,"Y")>2,"Aged",
E4<TODAY(),"No",
E4<(DATE(YEAR(TODAY()),MONTH(TODAY())+3,DAY(TODAY()))),"At Risk",
TRUE,"Yes",""
your new condition & consequence
)
P.S. It always helps if, rather than an image, you post a copy of the actual workbook/spreadsheet on OneDrive or GoogleDrive, with a link here that grants access.
- Appreciate the input and the feedback. Didn't think of actually put the file in GoogleDrive or OneDrive, it would be 1000x easier for help. Definitely will add it when I can and will try that formula out.
Unfortunately, the IFS function is not supported on SharePoint so I have to stick with IF statement for now.- Well then, put it here for us to help with the IF function. I just don't like having to create the underlying set of conditions to test a formula, and you clearly have a spreadsheet already.
