How Do I tell a cell that if the cell contains a date then subtract from another date, If the cell d

Melissa Shoumlisky

I'm stumped.

I've written a formula that says, if my finish date is blank, then take my start date and subtract it from today's date to get the # of days open:

=IF([Finish Date]2 = 0, TODAY() - [Start Date]2)

Works great!

But in the same formula, I'm trying to get it to say, but if the finish date is NOT blank then take the finish date minus the start date to get the number of days the COMPLETED project was open.

=[Finish Date]1 - [Start Date]1

How do I get this all together in one formula that says:

If the finish date field is blank then subtract start date from today's date, but if the finish date field is NOT blank, then subtract the start date from the finish date. 

Thank you in advance.

Melissa

 

 

L_123

=if(isdate([Finish Date]@row),today()-[Start Date]@row,[Finish Date]@row - [Start Date]@row

 

If statements are formatted as 

=if(criteria,"Return if True","Return if False")

You just need to add your false part to the formula. I threw in a couple of extra things you can take a look at, but the main fix is to just put a comma and then your return if false.

Melissa Shoumlisky

Thank you. I typed this in:

=IF(ISDATE([Finish Date]2), TODAY() - [Start Date]2, [Finish Date]2 - [Start Date]2) 

If the finish date is empty it's not returning a value instead of taking today's date minus the start date.  If the finish date is filled in, it's returning a value of 2 regardless of what finish date I enter.

Did I type something wrong?

 

Paul Newcome

Try swapping your criteria...

 

=IF(ISDATE([Finish Date]2), [Finish Date]2 - [Start Date]2, TODAY() - [Start Date]2) 

 

If the Finish Date is a date, then do Finish Date minus the Start Date, otherwise do Today minus the Start Date.

Melissa Shoumlisky

That worked! Thank you!!

Paul Newcome

Happy to help.