Days Tracker in Excel

[Peteor]

Hello! I would like to do something in Excel I don't believe is possible. I would like to count the number of days a cell has a value.

E.G. - Assume the values below have not changed for 10 days. I would like to count the number of days the cell has not been blank.

Action resides with John Doe	Days action resided with John Doe
	0
X	10
	0

 

[VBE313]

Does the user open this everyday? Or do you want this to trigger regardless of opening in the document?

 

[Peteor]

I do open the tracker everyday.

 

[Peteor]

*Bear in mind that the count may not be continuous in nature as well*

E.G.
9/1/2020 - 9/10/2020 - Action resided with John Doe
9/11/2020 - 9/15/2020 - Action did not reside with John Doe
9/16/2020 - 9/21/2020 - Action resided with John Doe

I simply want to count the total days action resided with John Doe.

 

[VBE313]

Maybe something like this...

Sub Workbook_Open()
Dim numOfDaysWithSameValue As Long
With Sheets("Sheet1")
    If .Range("F2").Value = .Range("A3").Value Then
        numOfDaysWithSameValue = Date - .Range("E2").Value
        If numOfDaysWithSameValue > 0 Then
            .Range("B3").Value = .Range("B3").Value + numOfDaysWithSameValue
            .Range("G2").Value = .Range("B3").Value
            .Range("F2").Value = .Range("A3").Value
            .Range("E2").Value = Date
        End If
    Else
        .Range("F2").Value = .Range("A3").Value
        .Range("E2").Value = Date
        .Range("G2").Value = 0
        .Range("B3").Value = 0
    End If
End With
End Sub

 

[Peteor]

I see. I was hoping to avoid the macro. Thank you though

 

[VBE313]

I see. I was hoping to avoid the macro. Thank you though

Do you have Microsoft 365 E3 or E5?

 

[Peteor]

I am unsure. I am currently running Excel 2016.

 

[VBE313]

Then I am not sure how you can do this without using VBA.