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

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?

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

Hi Kim,

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

HTH
Martin



"kimrich" wrote in message
...
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?

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

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


On May 31, 4:29*pm, "MartinW" wrote:
Hi Kim,

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

HTH
Martin

"kimrich" wrote in message

...



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?- Hide quoted text -

- Show quoted text -

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

Right click sheet tabview codecopy/paste thismodify 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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"kimrich" wrote in message
...
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?

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

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


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


On May 31, 4:29 pm, "MartinW" wrote:
Hi Kim,

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

HTH
Martin

"kimrich" wrote in message

...



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?- Hide quoted
text -

- Show quoted text -