Hi,
I have an employee schedule at work that I want to update to speed
entry of all times. Currently, every cell that is time is formatted as
such, and to get 6:00 PM, I type in 18:00 (I find it easier to think in
military time when using excel).

However, what I want is some code so that I can leave out typing the
colon. Hence, I want to be able to type in 1800 and have excel
automatically change that to 6:00 PM.

How do I accomplish this?

Thanks in advance,
Scott


--
WinterCoast
------------------------------------------------------------------------
WinterCoast's Profile: http://www.excelforum.com/member.php...o&userid=21044
View this thread: http://www.excelforum.com/showthread...hreadid=386646

Without writing a macro the only way I know of to get close to that is
by formatting the cell:

Select Custom Format
Enter:
#":"##

That will format your 1800 into 18:00

As far as getting it into another time format, I'm not sure how to do
that without writing a macro.

G


--
prepotency
------------------------------------------------------------------------
prepotency's Profile: http://www.excelforum.com/member.php...o&userid=24155
View this thread: http://www.excelforum.com/showthread...hreadid=386646

Hi Scott,

See Chip Pearson's Date And Time Entry page at:

http://www.cpearson.com/excel/DateTimeEntry.htm


---
Regards,
Norman



"WinterCoast"
wrote in message
...

Hi,
I have an employee schedule at work that I want to update to speed
entry of all times. Currently, every cell that is time is formatted as
such, and to get 6:00 PM, I type in 18:00 (I find it easier to think in
military time when using excel).

However, what I want is some code so that I can leave out typing the
colon. Hence, I want to be able to type in 1800 and have excel
automatically change that to 6:00 PM.

How do I accomplish this?

Thanks in advance,
Scott


--
WinterCoast
------------------------------------------------------------------------
WinterCoast's Profile:
http://www.excelforum.com/member.php...o&userid=21044
View this thread: http://www.excelforum.com/showthread...hreadid=386646

This is some crude code I use.
Column 5 (E) is where I input my time values (0 - 2400)

I use a workbook sheet change event in the ThisWorkbook module (so it will
apply to all worksheets)

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Application.EnableEvents = False
Application.ScreenUpdating = False

Dim h, hh, m, mm, pam

If Target.Column = 5 Then
If Target 0 Then
h = Target.Value
If h < 100 Then
hh = 0
pam = " AM"
ElseIf h = 1200 Then
hh = Left(h, 2) - 12
pam = " PM"
Else: hh = Left(h, Len(h) - 2)
pam = " AM"
End If
mm = WorksheetFunction.Floor(Target.Offset(0, -1), 1)

Target = hh & ":" & Right(h, 2) & pam
h = Cells(Target.Row, Target.Column).Value
End If
End If
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

--
steveB

Remove "AYN" from email to respond
"WinterCoast"
wrote in message
...

Hi,
I have an employee schedule at work that I want to update to speed
entry of all times. Currently, every cell that is time is formatted as
such, and to get 6:00 PM, I type in 18:00 (I find it easier to think in
military time when using excel).

However, what I want is some code so that I can leave out typing the
colon. Hence, I want to be able to type in 1800 and have excel
automatically change that to 6:00 PM.

How do I accomplish this?

Thanks in advance,
Scott


--
WinterCoast
------------------------------------------------------------------------
WinterCoast's Profile:
http://www.excelforum.com/member.php...o&userid=21044
View this thread: http://www.excelforum.com/showthread...hreadid=386646