Calculating a schedule date from a Date

We send out notifications in the month before a closing month, on the 23rd. This simple formula was working well, except it does not work for January. If the closing date is 12/31/23, the notification date is 11/23/23. Any help would be greatly appreciated.

=IFERROR(DATE(YEAR([Closing Date]@row), MONTH([Closing Date]@row) - 1, 23), "")

Find more posts tagged with

Accepted answers

KPH

You have at least 2 options (as well as living with it) 😁

Add another IF to the start to evaluate if the Closing Date is blank and return nothing in that case.
Return to using blank for an IFERROR but include an IF after it to use the alternate formula if MONTH([Closing Date]@row) is January.

I'd probably go with option 1 as it is the easiest to understand when looking at the formula. Although, option 2 is probably the most robust - just in case there are other errors.

Here is option 1:

=IF(ISBLANK([Closing Date]@row), "", (IFERROR(DATE(YEAR([Closing Date]@row), MONTH([Closing Date]@row) - 1, 23), DATE(YEAR([Closing Date]@row) - 1, 12, 23))))

Here is option 2:

=IFERROR(IF(MONTH([Closing Date]@row) = 1, DATE(YEAR([Closing Date]@row) - 1, 12, 23), (DATE(YEAR([Closing Date]@row), MONTH([Closing Date]@row) - 1, 23))), "")

All comments

KPH

Your formula is creating a date based on the date in the closing date column.

It takes the year from the closing date column and uses that for the new date

It takes the month from the closing date column and subtracts one to use that for the new date

It then uses 23 as the day.

It cannot take 1 from the month of January (month 1) as there is no month 0.

So instead would generate an error.

In your case the IFERROR part of the function returns "".

So you see nothing.

If you replace "" with a DATE function for December 23 in the year prior to the year of the closing date you should be fine.

DATE(YEAR([Closing Date]@row)-1, 12, 23)

So your formula would be

=IFERROR(DATE(YEAR([Closing Date]@row), MONTH([Closing Date]@row) - 1, 23), DATE(YEAR([Closing Date]@row)-1, 12, 23))

Robert S Fike

That worked. Thank you. But now, in jobs I don't have a date plugged in yet. It shows #INVALID DATA TYPE. Can this be removed, or we just live with that.

Thanks

KPH

You have at least 2 options (as well as living with it) 😁

Add another IF to the start to evaluate if the Closing Date is blank and return nothing in that case.
Return to using blank for an IFERROR but include an IF after it to use the alternate formula if MONTH([Closing Date]@row) is January.

I'd probably go with option 1 as it is the easiest to understand when looking at the formula. Although, option 2 is probably the most robust - just in case there are other errors.

Here is option 1:

=IF(ISBLANK([Closing Date]@row), "", (IFERROR(DATE(YEAR([Closing Date]@row), MONTH([Closing Date]@row) - 1, 23), DATE(YEAR([Closing Date]@row) - 1, 12, 23))))

Here is option 2:

=IFERROR(IF(MONTH([Closing Date]@row) = 1, DATE(YEAR([Closing Date]@row) - 1, 12, 23), (DATE(YEAR([Closing Date]@row), MONTH([Closing Date]@row) - 1, 23))), "")

Robert S Fike

Thank you very much. That worked perfect.

KPH

Wonderful! 👍️

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.