time question

  • Thread starter Thread starter Cesar Zapata
  • Start date Start date
C

Cesar Zapata

Hello,

Im trying to play with this code.
i'm having problems with the second IF part.

thanks for your help in advance.

Private Sub Worksheet_Change(ByVal Target As Range)

pinput = Target.Value
' this changes the value from 330p to 3:30 PM
' this works
If Right(pinput, 1) = "p" And Len(pinput) > 2 Then
newvalue = Left(Left(pinput, Len(pinput) - 1), Len(Left(pinput,
Len(pinput) - 1)) - 2) & ":" & Right(Left(pinput, Len(pinput) - 1), 2) & "
PM"
Target.Value = newvalue

Else
' this should for example change from 2p to 2:00 PM
' it does not work. it does nothing.

If Right(pinput, 1) = "p" And Len(pinput) < 2 Then
newvalue = Left(pinput, Len(pinput) - 1) & ":00" & " PM"
Target.Value = newvalue


End If
End If

End Sub


Thanks,
 
Hi
change the lines
----
Else
' this should for example change from 2p to 2:00 PM
' it does not work. it does nothing.

If Right(pinput, 1) = "p" And Len(pinput) < 2 Then
newvalue = Left(pinput, Len(pinput) - 1) & ":00" & " PM"
Target.Value = newvalue
End If
 
Hi Cesar,

Here's a shot

Private Sub Worksheet_Change(ByVal Target As Range)
Dim pinput, newvalue

Application.EnableEvents = False
On Error GoTo ws_exit
pinput = Target.Value
' this changes the value from 330p to 3:30 PM
' this works
If Right(pinput, 1) = "p" And Len(pinput) > 3 Then
newvalue = Left(Left(pinput, Len(pinput) - 1), _
Len(Left(pinput, Len(pinput) - 1)) - 2) & ":" & _
Right(Left(pinput, Len(pinput) - 1), 2) & "PM "
Target.Value = newvalue
Else
' this should for example change from 2p to 2:00 PM
' it does not work. it does nothing.

If Right(pinput, 1) = "p" And Len(pinput) < 4 Then
newvalue = Left(pinput, Len(pinput) - 1) & ":00" & " PM"
Target.Value = newvalue
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top