One more question about Excel

[mcleve]

Thank you so much for all your help. This board is filled With extreme intelligence. A very good resource!

I have reviewed my problem and have one more question.

If cell A1 has a Dynamic Data Exchange entry of either 1 or 0. Is it possible that if A1=1 then put a timestamp of when the cell A1 went to 1 in B1 and the word "started" in cell c1. when cell A1 goes to 0 then put a timestamp of when A1 went to 0 in B2 then the word "stopped" in cell C2 and so on.

I am trying to log uptime and downtime of a peice of equipment. What time the machine stopped and what time it started back 1=on 0=off.

I hope that I am not being too much of a Nuisance. and Thank you all again for all your help!!!

[Tom Schreiner]

Hi
This code will record a Date &
Time stamp in column B
"Started" or "Stopped" in column C
"Up-Time" or "Down-Time" in column D
And the amount of uptime or downtime
in column E in HH:MM:SS.
The formatting is as little tricky.
If you have any problems, I can email
you the workbook I tested it on.

See example:

         B         |   C   |      D      |   E

04-Apr-02 23:32:24  Started Insuff. Data    N/A

05-Apr-02 13:32:37  Stopped UP-Time         14:00:13

05-Apr-02 13:33:43  Started DOWN-Time       0:01:06

05-Apr-02 13:34:13  Stopped UP-Time         0:00:30






Copy this code, replacing the old:







Private Sub Worksheet_Calculate()

Dim NextRow As Long

Application.EnableEvents = False

If Range("A1") <> OldValueInA1 Then

    If Range("B1") <> 0 Then

        NextRow = Range("B1:B" & Range("B65536"). _

        End(xlUp).Row).Rows.Count + 1

    Else

        NextRow = 1

    End If

    OldValueInA1 = Range("A1")

    Range("B" & NextRow) = Format(Now, "DD-MM-YY HH:MM:SS")

    If Range("A1") = 0 Then

        If NextRow <> 1 Then

            Range("C" & NextRow) = "Stopped"

            Range("D" & NextRow) = "UP-Time"

            Range("E" & NextRow) = Format(Now - Range("B" & _

            NextRow - 1), "HH:MM:SS")

        Else

            Range("C" & NextRow) = "Stopped"

            Range("D" & NextRow) = "Insuff. Data"

            Range("E" & NextRow) = "N/A"

        End If

    Else

        If NextRow <> 1 Then

            Range("C" & NextRow) = "Started"

            Range("D" & NextRow) = "DOWN-Time"

            Range("E" & NextRow) = Format(Now - Range("B" & _

            NextRow - 1), "HH:MM:SS")

        Else

            Range("C" & NextRow) = "Started"

            Range("D" & NextRow) = "Insuff. Data"

            Range("E" & NextRow) = "N/A"

        End If

    End If

End If

Application.EnableEvents = True

End Sub







Tom