Number of days a cell has a particular value
Closed
Bradley
-
Nov 18, 2009 at 04:32 AM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Nov 18, 2009 at 08:39 PM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Nov 18, 2009 at 08:39 PM
Related:
- Number of days a cell has a particular value
- Skype last seen days ago - Guide
- Add 90 days to a date - Guide
- If cell contains date then return value ✓ - Office Software Forum
- It usually takes us just over a day to review your information - Facebook Forum
- You have 179 days left to request a review ✓ - Facebook Forum
1 response
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Nov 18, 2009 at 08:39 PM
Nov 18, 2009 at 08:39 PM
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
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
