| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to format time in decimal format & calculate time | Excel Discussion (Misc queries) | |||
| How to format time in decimal format & calculate time | Excel Discussion (Misc queries) | |||
| How to format time in decimal format & calculate time | Excel Discussion (Misc queries) | |||
| How to type format time in decimal format & calculate time | Excel Discussion (Misc queries) | |||
| Remove time from a date and time field? Format removes the displa. | Excel Worksheet Functions | |||
Linear Mode
