Time format cells

[craigwojo]

making a cell for time to show (for example 10:23am). But I just want to input 10.23 for faster input on the spreadsheet.

Is this possible?

Using Office 2013

 

[James006]

Hello,

There is a shortcut to enter current time ...

Ctrl Shift ;

Hope this will help

 

[craigwojo]

Thats really cool. But I'll be putting in different times not the current

 

[JoeMo]

Lightly tested, but here's an event macro (set for Column P in this example) that changes any entry in the format "?#.##" to military time. So, 10.23 becomes 10:23 and 22.23 becomes 22:23.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
'change target range to suit
If Not Intersect(Target, Columns("P")) Is Nothing Then
    For Each c In Intersect(Target, Columns("P"))
        If IsNumeric(c.Value) And c.Value Like "?#.##" Then
            Application.EnableEvents = False
            c.Value = Int(c.Value) & ":" & Right(c.Value, 2)
            Application.EnableEvents = True
        End If
    Next c
End If
End Sub

 

[Repush]

depends on the source: text or number

time text to time value:

TimeStr = CStr(ActiveCell.Value)
hrs = Left(TimeStr, InStr(1, TimeStr, ".") - 1)
mins = Mid(TimeStr, InStr(1, TimeStr, ".") + 1, 2)
TimeVal = hrs / 24 + mins / 24 / 60
ActiveCell.NumberFormat = "h:mm;@"
ActiveCell.Value = TimeVal

time number to time value:

TimeVal_0 = ActiveCell.Value
hrs = Int(TimeVal_0)
mins = (TimeVal_0 - Int(TimeVal_0)) * 100
TimeVal = hrs / 24 + mins / 24 / 60
.

 

[James006]

If you have 10.23 in cell A2 ...

you can have the time conversion in cell B2

=(INT(A2)/24)+((A2-INT(A2))*100)/1440

Hope this will help

 

[James006]

Hi again,

Should you need an Event macro ...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
' Adapt Column Input to your situation '
If Target.Column <> 1 Then Exit Sub
If Target = "" Then Exit Sub
Application.EnableEvents = False
Application.ScreenUpdating = False
  With Target
    .Offset(0, Range("IV2").End(xlToLeft).Offset(0, 1).Row).Value2 = _
    (Int(.Value2) / 24) + ((.Value2 - Int(.Value2)) * 100) / 1440
    .Value2 = .Offset(0, Range("IV2").End(xlToLeft).Offset(0, 1).Row).Value2
    .NumberFormat = "hh:mm"
    .Offset(0, Range("IV2").End(xlToLeft).Offset(0, 1).Row).Clear
  End With
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

Hope this will help

 

[James006]

@craigwojo

Wouldn't it be great to share with the Forum ... the final outcome ...

 

[James006]

@craigwojo

Should you ever visit again the Forum ... sincerely eager to know the final outcome ...

Thanks a lot ...

 

[craigwojo]

I apologize that I didn't get back. But everything worked out correctly. Thank you.