excel time entry help!!!

  • Thread starter Thread starter Ted
  • Start date Start date
T

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
 
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.
 
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
 
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?
 
<"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?
 
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.
 
Back
Top