Formula: Blank Date Causing Error

[Thomas14 (Thomas Waning)]

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

[JCorrell (Jim - The Monday Man)]

@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

[Thomas14 (Thomas Waning)]

Jim,

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

Thank you so much