Can I stop # of days counter when I fill a cell?

October 15, 2019 at 08:53:39
Specs: Windows 10
Im using the formula:
=IF(AE29="",TODAY()-H29,AE29-H29)
To count the number of days (In column A) since the specified date (In column H). I would like to be able to enter the word "CLOSED" in column D and have column A stop counting at that point, but maintain current data. If placing an actual date in column D would make it easier, I'd accept that too.

See More: Can I stop # of days counter when I fill a cell?

Reply ↓  Report •

#1
October 15, 2019 at 09:20:49
If the cell in Column D is normally empty, then entering the "Closed" date in Column D might be the easiest:

=IF(D29<>"",D29-H29,IF(AE29="",TODAY()-H29,AE29-H29))

Even if Column D does contain data, some variation of that IF formula might work.

Another option is a Worksheet_Change macro that monitors Column D and locks the numeric value in Column A when Closed is entered in Column D.

Retain your original formula and paste the following code into the Sheet module for the sheet with your dates. One possible issue with this method is that if whatever you Closed needs to be Opened again, the formula will need to be put back in the cell. I included an instruction to do that.

Obviously I don't know your work process or how you use the sheet, so this code may need to be modified.

Private Sub Worksheet_Change(ByVal Target As Range)

'Determine if Closed was entered in Column D
  If Target.Column = 4 Then
    If Target = "Closed" Then
'Replace formula in Column A with current value
      Cells(Target.Row, "A") = Cells(Target.Row, "A")
    Else:

'Replace value with formula if Closed is not in Column D
       Cells(Target.Row, "A").Formula = "=IF(AE29="""",TODAY()-H29,AE29-H29)"
    End If
  End If

End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#2
October 15, 2019 at 10:15:11
Your first suggestion worked like a charm! I pasted the macro, but will have to wait til tomorrow to see it if worked. Thank you so very much!!

Reply ↓  Report •

#3
October 15, 2019 at 11:28:54
I assume that you are aware that my suggestion was either the formula or the macro, not both.

In any case, why would you have to wait until tomorrow to see if the macro worked?

If you enter Closed in Column D and then click the cell in Column A, you should no longer see the formula, just a hard coded value. Delete "Closed" and the formula should come back. If the formula is not in the cell, the value can't change. I tested both the new IF formula and the code.

If you need to actual test a date, you can change the system date since that is what Excel uses to determine what date the TODAY() function uses. Granted, that may momentarily impact things like a calendar. I've had past and future reminders pop up when I've altered the system date, but I've never seen any lasting impact as long as you remember to set the date to the real "today" when you are done testing. Bottom line is that you can make it "tomorrow" today if you want.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

Related Solutions

#4
October 15, 2019 at 14:51:29
I was going to wait until tomorrow because I don't have admin rights on this machine and cant change the date and time.

Yes, I understood it was either or. I'm testing in two separate tabs. But it looks like the macros stays with the entire workbook and cant be isolate to one tab, is that correct?

Well, changing Cell D to CLOSED is not removing the formula from Cell A. Assuming (because I suck at this) that this macro is supposed to affect the whole sheet/book and not just Row 29, should I be making changes to the part that reads:

'Replace value with formula if Closed is not in Column D
Cells(Target.Row, "A").Formula = "=IF(AE29="""",TODAY()-H29,AE29-H29)"
End If
End If

...or any where else?
Thanks


Reply ↓  Report •

#5
October 15, 2019 at 20:21:49
You are correct. The formula needs to be edited to reference the Target Row, not Row 29.

My error, I was rushing. I'll fix it tomorrow as I don't have access to Excel (for testing) right now.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#6
October 16, 2019 at 06:58:52
First a posting tip:

Please use the pre tags whenever you post VBA code. That will keep the indents and alignment intact and make it easier for us to read. (Click the link at the bottom of this post for instructions.)

Second, regarding this statement: "I don't have admin rights on this machine and cant change the date and time."

Just curious, did you try it? I don't have admin rights on my corporate machine, but I can change the date and time. I know that there are many different levels of "corporate lockdowns" when it comes to desktops, so I'm just curious about your ability to change the date and time. You might be surprised.

Finally, try the following version of the macro.

Please note that "closed" is not the same thing as "Closed". We can make it the same, but for simplicity of code at this point, I only used "Closed" as the trigger to replace the formula with the hard coded value. If you enter "closed" in Column D, you will not get the hard coded value.

Let me know how this works for you.

Private Sub Worksheet_Change(ByVal Target As Range)

'Determine if Closed was entered in Column D
  If Target.Column = 4 Then
    If Target = "Closed" Then
'Replace formula in Column A with current value
      Cells(Target.Row, "A") = Cells(Target.Row, "A")
    Else:

'Replace value with formula if Closed is not in Column D
       Cells(Target.Row, "A").Formula = _
             "=IF(AE" & Target.Row & "="""",TODAY()-H" & Target.Row & _
             ",AE" & Target.Row & "-H" & Target.Row & ")"
    End If
  End If

End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#7
October 16, 2019 at 08:02:54
According to your description of what should happen (Once D is changed to "Closed", the code should disappear from A) that is exactly what happened :)

I assume you were telling me that my input is case sensitive, in that "Closed" and "closed" are not the same? So, I changed the code to "CLOSED" since that's how I like it :) and it works! By the way, I also noticed that if I remove "CLOSED" (as to reopen the case) the original counter code automatically repopulates column A - BRILLIANT!

By the way, yes, before contacting you, I attempted to change the date and time while testing other failed efforts, with no luck. They have stuff locked-down around here.

Thanks again for all of your help. You saved me HOURS of heartache!


Reply ↓  Report •

#8
October 16, 2019 at 08:44:46
I'm glad it worked out for you. Come on back if you have any other questions.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

Ask Question