Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: One more question about Excel

  1. #1
    New Member
    Join Date
    May 2002
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!!!

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •