Formula help - advanced logic

Please see formula below - when the planned date is less than the due date and inside the same month it shows as 'N/A'…. when the planned date is greater than either the actual day/date or the month in the due date of next inspection column it shows as 'Yes' in late booking column - when the planned date is less than the due date it shows as 'IN PAST - ACTION ASAP' - I want it so that if the planned date is a date less than todays date then show as 'IN PAST - ACTION ASAP', also what i want is.. if the planned date is later than the due date but outside of the month so greater then i want it to show as 'YES - OUTSIDE MONTH- but if the planned date is less than the due date of next inspection but outside of its month so month outside the due date month i want it to show as ' IN PAST - OUTSIDE MONTH'.

I know this is a tad confusing but please see formula below and also a snippet of it. The formula the column is in is 'Late Booking?'

=IF(ISBLANK([Planned/Proposed Service Date]@row ), "", IF([Planned/Proposed Service Date]@row > [Due Date Of Next Inspection]@row , "YES", IF(AND(MONTH([Planned/Proposed Service Date]@row ) = MONTH([Due Date Of Next Inspection]@row ), YEAR([Planned/Proposed Service Date]@row ) = YEAR([Due Date Of Next Inspection]@row )), "N/A", "IN PAST - ACTION ASAP")))

Find more posts tagged with

Accepted answers

Monika_lnu

@Charlie H =IF(ISBLANK([Planned/Proposed Service Date]@row ), "",
IF([Planned/Proposed Service Date]@row < TODAY(), "IN PAST - ACTION ASAP",
IF(OR(MONTH([Planned/Proposed Service Date]@row ) <> MONTH([Due Date Of Next Inspection]@row ), YEAR([Planned/Proposed Service Date]@row ) <> YEAR([Due Date Of Next Inspection]@row )),
IF([Planned/Proposed Service Date]@row > [Due Date Of Next Inspection]@row , "YES - OUTSIDE MONTH", "IN PAST - OUTSIDE MONTH"),
IF([Planned/Proposed Service Date]@row > [Due Date Of Next Inspection]@row , "YES", "N/A")
)
)
)

All comments

Jgorsich

I'm having a very hard time parsing your desires from your write up, I'd strongly recommend rewording it to make it more clear. My personal method, when making a complex if statement that is giving me trouble, is to make an ordered list where to get to question 2 you had to answer "no" to question 1 FIRST. Once you can structure you desire that way, the if() statement just kinda falls out of it. Sometimes just clarifying your thinking is critical also.

In your case, for example, you seem to want 5 different possible outcomes, roughly defined as follows

"N/A": Planned before it is due AND in the same month AND not in the past
"Yes": Planned date after it is due AND not in the past
"In Past - Action ASAP": Planned date in the past AND in the same month as the Due Date
"Yes - Outside Month": Planned date after it is due AND not in the same month as the Due Date AND not in the past
"In Past - Outside Month": Planned date in the past AND not in the same month as the Due Date

Presuming that is correct, you can see that you've got 3 basic tests you are evaluating:

Is the planned date before or after the due date?
Is the planned date in the past?
Is the planned date in the same month as the due date?

Logically, I might structure that like this:

Is the planned date in the past? If yes:
1. Is the planned date in the same month as the due date? if yes show "In Past - Action ASAP"
2. otherwise it is NOT in the same month as the due date so show "In Past - Outside Month"
Is the planned date after the due date? If yes:
1. This is already NOT the past, so check month: is the planned date in the same month as the due date? If yes show "Yes"
2. Otherwise show "Yes - Outside Month"
You aren't in the past and the planned date is before the due date, so show "N/A"

You can see from that that the if() statement then just comes directly out of the logic:

if(plannedDate<today(),

if(month(plannedDate)=month(dueDate),"In Past - Action ASAP","In Past - Outside Month"),

if(plannedDate>dueDate,

if(month(plannedDate=month(dueDate),"Yes","Yes - Outside Month"),

"N/A"))

Obviously, that logic isn't grammatically correct for SmartSheet - you'll need to drop your actual column references in there and need to include your month+year check in place of what I just wrote as month, but the structure should be clear.

Now, all of that being said, I think your criteria could probably use a review - first, the "in the same month" portion. What if the due date is the first or last of the month? Then being only 1 day before it is due or only 1 day late will put it "outside of the month", is this really what you want? Second, "In past - action ASAP" and "in past - outside month" seem like they should have the same level of priority, why does only one get the "asap" designation? Finally, it seems like "outside of the month" is a big deal for you - why not track this even if the planned date is in the future and before the due date?

Charlie H

Hi,

The layout i have put in the message makes sense but because the logic is fiddly it is hard to get across by just typing it - This is not myself wanting it like this but this is a brand new client on a contract i am mobilizing for a go live very soon - for booking in PPM's all of the logic and abbreviations are client driven - Based off the value in the 'late booking?' column i will be using automation via request an update but for each value in the cell it will be a different automation request an update with different terminology , therefore guiding the customer and showing them what type of attempted ETA this is based off the due date - this is why it needs to be like this.

see below

"N/A": Planned before it is due AND in the same month
"Late Booking": Planned date after it is due but in the same month
"In Past - Action ASAP": Planned date is less than today's date (in the past) (this doesn't go to the customer for request an update, this now updates my team to liaise with the supplier as the date is in the past but shows them in the main sheet too)
"Late Booking - Outside Month": Planned date after it is due AND in a greater month than the Due Date
"In Past - Outside Month": Planned date less than the due date AND in a previous month. (if its less than today's date it will show as the above 'In Past - Action ASAP')

The logic is all there i just need to know the formula please - can you update the below if possible to match this? Thanks

Monika_lnu

Charlie H

amazing, thanks - this worked

Thank you very much

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.