Billing Date Formula

Srowley

Looking for a formula that generates a renewal date. Here are my three column headers

Column 7= Return Date

Column 8 = Paid Through 

Column 9 = Next Invoice Date

I want the Next Invoice Date to be either the Return Date or the day after the Paid Through Date. If the Return Date is Blank, the Next Invoice Date is the Paid Through Date +1.  

Basically, we bill in 30-day cycles, unless the property is scheduled to return prior to the next renewal cycle. The Return of property always trumps the renewal date, unless it's blank. As soon as we get a call to schedule the return, we'd put the date in the "return date" column and that should then change the "next invoice date column"

I can do this in Excel but am too new in Smartsheet. Feeling a bit of a dumbsheet (that's probably an old joke by now?

 

Chris Campbell

Here's the formula

=IF(ISDATE([Return Date]1), [Return Date]1, [Paid Through]1 + 1)

 

You use this formula for Column 9 - Next Invoice Date.  It's essentially checking if there's a value in "Return Date" and if there isn't then it uses paid through +1.  You need to copy this formula down into each row to make sure you don't end up with blank fields.