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]

ok,
not sure how i can explain further .........
I dont think you have constructed badly - i fully understood the post below

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.

1) Setup the excel preferences to allow iterations , by ticking the box as shown by the screen shots - that will allow the following formula to work as required
2) In d2 enter =IF(E2="",today()-A2,D2) , now it will count the number of days from a2 to TODAY() and change every day - as soon as you enter a date into E2 - it will stop counting and just display the number of days upto the day you entered a date in E2
IF you dont setup the iterations - then you will be get a circular reference error - as D2 is trying to enter D2 itself.... the websites i posted also explained in full the reason

anyway - fine if you want to ask again.. no problem at all , when posting , hopefully i will remember and not just repost this reply again and confuse you further

 

[jdpro]

Thanks for everything, and for summarizing your instructions. I tried again and made the adjustment to use iterative calculation, and the shot below shows what I entered and what it returned. So it is taking care of one of the conditions, but the other condition returns 0 days.

Maybe we're halfway there.

 

[jdpro]

I was able to find a formula to enter into the D cell that worked: =MIN(E2,TODAY())-A2

 

[Peter_SSs]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Counting Days Until Closing Date is Entered [SOLVED]
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[jdpro]

Thanks Peter, will do. This is my only cross post on any topic.

 

[etaf]

ok, sorry I did understand then , I thought we were only dealing with a condition in D...

sorry , glad you found the solution which worked -

you dont need to set up the iterations on that sheet any more
as
=MIN(E2,TODAY())-A2
even tough today()-a2 will continue to count forever , the min using E now it has an entry will override

 

[jdpro]

Etaf, I am really grateful for all the time and effort you put into this problem. Thanks agin.

 

[etaf]

you are very welcome