Formula not working - need help please - showing as incorrect argument

Can someone please help on the below formula - it is not working and showing as incorrect argument - I want it to take into consideration both the month and the year - if it past or later than the proposed service date then i want it to show its late via 'YES', blank if its blank and if its in date to show as N/A - the column this formula is in is called 'Late Booking?'

=IF(ISBLANK([Planned/Proposed Service Date]@row ), "", IF(MONTH(YEAR([Planned/Proposed Service Date]@row ) > (MONTH(YEAR([Due Date Of Next Inspection]@row ), "YES", IF([Planned/Proposed Service Date]@row <> [Due Date Of Next Inspection]@row , "N/A"))))))

Find more posts tagged with

Sheets

Formulas

Accepted answers

All comments

Alan P.

You can do this by using the date columns in the formula. Separating the MONTH and YEAR are not necessary.

=IF(ISBLANK([Planned/Proposed Service Date]@row ), "", IF([Planned/Proposed Service Date]@row > [Due Date Of Next Inspection]@row , "YES", IF([Planned/Proposed Service Date]@row <> [Due Date Of Next Inspection]@row , "N/A")))

Charlie H

Hi Alan - this is what i originally put in and see below the issue - the date of next inspection is due February 2027 and the planned date is for February 2027 so therefore it is being booked in the same month so it isn't a late booking. (See snippet below where i use your formula) What i need is for it show any date in the future outside of that month and year is a late booking ( if its booked in the same month then this is okay). My formula above in my original question is what i used but it only took into consideration the month and not the year. The actual day is not what i want here it is the month, but also the year - if i used my original formula above then no matter the year if its was the same month it would show it as in date which is why i needed to incorporate the year too. Hope this makes sense

please help :)

Thanks

Alan P.

Ahhh ok. If you want to evaluate both the month and year, you can use an AND statement in your if statement. Then you can evaluate the MONTH and YEAR separately, but the AND logic requires that both conditions are met before returning a true.

AND(logical_expression1, [logical_expression2, ...])

(the formula below is for the first part of your formula to evaluate for "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 ))

Charlie H

HI Alan - again, thanks - it shows as incorrect argument again - see below my formula im using now - it took into account the month so i am nearly there with it - do i just need to add Year in too somewhere? see below where i add this formula but as you can see the planned date month is January but the year is 2028 but still shows as not late when the date of next due date is 2027?? what it is doing is taking any earlier month or the month it falls in regardless of the year as a in date booking - i need the year

=IF(ISBLANK([Planned/Proposed Service Date]@row ), "", IF(MONTH([Planned/Proposed Service Date]@row ) > MONTH([Due Date Of Next Inspection]@row ), "YES", IF([Planned/Proposed Service Date]@row <> [Due Date Of Next Inspection]@row , "N/A")))

Alan P.

Yes, you have to add the year part, too.

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 )), "YES"….

In the formula the AND statement allow you to evaluate the MONTH:

MONTH([Planned/Proposed Service Date]@row ) > MONTH([Due Date Of Next Inspection]@row )

and the YEAR:

YEAR([Planned/Proposed Service Date]@row ) > YEAR([Due Date Of Next Inspection]@row ))

Paul Newcome

If the due date is March of 2027 and the planned is May of 2027, this will not show as late because the planned year is not greater than the due year.

Similarly, if the due date is December 2027 and the planned is January 2028, it will not flag as late because the planned month is not greater than the due month.

Try this instead:

=IF([Planned/Proposed Service Date]@row <> "", IF(VALUE(YEAR([Planned/Proposed Service Date]@row ) + RIGHT("0", MONTH([Planned/Proposed Service Date]@row ), 2)) > VALUE(YEAR([Due Date Of Next Inspection]@row ) + RIGHT("0", MONTH([Due Date Of Next Inspection]@row ), 2)), "YES", "N/A")

This creates a number of yyyymm for both the planned and the due dates. If the yyyymm of the planned is greater than the yyyymm of the due date, it will output "YES", otherwise it will output "N/A".

This will not run at all (and thus leave the cell blank) if there is no planned date.

Charlie H

Hi,

it did not work - see below what is now working and added some bits

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

Charlie H

Hi,

it did not work - see below what is now working and added some bits

Paul Newcome

@Charlie H When you say it did not work, what exactly do you mean?

Charlie H

it wasnt what i was looking for - so the formula was showing incorrect argument.

Paul Newcome

@Charlie H I'd be curious to see it in your sheet to see the differences. It worked in mine.

Charlie H

I don’t think I explained what I really needed properly - it is hard via typing it out but I managed to do it anyway.

I will be posting more here when I need help so thank you for replying so quick and looking to help

thanks

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.