G
Guest
Is there a way to set up a cell so that I can type in 1330, and have Excel put in the colon so it reads 13:30 (military time)?
Install the app
How to install the app on iOS
Follow along with the video below to see how to install our site as a web app on your home screen.
Note: This feature may not be available in some browsers.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
D
Don Guillett
right click sheet tab>view code>insert this. As written, works on a1:a100
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
--
Don Guillett
SalesAid Software
(e-mail address removed)
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
--
Don Guillett
SalesAid Software
(e-mail address removed)
put in the colon so it reads 13:30 (military time)?kimc said:Is there a way to set up a cell so that I can type in 1330, and have Excel
G
Gord Dibben
Kim
Only by using VBA.
See Chip Pearson's QuickEntry codes.
http://www.cpearson.com/excel/DateTimeEntry.htm
Gord Dibben Excel MVP
Only by using VBA.
See Chip Pearson's QuickEntry codes.
http://www.cpearson.com/excel/DateTimeEntry.htm
Gord Dibben Excel MVP
G
Guest
Thank you so much! One more question, this is the way my client formats Column A now. Can you just explain this function to me in English?
=LOOKUP(9.99999999999999E+307,A:A)
=LOOKUP(9.99999999999999E+307,A:A)
Don Guillett said:right click sheet tab>view code>insert this. As written, works on a1:a100
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
--
Don Guillett
SalesAid Software
(e-mail address removed)
put in the colon so it reads 13:30 (military time)?kimc said:Is there a way to set up a cell so that I can type in 1330, and have Excel
P
Peo Sjoblom
If you mean using a formula (not formats) it means it will return the last
entered numeric value in column A, where last entered
means down-most entered value
--
No private emails please, for everyone's
benefit keep the discussion in the newsgroup.
Regards,
Peo Sjoblom
entered numeric value in column A, where last entered
means down-most entered value
--
No private emails please, for everyone's
benefit keep the discussion in the newsgroup.
Regards,
Peo Sjoblom
Column A now. Can you just explain this function to me in English?kimc said:Thank you so much! One more question, this is the way my client formats
=LOOKUP(9.99999999999999E+307,A:A)
Don Guillett said:right click sheet tab>view code>insert this. As written, works on a1:a100
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
--
Don Guillett
SalesAid Software
(e-mail address removed)
Excelkimc said:Is there a way to set up a cell so that I can type in 1330, and have
put in the colon so it reads 13:30 (military time)?
G
Guest
Don, this works like a charm, but I have one question. How can I have it format 3 different columns in the same spreadsheet at once? I need this to format columns B, P and R at the same time.
Thanks - as you can see, I don't know VB too well.
Thanks - as you can see, I don't know VB too well.
Don Guillett said:right click sheet tab>view code>insert this. As written, works on a1:a100
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
--
Don Guillett
SalesAid Software
(e-mail address removed)
put in the colon so it reads 13:30 (military time)?kimc said:Is there a way to set up a cell so that I can type in 1330, and have Excel
G
Guest
Okay, I copied from "Private Sub Worksheet...." down to the last "End Sub"
and this doesn't work for me. Can somebody please help??
and this doesn't work for me. Can somebody please help??
Don Guillett said:right click sheet tab>view code>insert this. As written, works on a1:a100
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
--
Don Guillett
SalesAid Software
(e-mail address removed)
put in the colon so it reads 13:30 (military time)?kimc said:Is there a way to set up a cell so that I can type in 1330, and have Excel
J
JE McGimpsey
Not sure what "doesn't work" means - do you know what to do with a
macro? Did you put the macro in the worksheet's code module? Does it
give a compile error? a run-time error? a crash? The wrong result?
nothing?
If you don't know what to do with macros, see David McRitchie's "Getting
Started with Macros":
http://www.mvps.org/dmcritchie/excel/getstarted.htm
If it's one of the other problems, please explain what "doesn't work"
means to you...
macro? Did you put the macro in the worksheet's code module? Does it
give a compile error? a run-time error? a crash? The wrong result?
nothing?
If you don't know what to do with macros, see David McRitchie's "Getting
Started with Macros":
http://www.mvps.org/dmcritchie/excel/getstarted.htm
If it's one of the other problems, please explain what "doesn't work"
means to you...
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.