Formula to stop the counting based on date

Hi to all wizards again!

I have this problem that would really need your help.

I have cell A1 with a value of 4/3/2012 and cell B1 with a formula =TODAY()-A1

So, cell B1 there will continuously count the number of days with reference from cell A1

Now, I want a formula on cell C1 that if I enter the current date then cell B1 will stop counting.

Somebody please help.

Thanks.

Hi Sir Fotis,

It will stop counting if I enter the current date on cell C1.

Thanks.

Hello Sir dilipandey,

It seems that this formula won't work on my sheet.

Hi sorry to all, below should be the problem:

I have cell A1 with a value of 4/3/2012 and cell B1 with a formula =TODAY()-A1

So, cell B1 will continuously count the number of days that had elapsed(as of today B1=41) with reference from cell A1 fixed date

Now, I want a formula on cell B1 that if I enter the current date in cell C1 then cell B1 will stop counting AND IT WILL REMAIN 41 if date will move forwards in my PC system..

Somebody please help.

Thanks.

Hi Sir Fotis,

I still having a problem with the formula, value was disappered. Kindly check the attachment.

Thanks.

j.

Hello again Sir Fotis,

Yup I had tried that, I just wondered why instead of having it 41 days then why it became 40 days?

Well, still I could not have to take a test on the formula cuz I can't change the date of my system to tomorrow's date as its lock by our admin. Then I have to wait for tomorrow and see if counting will continue or not

Thanks a lot! I appreciate.

Hi Sir Fotis,

I have tried this at home now, and when I set my system's date forward column will still continue to count.

Hi Sir Pike,

I had tried your formula but had no idea about VBA and I'm not educated about it. I tried opening the UI of VBA but I'm not really sure on how to do it correctly. Hope you can guide me.

Thanks a lot!

What was wrong with Dilipandey's solution? It assumes column C is empty until you put in a date, which seems reasonable. The problem is that 'today' is a moving target. Can you have the spreadsheet be:
Column A: Your Start date
Column B: Your count of days from start date to today or the date in column C (depending on whether C is populated or not)
Column C: Your End date or blank

To tie your formula to stop counting only if the value in column C is 'today' will only be true for that day.
What would column C represent if not an end date?

Pauley

You are right Mr. Pauleyb. And now I just use this kind of formula on column B:

=IF(ISNUMBER(C1),SUM((C1-1)-A1),TODAY()-A1))

We can't really shoot a constantly moving target.

Or, if you want B2 to stop counting ONLY IF the date entered in C2 equals the current date at the time the date is entered, then try:
=IF(C2=TODAY(),C2-A2,TODAY()-A2)

Again, making sure not to use =TODAY() in column C

Thanks again Pike,ATLGator & xladept!star,star & star...

We'll see then and try all your suggestions and we'll give you feedbacks. I thanked you all a lot!

Hi! ALTGator & xladept...this formula won't work when I tried it. The counting on cell B2 still continues when the date moves forward.

please people in the house , help me, i need help plsssssssssssssssssssssssssssssssssss

This is the question:
i have a date that i supplied goods to a customer e.g 17/7/2013 in cell A1 (date supplied) and the total amount of the goods supplied in cell B1, which the customer has 30 days for the payment to expire, so i need a formula that will countdown each day(i.e from 30, 29, 27 ---------0 and it will bring out the amount as well (i.e 4 days remaining to paid up the value which is 2000). also i have a table in excel which goes like this: Name of Customer/ Amount Paid/ payment due date /0-5 days/6-20 days/21-40 day. so when the decrease fall on 6-20 days, the payment reminder should move to 6-20 day. When it reaches the deadline day, which is 30 days the reminder should move to payment due.

Note: the countdown should always be automatic update without me opening my system always.
Here is my attached format in excel sheet.

Thanks
Alli Adejumoke