Time formula if great than 15

[Patriot2879]

Hi i hope you can help i would like a formula please. i have cell A1 which time stamps todays date and time when data is inputted in another cell. B1 has a 'yes' or 'no' in a list which gets selected. What i require is when A1 is updated with a time stamp and cell B1 = 'Yes' i want cell C1 to put in the time for when B1 was updated. Then in D1 i want a calculation to work out the time difference between cell A1 and C1 and if this is greater than 15 i want cell A1 to change to red colour. I hope this all makes sense and that you can help please?

 

[Patriot2879]

Hi good morning, column R has the initial timestamp, Column X has the listbox for Yes and No, Column AC is where i want the additional timestamp to be placed when Yes or no is selected from column X, and then if the time difference is greater than 15 minutes between R and AC i want column R to change to RED.
Basically what i have is a time stamp is created in columm R and once data is entered i then have 15 minutes to complete the job, then i select yes or no from column X, so as soon as yes or no is selected from column X i want the new timestamp to be created in column AC, then to do the calculation between the 2 timestamps in column R and AC and then if i took longer than 15 minutes to complete the task i want the timestamp in R to turn RED. I hope this makes sense.

 

[CountTepes]

You nearly got it right. Most probably a typo.

Looking back at your changes to the code. Where TimeDiff is set up, you changed the two columns to the same value.
You were in fact looking at the original time stamp and subtracting it from itself and not the new time stamp.

If Target.Column = 24 And (UCase(Target.Text) = "YES" Or UCase(Target.Text) = "NO") Then   ' Check correct column and If Yes or No
        Cells(Target.Row, 29).Value = Now                                                                                ' Timestamp current date and time
        Cells(Target.Row, 29).NumberFormat = "dd/mm/yyyy hh:mm"                                    '  Make sure correct date time format
        TimeDiff = Format(Cells(Target.Row,  29).Value2 - Cells(Target.Row,18).Value2, "hh:mm:ss") ' Subtract start time from end time ------------- This is where the error was.
        If TimeDiff > "00:15:00" Then Cells(Target.Row, 18).Interior.Color = vbRed     ' Check timediff is greater than 15 min then colour a cell red
End If

 

[Patriot2879]

Hi I have changed it to the below but still its doing nothing at the moment. can i attach you the folder?

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Rw As Range
   Dim TimeDiff
If Target.Column = 24 And (UCase(Target.Text) = "YES" Or UCase(Target.Text) = "NO") Then   ' Check correct column and If Yes or No
        Cells(Target.Row, 29).Value = Now                                                                                ' Timestamp current date and time
        Cells(Target.Row, 29).NumberFormat = "dd/mm/yyyy hh:mm"                                    '  Make sure correct date time format
        TimeDiff = Format(Cells(Target.Row, 29).Value2 - Cells(Target.Row, 18).Value2, "hh:mm:ss") ' Subtract start time from end time ------------- This is where the error was.
        If TimeDiff > "00:15:00" Then Cells(Target.Row, 18).Interior.Color = vbRed     ' Check timediff is greater than 15 min then colour a cell red
End If

   Application.EnableEvents = False
   If Not Intersect(Target, Range("H:H")) Is Nothing Then
      For Each Rw In Target.Rows
         Cells(Rw.Row, "Q").Value = Environ("Username")
         Cells(Rw.Row, "R").Value = Now
         Cells(Rw.Row, "R").NumberFormat = "dd/mm/yyyy hh:mm"
      Next Rw
   ElseIf Not Intersect(Target, Range("AA5:AA70")) Is Nothing Then
      If LCase(Target.Value) = "cancel all" Then
         Range("S" & Target.Row).Resize(, 4).Clear
      ElseIf LCase(Target.Value) = "cancel 1" Then
         With Range("S" & Target.Row).Resize(, 4)
            .Value = Evaluate("if(" & .Address & "<>""""," & .Address & "-1,"""")")
         End With
      End If
   End If
   Application.EnableEvents = True
End Sub

 

[Patriot2879]

Hi i have added my link to the database hope it makes sense
test.xlsm

 

[CountTepes]

I can't download to this computer. It's blocked.

But I have just tested the code I gave you, it worked.
Put a breakpoint in at the IF statement and step through the code, watching the values, when you type yes or no into column X

 

[Patriot2879]

hi how do i do a break point? what is that? the new timestamp doesnt go into column AC either, dont know what im doing wrong if it works for you

 

[Patriot2879]

I got it working, but it doesnt work if i use the list box which i put in through Data validation, does that mean i cant do it this way and have to type Yes or No in by hand?

 

[CountTepes]

Put your cursor in the code and hit F9.

Wherever your cursor is, that line will have a breakpoint, which you will see as a red dot in the margin.

You can put as many as you want in code.

Pressing F8 will step through the code. Pressing F5 will make the code run, until the end or the next break point.

You will be able to see the values in the Locals Window. So if a test fails, for example, If X=6 then. You can stop the code at that line and inspect the value of x, either bu looking in the locals window, or hovering over X with the mouse, which will display the value. So if you can see that the value 6 , from which X is picking up its value. You may at that pint see that the 6 is test and not numerical.

Breakpoints are an invaluable tool for debugging code.

Are you sure that your form is not disabling events, whilst it is running? If it is, then the worksheet_Change event will never occur.

 

[Patriot2879]

What i have done to test it is remove my list from column X and typed in yes or no manually and it works.
Is there any way it will work with my list for yes or no? which i put in through data validation then created my list for dropdown.

 

[Patriot2879]

its sorted, i found the issues i put a full stop after yes and no in the drop down list it works great now many thanks for your help on this you have been amazing thank you