Time Format Input question

[WinterCoast]

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

[prepotency]

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

[Norman Jones]

Hi Scott,

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

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


---
Regards,
Norman



"WinterCoast" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

[STEVE BELL]

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>