Time Format
 

Hello

I have a worksheet where one of the columns is TIME. All I want is that I
write 0315 (3.15 AM) and it appears as 03:15. Is there a solution for this?

Thanks in advance.

Time Format
 

You need an event macro for that:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 10 Then 'change column No as necessary
Application.EnableEvents = False
ora = Int(Target.Value / 100)
perc = Target.Value - ora * 100
Target.Value = TimeSerial(ora, perc, 0)
Application.EnableEvents = True
End If
End Sub

This one works for column J (column 10).
Post if you need help to install it!

--
Regards!
Stefi



€žMAX€� ezt Ã*rta:

Hello

I have a worksheet where one of the columns is TIME. All I want is that I
write 0315 (3.15 AM) and it appears as 03:15. Is there a solution for this?

Thanks in advance.

Time Format
 

IT works perfect but I need this from cell A7 to A68.

"Stefi" wrote:

You need an event macro for that:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 10 Then 'change column No as necessary
Application.EnableEvents = False
ora = Int(Target.Value / 100)
perc = Target.Value - ora * 100
Target.Value = TimeSerial(ora, perc, 0)
Application.EnableEvents = True
End If
End Sub

This one works for column J (column 10).
Post if you need help to install it!

--
Regards!
Stefi



€žMAX€� ezt Ã*rta:

Hello

I have a worksheet where one of the columns is TIME. All I want is that I
write 0315 (3.15 AM) and it appears as 03:15. Is there a solution for this?

Thanks in advance.

Time Format
 

Try the below..Modify the range to suit your requirement

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("A1:A10")) Is Nothing Then
If IsNumeric(Target.Value) Then
Application.EnableEvents = False
Target = Left(Format(Target.Value, "0000"), 2) & ":" &
Right(Format(Target.Value, "0000"), 2)
Application.EnableEvents = True
End If
End If
End Sub

--
Jacob


"MAX" wrote:

Hello

I have a worksheet where one of the columns is TIME. All I want is that I
write 0315 (3.15 AM) and it appears as 03:15. Is there a solution for this?

Thanks in advance.

Time Format
 

An error is given and to help you these 2 lines appear in RED.
Target = Left(Format(Target.Value, "0000"), 2) & ":" &
Right(Format(Target.Value, "0000"), 2)

"Jacob Skaria" wrote:

Try the below..Modify the range to suit your requirement

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("A1:A10")) Is Nothing Then
If IsNumeric(Target.Value) Then
Application.EnableEvents = False
Target = Left(Format(Target.Value, "0000"), 2) & ":" &
Right(Format(Target.Value, "0000"), 2)
Application.EnableEvents = True
End If
End If
End Sub

--
Jacob


"MAX" wrote:

Hello

I have a worksheet where one of the columns is TIME. All I want is that I
write 0315 (3.15 AM) and it appears as 03:15. Is there a solution for this?

Thanks in advance.

Time Format
 

That should be in one line..Try copy pasting the below code....

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("A1:A10")) Is Nothing Then
If IsNumeric(Target.Value) Then
Application.EnableEvents = False
Target = Left(Format(Target.Value, "0000"), 2) & ":" & _
Right(Format(Target.Value, "0000"), 2)
Application.EnableEvents = True
End If
End If
End Sub


--
Jacob


"MAX" wrote:

An error is given and to help you these 2 lines appear in RED.
Target = Left(Format(Target.Value, "0000"), 2) & ":" &
Right(Format(Target.Value, "0000"), 2)

"Jacob Skaria" wrote:

Try the below..Modify the range to suit your requirement

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("A1:A10")) Is Nothing Then
If IsNumeric(Target.Value) Then
Application.EnableEvents = False
Target = Left(Format(Target.Value, "0000"), 2) & ":" &
Right(Format(Target.Value, "0000"), 2)
Application.EnableEvents = True
End If
End If
End Sub

--
Jacob


"MAX" wrote:

Hello

I have a worksheet where one of the columns is TIME. All I want is that I
write 0315 (3.15 AM) and it appears as 03:15. Is there a solution for this?

Thanks in advance.

Time Format
 

Thanks to you and Stef for your great help in time.

"Jacob Skaria" wrote:

That should be in one line..Try copy pasting the below code....

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("A1:A10")) Is Nothing Then
If IsNumeric(Target.Value) Then
Application.EnableEvents = False
Target = Left(Format(Target.Value, "0000"), 2) & ":" & _
Right(Format(Target.Value, "0000"), 2)
Application.EnableEvents = True
End If
End If
End Sub


--
Jacob


"MAX" wrote:

An error is given and to help you these 2 lines appear in RED.
Target = Left(Format(Target.Value, "0000"), 2) & ":" &
Right(Format(Target.Value, "0000"), 2)

"Jacob Skaria" wrote:

Try the below..Modify the range to suit your requirement

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("A1:A10")) Is Nothing Then
If IsNumeric(Target.Value) Then
Application.EnableEvents = False
Target = Left(Format(Target.Value, "0000"), 2) & ":" &
Right(Format(Target.Value, "0000"), 2)
Application.EnableEvents = True
End If
End If
End Sub

--
Jacob


"MAX" wrote:

Hello

I have a worksheet where one of the columns is TIME. All I want is that I
write 0315 (3.15 AM) and it appears as 03:15. Is there a solution for this?

Thanks in advance.

Time Format
 

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A7:A68")) Is Nothing Then
Application.EnableEvents = False
ora = Int(Target.Value / 100)
perc = Target.Value - ora * 100
Target.Value = TimeSerial(ora, perc, 0)
Application.EnableEvents = True
End If
End Sub

--
Regards!
Stefi



€žMAX€� ezt Ã*rta:

IT works perfect but I need this from cell A7 to A68.

"Stefi" wrote:

You need an event macro for that:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 10 Then 'change column No as necessary
Application.EnableEvents = False
ora = Int(Target.Value / 100)
perc = Target.Value - ora * 100
Target.Value = TimeSerial(ora, perc, 0)
Application.EnableEvents = True
End If
End Sub

This one works for column J (column 10).
Post if you need help to install it!

--
Regards!
Stefi



€žMAX€� ezt Ã*rta:

Hello

I have a worksheet where one of the columns is TIME. All I want is that I
write 0315 (3.15 AM) and it appears as 03:15. Is there a solution for this?

Thanks in advance.