Hi Dears,
I need your support on following:-
I need to have a counter of days in Excel sheet, for example if text in A1 is “Normal” than the counter in B1 should start counting number of days based on date change and cell B1 should show number of days passed. If text in A1 is another text than “Normal” counter should stop and cell B1 should show “0” till the text is changed back to “Normal” in A1. If text is again changed to “Normal” in A1 counter shall resume counting from “0”.
View attachment 153
Looking forward to get your reply soon.
Regards
Greetings,
I think I understand your problem,Below I am going to present a sample code which will help your cause.You have to place the code in the worksheet, not in the module(shown in the image below).
First, right click on the
sheet1 name in the workbook as shown in the image.
And then click on the
View Code.
Then in the code editor window, paste the following code as shown in the image.
Code:
Public carry As Date
Private Sub Worksheet_Change(ByVal Target As Range)
Dim start_date As Date
Dim day_counter As Integer
If Target.Address = "$A$1" Then
If UCase(Target.Value) = "NORMAL" Then
carry = Date
Debug.Print carry
Else
ActiveSheet.Cells(1, 2).Value = "0"
End If
End If
End Sub
Private Sub Worksheet_Activate()
If ActiveSheet.Cells(1, 1).Value = "Normal" Then
If Month(carry) = Month(Date) And Year(carry) = Year(Date) And Day(carry) = Day(Date) Then
ActiveSheet.Cells(1, 2).Value = 0
Debug.Print "Success"
Else
ActiveSheet.Cells(1, 2).Value = DateDiff("d", carry, current_date)
dday = DateDiff("d", carry, current_date)
Debug.Print dday
End If
End If
End Sub
Now how you use the code is crucial. There is subtle change of how you describe the problem. In this code, if you enter
Normal in cell
A1, then the code will start counting, and after couple of days when you open the worksheet again(say 10 day later), you will see the 10 in cell
B1.
Now if you switch back to other word in the cell
B1, then you will see the counter in cell B1 now set back to
0.
Now if you set it back to Normal, the counter will set to 0 again.Here one think need to be consider,
you gotta select sheet2 and again swithc back to Sheet 1 in order to see the updated value. The coding is done is such a way that
if you want to see updated value, entering value is not enogh, you need to reopen the Sheet1 sheet to see the change in counter. This is done in order to maintain dynamicity of the code.If you have nay confusion of this, feel free to comment, I will try my best.
Moreover, it will be best to add a button to execute the code instead of dynamically updating. So let me know if you can consider changing your requirements a bit, in that way it will be easy for you to understand and implement the code.
If you have any problem, let me know.
