How can I calculate days between dates if a field is blank? (Days open counter)

Jason Schmitz

My column property set to "text/number". The formula I am trying to use: =IF([Completion Date]@row = " ", "Closed", TODAY() - [Creation Date]@row)

I have a column named "Creation Date", then I have a column named "Completion Date". When there is a date in the completion date column I want it to populate "Closed". When the field (Completion Date) is left blank, I want to calculate the number of days it has been open.

Genevieve P.

Hi Jason,

It looks like you just have your formula backwards! IF statements are structured like so:

=IF this is true, then return this, if it's false return this.

Right now you're saying that if the Completion Date is blank you want it to say "Closed". Swap around your last two elements and it should work just fine! Try:

=IF([Completion Date]@row = "", TODAY() - [Creation Date]@row, "Closed")

Cheers,

Genevieve

Jason Schmitz

Whether there is a date in there or not it will only return "closed" now.

Genevieve P.

Hi @Jason Schmitz

Try taking away the space between your "quotes". To indicate a blank cell you'll want to have two quotes close together, like so: ""

Let me know if that fixed it!

Cheers,

Genevieve

Jason Schmitz

When I did that it tells me "#invalid operation"

Genevieve P.

Hi @Jason Schmitz

That tells us we're on the right track. You're getting the error from this part of the formula:

TODAY() - [Creation Date]@row

How are you populating the Creation Date column? This will need to be a date-type of column in order to subtract it from Today.

Jason Schmitz

I put a date in there and it reads closed now, but the calculation of today - date gives the #invalid operation. So looks like I solved half of it.

Genevieve P.

Hi @Jason Schmitz

Yes, it will have to do with the [Creation Date]@row not being seen as a date, which is why you're getting an error.

How are you populating the Creation Date column? 

Jason Schmitz

The Creation Date column properties was set to date. However that column is formulated to pull the date only (removing the times stamp) from the original coulumn which is autopopulated.

I referenced the original Date column (which included the time stamp) and it worked correctly.

Now I just removed the creation date column as it will no longer be needed.

Thank you for all your help.

Genevieve P.

No problem at all! I'm glad you were able to get it working. 🙂