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

[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?

 

[MartinW]

Hi Kim,

Chip Pearson has a way here,
http://www.cpearson.com/excel/DateTimeEntry.htm

HTH
Martin

 

[muddan madhu]

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

 

[Don Guillett]

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

 

[Peo Sjoblom]

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)