Trying to only calculate from a start date to today only when a certain cell contains "Pending"

[fifi8495]

Good afternoon,

I am trying to get a formula to only calculate when another cell contains the word "Pending" from a start date to today. Once The cell changes to Complete, I want the number of days to stay present but not calculate anymore.

For example,

-B3 = Start Date​

-E3 = Pending / Complete​

-H3 = Number of days between Start Date and Today​

If anyone can assist, that would be greatly appreciated.

 

[Coyotex3]

Maybe

IF(E3="Pending",TODAY()-B3+1,"")

I missed your second requirement on this one. Once it changes to complete, would it be possible to paste as values to keep the value on the cell?

 

[fifi8495]

Maybe

IF(E3="Pending",TODAY()-B3+1,"")

I missed your second requirement on this one. Once it changes to complete, would it be possible to paste as values to keep the value on the cell?

The first one worked but lost the value once it when to complete. What do you mean paste as values?

 

[Coyotex3]

The first one worked but lost the value once it when to complete. What do you mean paste as values?

The formula is only looking for the word "Pending". So a workaround would be to click on the cell where you have the formula, Copy it(Ctrl +C) and then right click on the same cell and paste the formula as value(Can also be done by pressing Ctrl +Shift + V).

 

[fifi8495]

The formula is only looking for the word "Pending". So a workaround would be to click on the cell where you have the formula, Copy it(Ctrl +C) and then right click on the same cell and paste the formula as value(Can also be done by pressing Ctrl +Shift + V).

View attachment 104400

So is there no other way for it to do it automatically?

 

[Coyotex3]

So is there no other way for it to do it automatically?

There might be a way of doing it automatically. But a few things I can think of are, the formula I provided is using Today in the calculation. Would there be an instance where the status changed from Pending to Complete on a day that isn’t today?

Will the Date completed tab be populated? If so I can alter the formula to calculate based on the date it was completed?

 

[fifi8495]

There might be a way of doing it automatically. But a few things I can think of are, the formula I provided is using Today in the calculation. Would there be an instance where the status changed from Pending to Complete on a day that isn’t today?

Will the Date completed tab be populated? If so I can alter the formula to calculate based on the date it was completed?

It will be eventually, but I will need the number of days during the process before its completed. Having the information stay is more for tracking purposes for the future. The numbers of days during the process is more important.

 

[Coyotex3]

It will be eventually, but I will need the number of days during the process before its completed. Having the information stay is more for tracking purposes for the future. The numbers of days during the process is more important.

Sadly, I'm at a lost as to how to accomplish this.

The best I can think of(Limited Knowledge) would be this formula. This would require for the Completed tab to be filled out as this formula will look at Column J and subtract that from the Date column.

=IFNA(IFS(E3="Pending",TODAY()-B3+1,E3="Complete",J3-B3+1),"")

 

[fifi8495]

Sadly, I'm at a lost as to how to accomplish this.

The best I can think of(Limited Knowledge) would be this formula. This would require for the Completed tab to be filled out as this formula will look at Column J and subtract that from the Date column.

=IFNA(IFS(E3="Pending",TODAY()-B3+1,E3="Complete",J3-B3+1),"")

This one will actually work for what we need. Thank you so much!

 

[Coyotex3]

You're welcome! Happy to help!