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))
Answers
-
What would you want it to do if there is no date?
-
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 "". :)
-
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.
-
I am looking for it to ignore the [Current Coterm Date] if it is blank and calculate with just the Actual Billing Date.
-
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))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!