Excel Time Formats

[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

 

[Don Guillett]

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

 

[Maurice]

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

 

[Ron Rosenfeld]

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.

See below for a "cleaning" routine. But if you are just using conditional
formatting to determine the time, independent of the date, you could modify
your CF formulas to only look at the fractional part of the entry. Date/Times
are stored as numbers, and the fractional part of the number is the time (the
integer portion is the date). So instead of, for example, A1 in your CF
formula, you could use =MOD(A1,1) to extract the time portion.

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

That would definitely have to be done in VBA. Here is an event-driven routine
that will trap and convert many errors. I don't know if it'll work in every
circumstance, though, or how easily it will adapt to your worksheet.

To enter this, select View Code from the right click on sheet tab menu, then
paste in the below code. Modify InputRange to represent the input cells to be
checked.

---------------------------
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim InputRange As Range
Dim Temp As Variant
Dim i As Integer
Dim Separators(2) As String

Separators(0) = ";"
Separators(1) = ","
Separators(2) = "."


Application.EnableEvents = False

Set InputRange = [A1:A100] 'or whatever
If Intersect(Target, InputRange) Is Nothing Then GoTo Bye
If Target.Value = 0 Then GoTo Bye
Temp = Target.Value

'If entry is an integer, then make it a time

On Error GoTo ConvertString
If Int(Temp) = Temp Then
Temp = (Int(Temp / 100) + (Temp / 100 - Int(Temp / 100)) * 100 / 60) / 24
GoTo Last
ElseIf Temp < 1 Then 'probably a time
GoTo Last
End If

If Temp > 15000 Then 'probably a date/time string
Temp = CDate(Temp - Int(Temp))
GoTo Last
End If

ConvertString: 'replace separator with colon unless the string is a date
On Error GoTo 0
For i = 0 To 2
Temp = Replace(Temp, Separators(i), ":")
Next i


Last:
Target.Value = Temp
Target.NumberFormat = "hh:mm"
Bye: Application.EnableEvents = True
End Sub