Blank Date in Formula

Farrah Jensen

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))

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))