For example:
A1 =today()
B1 Incident Date mm//dd/yyyy
B2 Number of Days since Incident Date
B2 30 (Days Past) =if(B1>A1+30,"X","")
B3 60 (Days Past) =if(B1>A1+60,"X","")
B4 Status "Current", "Pending", "Closed"
I realize if I copy paste special value, but how would I get the counting to stop the counting based on the Status ("Current" etc.)
Thanks,
Jim
Welcome to the forum!
I have read your post three times and am struggling to visualise what you want. Can you give a specific example about how you want something to stop counting? Attach a sample workbook, please.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Stop counting in cell B2- Number of Days since Incident Date
Stop counting when??? What is the criterion?
Maybe this:
=IF(A1-B1>60,60,A1-B1)
or:
=MIN(60,A1-B1)
Last edited by AliGW; 10-29-2017 at 06:39 AM.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
I think I need VBA. Originally posted in that forum.
Your thread has not been moved - why do you think you need VBA?
Maybe this in A1?
=IF(TODAY()-B1>60,B1+60,TODAY())
I want to do an automated Copy/PasteSpecialValue
But today() will keep updating. I want it to stop when the incident is closed.
Shall I move your thread?
Your tone is very demanding - you do realise we are all volunteers here offering you support in our free time, don't you?
It won't if you tell it not to. See post #8.But today() will keep updating. I want it to stop when the incident is closed.
I sincerely apologize. Really. I didn't realize it. Very sorry. Will try to do better.
Have a look at this:
Excel 2016 (Windows) 32 bit
Sheet: Sheet1
Excel 2016 (Windows) 32 bit
Sheet: Sheet1
Last edited by AliGW; 10-29-2017 at 07:33 AM.
I see what you are saying. I guess I would like the today() to be a value so it will automatically stop the counting.
p.s. I've posted with you in the past. You helped me out with a index/match that I just couldn't get. I admire your knowledge and would love to know excel better.
So do you want to stop the counting, or freeze A1 after 60 days, or both?
I help a lot of people - I am sorry that I didn't 'recognise' you.
These two formulae will both stop (freeze their value) once 60 days are reached:
Excel 2016 (Windows) 32 bit
Sheet: Sheet1
Excel 2016 (Windows) 32 bit
Sheet: Sheet1
If you enter the DATE when an incident is closed into cell B5
- then put this formula in cell B1
Formula:
Please Login or Register to view this content.
counting stops when incident closed
Click *Add Reputation to thank those who helped you. Ask if anything is not clear
It's okay...I realize you do help many people. I am the guru amongst my excel newbie friends.
But I really would like to know what date it was closed. And the days passed since opened. It could be 60, could be 40 could be 100.
hmmm...that is an interesting min function use. I thought it only referred to ranges.
The problem here actually is the missing information. We don't know what the criteria for this are:
Jym - I don't know who you are. You seem to have only today's 7 posts associated with this account.B4 Status "Current", "Pending", "Closed"
- you must input that dateBut I really would like to know what date it was closed
- Excel will not remember when you changed the value in status to "Closed"
- see post#18
Last edited by kev_; 10-29-2017 at 07:59 AM.
Got it. Could you please help with how that min function is used the way you did it?
The other thing you could do is have a checkbox associated with a cell that is referenced by A1 - once it's ticked, A1 stops increasing.
Whom are you addressing, Jym - me or Kev?Got it. Could you please help with how that min function is used the way you did it?
Kev I guess...sorry
Actually thought about a checkbox. Would like that because I am writing this for someone else..
Kev. Could you explain how you used that min function? I thought it only worked on ranges.
Just realised I need to go somewhere - hopefully Kev will help you conclude this - sorry!
Last edited by AliGW; 10-29-2017 at 08:03 AM.
=MIN(value1,value2,etc.)Originally Posted by Jym396
The values can be single cell references, values or a range of cells.
Quite alright. Be careful wherever you are going And...thanks!
I have been watching this thread and am also confused by your explanations
Have a look at the attached workbook
- if that is not what you want then please attach a workbook with the values you do want with an explanation of how to derive them
To attach a workbook
- click on GoAdvanced
- look below for ManageAttachments
- follow screen instructions
Last edited by kev_; 10-29-2017 at 08:15 AM.
Could you explain how you used that min function? I thought it only worked on ranges.Formula:Please Login or Register to view this content.
You can also use =MIN to select the minimum from a list of values separated by comma
- the answer to the above is obviously 1
- enter the above formula in a cell and change the values and see what happens
Also works with =MAX, =AVERAGE, =SUM
Very helpful Kev...thank you so very much.
Jim
Please close this thread. I don't know how.
Thank you both so much!
Jim
Did you look at post 32 - is it what you want?
Go to thread tools at top of thread and mark thread as SOLVEDPlease close this thread. I don't know how.
The download really helped!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks