Counting Number of Days Until New Date is Entered

[jdpro]

A	B	C	D	E
Open Date	Expiration Date	Days to Expiration	# Days	Close Date
10/10/22	10/21/22	=MAX(0,B2-TODAY())		10/13/22
11/17/22	12/16/22	=MAX(0,B3-TODAY())

This chart represents contracts with expiration dates. I would like some help with a formula for column D. The 10/21/22 contract was closed before the expiration date, and the 12/16/22 contract is still open. It may be closed any time prior to or on the date of expiration. Column C is counting down the days to expiration until it reaches 0. What I would like to do is count up the days in column D that the contract is open from the open date until today as long as it remains open, and then stop counting once the cell in column E is filled in.

I hope that is clear, and thanks in advance for your help!

 

[etaf]

=if( E2="", TODAY()-A2, "")
if you need it to keep the number of days - then you may need VBA - or a timestamp type formula, modified to stop the today()-a2 calc

How to insert timestamps in excel sheet using formulas

Often when you use excel to track a particular item (like expenses, exercise schedules, investments) you usually enter the current date (and time). This is nothing but timestamping. Once the item is time stamped, it is much more easier to analyze it. Here is an excel formula trick to generate...

chandoo.org

https://howtoexcelatexcel.com/blog/create-a-timestamp-in-excel-with-formulas/

do you want a number of days or just blank as shown

 

[jdpro]

Yes thanks etaf, I would like it to keep the number of days once the contract is closed. How should I enter that formula?

 

[etaf]

ignore - will answer in another post

 

[etaf]

so in D2
=IF(E2="",today()-A2,D2)
Then you need to select and allow iterations
have a look at the website i posted on how to do that

seems to work ok for me - I tested with a different cell , rather than today - so i could see if once e2 has a value it freezes and it does

Book1
	A	B	C	D	E
1	Open Date	Expiration Date	Days to Expiration	# Days	Close Date
2	10/10/22	10/21/22	0		10/13/22
3	11/17/22	12/16/22	13	16
4
Sheet1

Cell Formulas
Range	Formula
C2:C3	C2	=MAX(0,B2-TODAY())
D3	D3	=IF(E3="",TODAY()-A3,D3)

 

[jdpro]

Thanks, I looked at the website but have trouble figuring out from there what I need to do here.

I would like to have the total number of days that the contract was held once the close date is filled in. Using =E2-A2 returns the 3 days that the contract was open.

But while the E cell is still empty, I would like to have a running count in the D cell of the number of days the contract has been open. Using =TODAY()-A2 in the D cell returns the 54 days that have elapsed since that contract was opened. I want that result for a contract that's still open, but not for one that has been closed.

So the mission, if not impossible, would be to come up with the VBA that can marry these two.

 

[etaf]

Using =TODAY()-A2 in the D cell returns the 54 days that have elapsed since that contract was opened.

Thats whats in the cell and will calculate all the time E is blank
BUT when its closed and a date is entered into E
=TODAY()-A2
What do you want in D ?

so
IF( E2="", TODAY()-A2, "")

then when a date is entered into E - the cell in D will be blank

so
IF( E2="", TODAY()-A2, "")

will work

BUT say D2 showed 23 days - since the contract open to today
and then you add the closed date into E2 = D2 will no longer contain 23 any more
if you want 23 to stay in the cell then you need to use
=IF(E2="",today()-A2,D2)

But also you have to set excel to do circular references other wise you will get an error - and those websets show how to set excel to allow circular references called iterations

if you are happy that once a date is entered into E2 , then D2 turns blank
then simply use

IF( E2="", TODAY()-A2, "")

 

[jdpro]

You ask, "What do you want in D?"

I would like D to show the number of days a contract is or was open.

There are two conditions.
First Condition - E is empty. I want D to show a count of days since the date in A. It will keep counting up until E is filled.
Second Condition - E is filled. I want D to show the number of days between the dates in A and E. That number will not change once E is filled.

Hopefully that makes it clearer.

 

[etaf]

ok,

see screen shots on how to setup iterations on a macOSX - if windows - then the websites I posted

in D2
=IF(E2="",today()-A2,D2)

But if you dont put on iterations , then it will give an error of a circular reference

all the time E2 is blank - then d2 will show the calculation today()-A2 and so increment every day

Now when the cell E has a date entered , the formula will show D2 - BUT thats a circular reference , and so inorder for that to work and just keep the number of days - you need to set up the iterations

i'm on a mac OSX

and so here are some screen shots

First - goto EXCEL in the top menu - Preferences
Then select > calculation
Then - tick > use iterative calculation - Leave on 100
Now it should work

 

[jdpro]

Man, I'm really sorry etaf, but I'm not understanding these instructions or seeing how the result should appear. I do appreciate your efforts to help, but I think I'll sit on it a while and maybe repost another time perhaps with a better constructed question. It looks like others haven't chimed in with anything different. Sometimes I find that getting away from a problem for a while can help. Thanks much.