TODAY Formula

Copper Contributor

I use the TODAY formula to track the amount of days a person is located in a specific room.  I base the starting point as their date of registering into the room and track the amount of days they are in the room with the formula assuming C3 is the cell with the initial date (1/01/2018) =TODAY()-C3

 

What I need to know is when I enter a new date in a different cell that captures their departure date I want the auto-calculations to stop, but still retain the total days in the room for historical purposes.

 

Is there something I add to the original TODAY formula that will stop calculating once the cell with the departing date is filled in?

1 Reply
Lets assume the departing date is in D3 and the days calc is in E3
Then you could use this in E3

=if (D3 = "", TODAY(), D3) - C3


Or to be a little more advanced...
=if (C3 = "", 0, if ( D3 = "", Today(), D3 ) - C3

The first part C3 = "" just checks that you have entered a start date to avoid a negative number showing.