Formula help please

Hi,

See below formula and snippet for reference - I want it all the same but the only thing i want adding is if the line is overdue then in the 'late booking?' column to show 'YES - OVERDUE SERVICE'

=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", "ETA EARLY - OUTSIDE MONTH"), IF([Planned/Proposed Service Date]@row > [Due Date Of Next Inspection]@row , "YES", "N/A"))))

Find more posts tagged with

Accepted answers

Paul Newcome

Try this:

=IF([Inspection Overdue Status]@row = "Overdue", "YES - OVERDUE SERVICE", 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", "ETA EARLY - OUTSIDE MONTH"), IF([Planned/Proposed Service Date]@row > [Due Date Of Next Inspection]@row, "YES", "N/A"))))

[Deleted User]

@Charlie H =IF(ISBLANK([Planned/Proposed Service Date]@row ), "",
IF([Due Date Of Next Inspection]@row < TODAY(), "YES - OVERDUE SERVICE",
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", "ETA EARLY - OUTSIDE MONTH"),
IF([Planned/Proposed Service Date]@row > [Due Date Of Next Inspection]@row , "YES", "N/A")))))

All comments

Anjanesh Vaidya

Hi @Charlie H
You just have to add another condition. Please use the formuls below:

=IF([Inspection Overdue Status]@row="Overdue",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", "ETA EARLY - OUTSIDE MONTH"), IF([Planned/Proposed Service Date]@row > [Due Date Of Next Inspection]@row , "YES", "N/A")))),"")

Here, when the Inspection Overdue Status column shows Overdue, the Late Booking values will be displayed based on the defined logic, Otherwise, the field will remain blank.

Charlie H

Hi,

Thanks for getting back to me.

this has not worked - all of my others what had different logic in, went blank - i need the formula exactly the same but just the overdue bit adding - see snippet below of other logic as an example which i need to be the same

Try this:

Thank you!!!

Happy to help. 👍️

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.