Time without Colon

[countachl]

I would like to enter the time (e.g. 1234) in a cell without the colon,
since I have many to enter. I would then like to calculate the diff
between two times. However, I can't discover a way to do this (the way I
want).

There seems to be two problems: first, If I configure a cell with
contents 1234 as Time, I get a date and 0:00. So the entered time is
not correct. The second problem is calculating the difference as time
when the source cells aren't in the 12:34 format.

Does anyone have an idea how this might be accomplished ?

Thansk for any assistance.

DonC

 

[Paul B]

Don, have a look here http://www.cpearson.com/excel/DateTimeEntry.htm
--
Paul B
Always backup your data before trying something new
Using Excel 97 & 2000
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **

 

[Nick Hodge]

Don

You have to understand how excel handles dates and times to know why you get
a date and 0:00. Look here

http://www.cpearson.com/excel/datetime.htm

and here

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

You can use some code during or after the entry to insert the :, but that is
dependant on how you are entering times. e.g 730 or 0730

You can almost certainly also convert after the event with a function.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Don Guillett]

right click on the sheet tab>view code>insert this>SAVE
Just be sure you enter all FOUR as 0300 1530, etc

Private Sub Worksheet_Change(ByVal Target As Excel.Range)'Don
If Target.Column = 4 Or Target.Column = 5 Then
On Error GoTo endit
Application.EnableEvents = False
Target = Left(Target, 2) & ":" & Right(Target, 2)
End If
endit:
Application.EnableEvents = True
End Sub
--------------------------------
Or, on a DIFFERENT sheet with the same instructions.
'========
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range) 'Ron Rosenfeld
<[email protected]>
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
'========

 

[Gord Dibben]

Don

http://www.cpearson.com/excel/DateTimeEntry.htm for Time entry without
colons.

http://www.cpearson.com/excel/datetime.htm#AddingDates for help with
adding/subtracting Times.Dates

Gord Dibben XL2002