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?

 

[CountTepes]

what is the time difference. Months, days, hours, minutes?

Also what is the type of value in A1. Is it text?

Can you give an example please?

 

[jmacleary]

Hi there. It is not possible to have a formula which freezes a date/time when something is entered - you will need VBA for this. You say A1 is timestamped - this suggests a macro already exists to achieve that - but then the rest of your post becomes confusing. If you have a macro (I guess it will be a worksheet change event macro) please post it and we can see how it can be modified. If you don't have a macro, I don't understand how A1 gets 'timestamped'.

 

[Patriot2879]

Hi yes i have a macro for A1 (A1 is actually row R) i called it row a1 - d1 to be easier. So when data is entered in row a then q and r are updated with a username and timestamp, row x has a list box in the cell with 'yes' and 'no' what i need is when row r and x have been updated and row x = yes then i want row AC to be timestamped for when x was updated then have a calculation to work out the 2 times between r and ac and if greater than 15 minutes to turn the cell in x to turn red.

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Rw As Range
  
   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

 

[CountTepes]

At the beginning, put: I'm not sure if I have the correct Columns, but I'm sure you can sort it out.

Dim TimeDiff

If Target.Column = 24 And (UCase(Target.Text) = "YES" Or UCase(Target.Text) = "NO") Then
        Cells(Target.Row, 3).Value = Now
        Cells(Target.Row, 3).NumberFormat = "dd/mm/yyyy hh:mm"
        TimeDiff = Format(Cells(Target.Row, 3).Value2 - Cells(Target.Row, 18).Value2, "hh:mm:ss")
        If TimeDiff > "00:15:00" Then Cells(Target.Row, 24).Interior.Color = vbRed
End If

 

[Patriot2879]

Hi i have changed Target.Row to 29 thats for row AC to put in the other date to work out the gap in time between column 18 and 29 (Row R and AC), and changed the time difference to 18 for row R, but if greater than 15 minutes Ro3 R is not turning to red. hope you can help.

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
        Cells(Target.Row, 29).Value = Now
        Cells(Target.Row, 29).NumberFormat = "dd/mm/yyyy hh:mm"
        TimeDiff = Format(Cells(Target.Row, 18).Value2 - Cells(Target.Row, 18).Value2, "hh:mm:ss")
        If TimeDiff > "00:15:00" Then Cells(Target.Row, 18).Interior.Color = vbRed
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

 

[CountTepes]

Put a break at the IF target.column line, then step through with F9 Check the logic whilst looking at the values for the cells and and variables.

Find what is failing then there is more chance of fixing it.

 

[Patriot2879]

Hi what do you mean put a break? i pressed F9 but nothing happens

 

[Patriot2879]

Hi i changed the line below to 18 instead of 24 as well as that will be for column R to change to red or green if the time is greater than 15 between column R and AC, but still not work

If Target.Column = 18 And (UCase(Target.Text) = "YES" Or UCase(Target.Text) = "NO") Then

 

[CountTepes]

I have spotted something else which is wrong.
Move application.enablevents=false to the top of the code. As a change in the cell also timestamps, it will trigger itself again.

Back to the issue.
Which column has your initial timestamp?
Which column should have "Yes" or "No"?
Which column do you want "Yes" or "No", to timestamp?
Which Column do you want turning cells to turn RED?

The description of the code I gave you.
It looked if it was in the correct column for Yes or No and then checked if it was Yes or No
Passing that test, it timestamped a cell in the same row and made sure that the format was dd/mm/yyyy hh:mm:ss
It then looked at the original timestamp and subtracted it from the current timestamp and stored it in TimeDiff
It then checked if Timediff >"00:15:00" and turned a cell red.

Now I am not sure of which columns you need, so I have removed the numbers and labelled the columns, which you need to put the correct numbers in.
I have also commented the code

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