The date a cell is modified

[Maddoktor]

Hi all,

I am hoping someone can help me with the following problem.

I have an excel spreadsheet with 5 columns.

Column A - Date
Column B - Number
Column C - Colour
Column D - Size
Column E - Date last modified

If i was to enter the following information:

20/08/04 999999/88 red 550 ?

I would like formulas or macros to do two things. The first is to count the
number as two seperate numbers when it is seperated by the "/" sign. ie.
999999/88 means 999999/999988. If I was to use the count function as an
example, the answer should be two for the amount of pieces.

The second part is for possibly another macro to insert the date a cell is
modified. I would like to protect this sheet with a password so the date
will need to be inserted into a protected cell. The date is to be the
current date the cell was modified.

Thank you in advance for your help.

 

[Bob Phillips]

=LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))+1

and

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
If .Column = 2 Then
.Offset(0, -1).Value = Date
.Offset(0, -1).FormatNumber = "dd mmm yyyy"
End If
End With

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Maddoktor]

Thanks for the quick reply.

Is it possible to use the formula for a range of numbers in a single column?
ie. A5 - 999999/88, A6 - 999977, A7 - 999966/55 equals count of 5?

 

[Bob Phillips]

Try this

=SUMPRODUCT(--(LEN(A1:A100)-LEN(SUBSTITUTE(A1:A100,"/",""))))+COUNTA(A1:A100
)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Maddoktor]

Thank you ... it worked great. Thanks again for the quick replies.

 

[Bob Phillips]

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

Caught me in my lunch-break<vbg>

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Maddoktor]

Bob ... hope i am lucky enough to catch you on your lunch break again

I would like to take it a step further with my previous question. If i was
to use the same data and added the following:

20/08/04 999999/88 red 550
21/08/04 888888 blue 500
22/08/04 777777/60/70 red 550

The formula to count the pieces works perfect but i would now like to count
the number of pieces for all the color of "red". i.e the number of pieces
should equal 5. The formula should also be able to be manipulated if i was
not to chose "red: but "550" instead.

Thanx again.

 

[icestationzbra]

slight addition to bob's formula, seems to be working for me.

=SUMPRODUCT(--(LEN(A1:A100)-LEN(SUBSTITUTE(A1:A100,"/","")))*(B1:B100="red"))+COUNTIF(B1:B100,"red")

in my test file, i had colours in column B. you can change it to th
the column that you have colours in. replace "red" with anything else
while change the columns too. the formula should hold good.whe
entering numbers, do not use quotes.

for example:

=SUMPRODUCT(--(LEN(A1:A100)-LEN(SUBSTITUTE(A1:A100,"/","")))*(C1:C100=55))+COUNTIF(C1:C100,55

 

[Maddoktor]

Thanks ... I modified it a little and it worked great.

The final problem is counting what gets done for a particular date. I have
been using the 'count' to count each time a date is entered into the cell
and it works correctly telling me how many days has passed. What I would
like to do is modify the formula so that i can not only add the number of
days that have passed but how many pieces were done for each particulr day.
i.e.

column A = 01/01/04 100000 red 550
column A = blank (means same as 01/01/04) 200000/10 red 550
column A = 02/01/04 300000 blue 500

Therefore if i was to find how many pieces were done for a particulr date
i.e. 01/01/04 the answer should be 3, incorporating the second line as the
date is the same as the above row.

Or if i was to ask the question how many 'red' pieces were done and the
number of days it took the answer would be 3 pieces and 1 day remembering
that the blank cell indicates the cell above that has a date input.

Is it possible for a formula to be written for the above question?