Number of days a cell has a particular value

Closed
Report
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
Hello,

I am using Excel 2007 and would like a cell to display the number of days that a cell has a particular value.

i.e. number of days a project has been at a status of 'In Progress' or 'awaiting approval'.

I have no clue how to do this, can you help??

1 reply

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
my version is windows xp excel 2002. I suppose this event code will work in excel 2007 also.
let me explain my logic
A1 has column heading
when you start the project you type in A2 or any cell down
in column A(column A only) the entry
"in progress"
immediately(automatically) in B2 the date on which you type "in progress" will be entered and remain so(the date will not change) in subsequent days
But C2 will go on changing giving the system date
D2 will give you the difference between the two dates, that is the number of days elapsed.
On the first day D2 will obviously be 0(zero).
may be clumsy logic and clumsy event code. see whether it works. I cannot test it unless I wait for a few days.

remember if you have typed "in porgress" in A2 for the first time you should not mess with that cell.

now where the event code is to be parked?

right click the sheet tab and click view code. in that window which comes up copy paste this event code.

EXPERIEMNT WITH A NEW WORKBOOK FIRST AND NOT IN YOUR ORIGINAL WORKBOOK.

If you are satisfied with the event code then you can copy the event code to your original file as instructed above

post feedback after a couple of days.
The event code is

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Column <> 1 Or Target.Address = "$A$1" Then GoTo subexit
If Target <> "" Then
Target.Offset(0, 1) = Date
Target.Offset(0, 1).Copy
Target.Offset(0, 1).PasteSpecial xlPasteValues
Target.Offset(0, 2) = Date
Target.Offset(0, 3) = Target.Offset(0, 2) - Target.Offset(0, 1)
Target.Offset(0, 3).NumberFormat = "0"
End If
subexit:
Application.EnableEvents = True
End Sub