Time Format

[paul]

Hi

how do i set a format to read time. I want to enter say 18
and Excel to recognise that i mean 6pm. I don't want it to
reference any particular date.

I want t oenter say 2 times 18.30 and 9.00 and be able to
work out the differnce in hours

Any help appreciated
thanks
Paul

 

[David McRitchie]

Hi Paul,
Formats are for display purposes, except that it also tells what kind
of number you have. So if you have a time format it is assumed
you are entering a time and you have to enter hours:minutes
The minimum you would have t o enter is 18:
with the colon after the 18.

You could probably use an Event macro as long as you remain under
24 hours *and* after 1 AM. If you need midnight to 1AM you
will have to remove cange the condition test in the following code..

To install Right Click on the worksheet tab you want to use in
view code, plop the following code inside.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
if target.column <> 3 then exit sub 'only for Column C
Application.EnableEvents = False 'should be part of Change macro
If Target.Value > 1 Then Target.Value = Time(Target.Value, 0)
Application.EnableEvents = True 'should be part of Change macro
End Sub

More information on Events
http://www.mvps.org/dmcritchie/excel/event.htm

Some examples for nonstandard Date and Time Entry -- Chip Pearson
http://www.cpearson.com/excel/datetimeentry.htm