excel time entry help!!!

[Ted]

I need to do the following:

A1=Time in (need to enter as 4 digit number but display as
hh:mm format without having to type the ":" separator)

B1= Time out (same as above)

C1 = (B1-A1) displayed in hh:mm format (A1 and B1 times will use 24hr
military times.)

Thanks to anyone who can help with this.
Ted

 

[J.E. McGimpsey]

Take a look here:

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

 

[Peo Sjoblom]

I assume this is because you enter time using the numpad, you could also
create a time entry sheet,
in the time entry sheet press Alt + F11, double click ThisWorkbook and paste
in this in the window that opens


Private Sub Workbook_Activate()
Application.AutoCorrect.AddReplacement What:=".", Replacement:=":"
End Sub

Private Sub Workbook_Deactivate()
Application.AutoCorrect.DeleteReplacement What:="."
End Sub

everytime you open this workbook and enable macors you can type in 20.30 for
20:30 and so on and when
you close the workbook autocorrect will reset so a period will work for
other workbooks. And if you need to
use a period in this workbook you can press space, then ctrl z, then space
or enter depending on if you are done typing in that cell.

 

[Peo Sjoblom]

Or you can use numbers without the time separator and let the formula take
of it

if for instance start time is in A1 and looks like

800 (for 08:00)

end time is in in B1 and can look like

1630 (for 16:30)

=(INT(B1/100)+MOD(B1,100)/60)/24-(INT(A1/100)+MOD(A1,100)/60)/24

will return 08:30 if formatted as hh:mm

 

[Don Guillett]

try this
right click on sheet tab>view code>insert this
Assumes that col A and col B are formatted as TEXT.
and you enter FOUR digit times such as 0800 1350, etc.

Then put a formula in col C =b1-a1 formatted as hh:mm
or
=(b1-a1)*24 formatted as general for the fractions of hours

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 1 Or Target.Column = 2 Then
On Error GoTo endit
Application.EnableEvents = False
Target = Left(Target, 2) & ":" & Right(Target, 2)
End If
endit:
Application.EnableEvents = True
End
======
BTW Does using the nospam in your email REALLY help to prevent the MS
messages coming to your box?

 

[RagDyer]

<"BTW Does using the nospam in your email REALLY help to prevent the MS
messages coming to your box?">

In 5 days it's reduced mine by almost 50%.
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

try this
right click on sheet tab>view code>insert this
Assumes that col A and col B are formatted as TEXT.
and you enter FOUR digit times such as 0800 1350, etc.

Then put a formula in col C =b1-a1 formatted as hh:mm
or
=(b1-a1)*24 formatted as general for the fractions of hours

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 1 Or Target.Column = 2 Then
On Error GoTo endit
Application.EnableEvents = False
Target = Left(Target, 2) & ":" & Right(Target, 2)
End If
endit:
Application.EnableEvents = True
End
======
BTW Does using the nospam in your email REALLY help to prevent the MS
messages coming to your box?

 

[Aladin Akyurek]

Also, Excel appears to parse time entries like 800, 1640, etc. correctly
with the following setup...

=--TEXT(A1,"00\:00")

The formula cell can then be time-formatted as hh:mm.