Formula: Blank Date Causing Error

Hi There, I’m having an issue with a formula. What I’m trying to do is calculate the the length of time for a customer being on our service.

Here are the fundamentals I’m working with:

  • Length of time for active customers is start date to current date
  • If an item doesn’t have an end date, I want the current date to be used
  • If a customer is not active, they will have an end date listed

Here is the formula I have put together:
IF({Date Customer Ended}="",(DAYS(TODAY(),{Date Customer Started})/30.417),(DAYS({Date Customer Ended},{Date Customer Started})/30.417))

Screen Shot 2021-09-08 at 10.01.00 AM

@Thomas14,

In most cases with monday formulas every section of the formula must valid even if logically it won’t be needed. With your formula, if the Date Customer Ended is blank, DAYS({Date Customer Ended}, {Date Customer Started}) will return an error (even though it won’t be used). So, if we change that portion so that it will always be vaild, everything will work:

IF({Date Customer Ended}="", DAYS( TODAY(), {Date Customer Started})/30.417, DAYS( IF({Date Customer Ended}="", "1/1/21", {Date Customer Ended}), {Date Customer Started})/30.417)

Jim

Jim,

I greatly appreciate the help! And that makes a lot of sense.

Thank you so much :slight_smile:

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.