Blank Date in Formula

Hi,

Can someone help me with accounting for a blank date? The formula works if I enter a date in the Current Coterm field otherwise it shows Invalid Data Type.

=IF([Initial or Renewal term?]@row = "Initial", IF((DAY([Current Coterm Date]@row, [Actual Billing Date]@row) / 365 * 12) > [Initial Term NLT]@row, DAY([Current Coterm Date]@row, [Actual Billing Date]@row) / 365 * 12, [Initial Term NLT]@row), IF((DAY([Current Coterm Date]@row, [Last Renewal Date]@row) / 365 * 12) > [Renewal Term NLT]@row, (DAY([Current Coterm Date]@row, [Last Renewal Date]@row) / 365 * 12), [Renewal Term NLT]@row))

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

What would you want it to do if there is no date?

Austin Urban JCI

A good catch-all I use in instances like this are IFERROR() functions. So it would look like:

_________________________________________________________________

=IFERROR(

IF([Initial or Renewal term?]@row = "Initial", IF((DAY([Current Coterm Date]@row, [Actual Billing Date]@row) / 365 * 12) > [Initial Term NLT]@row, DAY([Current Coterm Date]@row, [Actual Billing Date]@row) / 365 * 12, [Initial Term NLT]@row), IF((DAY([Current Coterm Date]@row, [Last Renewal Date]@row) / 365 * 12) > [Renewal Term NLT]@row, (DAY([Current Coterm Date]@row, [Last Renewal Date]@row) / 365 * 12), [Renewal Term NLT]@row))

,"")

_________________________________________________________________

Anything that kicked an error would then be blank or "". :)

Nick Korna

Hi @Farrah Jensen,

What outcome(s) are you looking for in the event you have a blank date? I would guess you would add some IFERROR statements around your IFs, but exactly how the formula would look would depend on what you want the result to be.

Farrah Jensen

I am looking for it to ignore the [Current Coterm Date] if it is blank and calculate with just the Actual Billing Date.

Paul Newcome

Each place that you have this:

DAY([Current Coterm Date]@row, [Actual Billing Date]@row)

would be replaced with this:

DAY(IF([Current Coterm Date]@row <> "", [Current Coterm Date]@row, [Actual Billing Date]@row))

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.