How do I enter 1635 and see 4:35 PM in the cell?

  • Thread starter Thread starter kimrich
  • Start date Start date
K

kimrich

In a spreadsheet, how can I quickly enter 3 or 4 time numbers and have excel
convert those numbers to a standard time AM or PM format?
 
try this ...suppose u enter 1635 in A1 , put this formula in B1
=TIME(LEFT(A1,2),RIGHT(A1,2),0)
 
Right click sheet tab>view code>copy/paste this>modify range(s) to suit

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo nomo
If Target.Count <> 1 Then Exit Sub

If Intersect(Target, Range("g:h")) Is Nothing _
Or Len(Application.Trim(Target)) < 1 _
Then Exit Sub

Application.EnableEvents = False
If Len(Target) = 3 Then
Target = Format(Left(Target, 1) & ":" & Right(Target, 2), "hh:mm")
Else
Target = Format(Left(Target, 2) & ":" & Right(Target, 2), "hh:mm")
End If

tr = Target.Row
If Cells(tr, "h") <> "" Then Cells(tr, "I") = _
(Cells(tr, "H") - Cells(tr, "g")) * 24
nomo:
Application.EnableEvents = True
End Sub
Sub fixit()

Application.EnableEvents = True
End Sub
 
That is not true, your way would use and extra cell and that is not what the
OP asked for. However if you are using an extra cell then this is somewhat
easier

=--TEXT(E1,"00\:00")

format the cell as h:mm AM/PM

or format directly as text

=TEXT(TEXT(E1,"00\:00"),"h:mm AM/PM")


--


Regards,


Peo Sjoblom


try this ...suppose u enter 1635 in A1 , put this formula in B1
=TIME(LEFT(A1,2),RIGHT(A1,2),0)
 
Back
Top