Excel Time Formats

  • Thread starter Thread starter Maurice
  • Start date Start date
M

Maurice

I'm working on a timesheet to show extra and lost times over a twenty
four hour day. The calculations all seem to work, my problem is the
human factor who can miss type hours/minutes.
I have two issues

1. even though i have formatted the cells, data can be placed from
pasting that includes the date portion i.e 01/01/1900 03:00:00 i need
to clean this data live to produce 03:00:00 (this is necessary due to
conditional formating). I am happy to work in VBA to clean this, just
dont know how.

2. Is there a way of setting up (like access) an input mask for times,
so that whether the user types 0300, 03;00, 03,00, 03.00 or 03:00 as
expected i can trap and convert to the value i need into the 03:00
format. I would prefer to do this transparently rather than flag it as
an error
 
right click on sheet tab>view code>insert this>save workbook
As written, it is working on column 4 below row 4 . Column should be
pre-formatted to TEXT
Now, how do you get them to always enter FOUR characters for it to work.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 4 and Target.row > 4 Then
On Error GoTo endit
Application.EnableEvents = False
Target = Left(Target, 2) & ":" & Right(Target, 2)
End If
endit:
Application.EnableEvents = True
End Sub
 
Don, thanks for the input
I had partial success with that method, i may have understated the
complexity of my task. I have three days to look at each as below, each
day is 56 rows deep

Stn Callsign Start Finish Manning Type
A1 A101 19:00 07:00 SM CORE
A2 A288 18:30 06:30 FM ADD
B1 B188 15:00 03:00 FM ADD
B5 B588 16:00 03:00 FM ADD
B5 B589 22:00 03:00 FM ADD
F2 F208 08:00 17:00 SM CORE

i'm using VBA to allow the 7 remote sites to sort the data. Column
start and finish are time fields which are manipulated to show how many
hours are gained or lost, the whole sheet is calculated for each day
with addition / subtraction performed on the data, i also extract
vehicle types from call sign to report them seperately on the same
sheet as they are key to our use. After each compalation the loose
sheets are combined into a single workbook. Conditional formats also
used to highlight the time data by three time segments

Hence the reason for trying to keep the fields as time, and the visible
user errors hidden
 
Back
Top