Stop counting cells when status is complete

[estanden]

Hi All,

Really need your help as I am pulling my hair out on how to do this!

Basically I have a column (F) which is has a date in for 'Date Response Required By' , and column (I) has 'Impact against programme'.

Column 'I' has the formula input into the cells to work out the programme impact, this is as follows: =[@[Date Response Required By (As per accepted programme)]]-TODAY()

Ideally, once column (O) says 'Closed' instead of 'Open', 'Overdue' or 'In progress' I would like column (I) to stop counting down any further but keep the number of days which have lapsed since column (F).

I have attached a snapshot below with a few columns hidden for ease.

I have added column (P) and inputting the date of when it the query is closed - As a few other forums have said this is required but if I can avoid using it that would be great as it does create extra workload inputting it.

Let me know if you need anything else to help me

Thanks all!

 

[alansidman]

=if(O2="Closed", P2-F2, F2-Today()). I have not used your field names, as I was too lazy to type them out. Try this substituting the field names for the cell addresses and see if that works for you.

 

[estanden]

=if(O2="Closed", P2-F2, F2-Today()). I have not used your field names, as I was too lazy to type them out. Try this substituting the field names for the cell addresses and see if that works for you.

Hi Alan,

Thank you - This does seem to work for the majority!

Only one problem, when the date in column P is past the date in column F, when it is then closed I would like it to show as a number less than 0. If the date we close the query, and get a response is within period / prior to the date in column F the it needs to be number greater than 0.

For example,

If the date the response is required is 31st July 2024, and we got a response and closed the query on 7th August 2024. I would like it to show as -8 days, rather than 8days.

Is there a way this can be done?

Basically I need to be able to clearly identify which queries were closed late (shown by - days past), compared to responses in date which show as positive numbers).

 

[alansidman]

Post your sample data with information that is representative of your actual data using XL2BB so that we can test appropriately without having to reconstruct your data ourselves.

 

[estanden]

Post your sample data with information that is representative of your actual data using XL2BB so that we can test appropriately without having to reconstruct your data ourselves.

HLN 0290 NPY_BAM RFI Register - Open in Browser Only.xlsx
	B	C			F			I						O
748	739	ELLA TEST 1			27 August 2024			19.00						Closed
749	740	ELLA TEST 2			03 July 2024			-36.00						Closed
750	741	ELLA TEST 3			10 August 2024			2.00						In progress
RFI Live Register

Cell Formulas
Range	Formula
I748:I750	I748	=[@[Date Response Required By (As per accepted programme)]]-TODAY()

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I10:I759	Cell Value	>28	text	NO
I10:I759	Cell Value	between 14 and 28	text	NO
I10:I759	Cell Value	between 1 and 13	text	NO
B9:P759	Expression	=$O9="Superseded"	text	NO
B9:P759	Expression	=$O9="Closed"	text	NO
I10:I759	Cell Value	<1	text	NO