Excel Formula to VBA

[dhosi439]

Can someone please assist with writing this formula in VBA:

=IF(RIGHT(TEXT(C2,"mm/dd/yy hh:mm:ss"),2)>"30",LEFT(C2,14)+0.000694,C2)

This is what I have so far, but I am receiving a type mismatch error. Please be aware this is a test Sub that I am using via a command button on a sheet. Ultimately this will be transfered into Outlook to pull date/time appropriately.

Private Sub CommandButton1_Click()
'=IF(RIGHT(TEXT(C2,"mm/dd/yy hh:mm:ss"),2)>"30",LEFT(C2,14)+0.000694,C2)
If Right(Format(ActiveCell.Offset(0, -4).Value, "mm/dd/yy hh:mm:ss"), 2) > "30" Then
ActiveCell.Value = (Left(ActiveCell.Offset(0, -4).Value, 14) + 0.000694)
Else
ActiveCell.Value = Left(ActiveCell.Offset(0, -4).Value, 14)
End If
End Sub

 

[lenze]

Private Sub CommandButton1_Click()
'=IF(RIGHT(TEXT(C2,"mm/dd/yy hh:mm:ss"),2)>"30",LEFT(C2,14)+0.000694,C2)
If Right(Format(ActiveCell.Offset(0, -4).Value, "mm/dd/yy hh:mm:ss"), 2) +0 > 30 Then
ActiveCell.Value = (Left(ActiveCell.Offset(0, -4).Value, 14) + 0.000694)
Else
ActiveCell.Value = Left(ActiveCell.Offset(0, -4).Value, 14)
End If
End Sub

lenze

 

[mikerickson]

Perhaps

If Second(CStr(ActiveCell.Offset(0, -4).Value)) > 30 Then

 

[dhosi439]

Okay, the change you presented didn't resolve my issue. But it did prompt me to realize that the error only occurs for this line:

ActiveCell.Value = (Left(ActiveCell.Offset(0, -4).Value, 14) + 0.000694)

Which is the code that runs when values are over 30.

 

[dhosi439]

mikerickson,

I'm actually not reading the seconds value. I am reading the milliseconds.

This is the new code:

Private Sub CommandButton1_Click()
If Right(ActiveCell.Offset(0, -4).Value, 2) > 30 Then
ActiveCell.Value = (Left(ActiveCell.Offset(0, -4).Value, 14) + 0.000694)
Else
ActiveCell.Value = Left(ActiveCell.Offset(0, -4).Value, 14)
End If
End Sub

 

[mikerickson]

What would the 14 characters of the cell be? Might

ActiveCell.Value = Int(ActiveCell.Offset(0, -4)) + DateValue("0:01:00")

 

[dhosi439]

The 14 characters are the date and time: "11/18/10 08:30"

I tried the new code, still received a type mismatch error.

 

[dhosi439]

This code resolved my issue.

Sub Test()
    Dim strTime As String
    
    strTime = Format(ActiveCell.Offset(0, -4).Value, "mm/dd/yy hh:mm:ss")
    If Right(strTime, 2) > "30" Then
        ActiveCell.Value = DateValue(Left(strTime, 14)) + TimeValue(Left(strTime, 14)) + 0.000694
    Else
        ActiveCell.Value = DateValue(Left(strTime, 14)) + TimeValue(Left(strTime, 14))
    End If
End Sub