Translate Formula to VBA

[Livin404]

Greetings, I have times in Column D and would like to add 20 minutes to that post them to Column E. Not every cell in Column D contains a time so I'm also trying to avoid value errors. I know the code

=D7+20/1440

works fine, but I need something in VBA to match only times in Column D. Column D will remain the same, Column E will have that time with 20 minutes added to it. Thank you,

 

[My Aswer Is This]

Do you want this to happen automatically when you enter a Time in column D.
Or do you want the script to run when you press a button?

Show me a example of how you put time in column D
Do you do it with a script or manually.

 

[Livin404]

Do you want this to happen automatically when you enter a Time in column D.
Or do you want the script to run when you press a button?

Show me a example of how you put time in column D
Do you do it with a script or manually.

Thank you for the prompt response. It will definitely happen with a button. It will happen right before the following VBA, and the VBA below it will need to include the new time in Column D.

Sub twenty_four_hour_Clock()
   With Range("D1", Range("D" & Rows.Count).End(xlUp))
      .NumberFormat = "@"
      .Value = Evaluate(Replace("if(@="""","""",if(isnumber(@),text(mod(@,1),""hhmm""),@))", "@", .Address))
   End With
End Sub

 

[Livin404]

Do you want this to happen automatically when you enter a Time in column D.
Or do you want the script to run when you press a button?

Show me a example of how you put time in column D
Do you do it with a script or manually.

Then after the VBA shown above it will look like this.

 

[maabadi]

You can use this for D1

Sub twenty_four_hour_Clock()
 Dim x As Long
   With Range("D1", Range("D" & Rows.Count).End(xlUp))
      .NumberFormat = "@"
      .Value = Evaluate(Replace("if(@="""","""",if(isnumber(@),text(mod(@,1),""hhmm""),@))", "@", .Address))
   End With
     If IsNumeric(Range("D1").Value * 1) And Application.WorksheetFunction.IsText(Range("D1").Value) Then
       x = Right(Range("D1"), 2) * 1
        If x > 39 Then
          Range("E1").Value = Range("D1").Value * 1 + 60
         Else
          Range("E1").Value = Range("D1").Value * 1 + 20
        End If
      End If
   
End Sub

And if for range I think you can expand it.