Format a time without entering the :, but keep it as a time

[ashley91481]

I have used a custom format so that I can enter a time as 115808 and have it show up as 11:58:08. Unfortunately, now i need to do calculations and the calculations are not correct b/c it is not recognizing this as a time. All of the "solutions" I've found for this don't include the seconds or require knowledge of visual basic. Is there a simple wait to convert this number 115808 into a time so it can be used in calculations?

 

[shg]

You could use something like this:

[FONT=Consolas][COLOR=#595959]Private Sub Worksheet_Change(ByVal Target As Range)[/COLOR][/FONT]
[COLOR=#595959][FONT=Consolas]   With Target[/FONT][/COLOR]
[COLOR=#595959][FONT=Consolas]       If .Column = 1 Then     ' change as necessary[/FONT][/COLOR]
[COLOR=#595959][FONT=Consolas]           On Error Resume Next[/FONT][/COLOR]
[COLOR=#595959][FONT=Consolas]           Application.EnableEvents = False[/FONT][/COLOR]
[COLOR=#595959][FONT=Consolas]           .Value = Dec2HM(.value) [/FONT][/COLOR]
[COLOR=#595959][FONT=Consolas]           ' or[/FONT][/COLOR]
[COLOR=#595959][FONT=Consolas]           .Value = Dec2HMS(.value) [/FONT][/COLOR]
[COLOR=#595959][FONT=Consolas]           Application.EnableEvents = True[/FONT][/COLOR]
[COLOR=#595959][FONT=Consolas]       End If[/FONT][/COLOR]
[COLOR=#595959][FONT=Consolas]   End With[/FONT][/COLOR]
[FONT=Consolas][COLOR=#595959]End Sub[/COLOR][/FONT]
[FONT=Calibri][SIZE=3][/SIZE][/FONT] 
[FONT=Calibri][SIZE=3]… and one of these[/SIZE][/FONT]
[FONT=Calibri][SIZE=3][/SIZE][/FONT] 
[FONT=Consolas][COLOR=#595959]Function Dec2HM(iVal As Long) As Date[/COLOR][/FONT]
[COLOR=#595959][FONT=Consolas]   ' converts 1234 to 12:34[/FONT][/COLOR]
[COLOR=#595959][FONT=Consolas]   Dec2HM = CDate((iVal \ 100) / 24 + _[/FONT][/COLOR]
[COLOR=#595959][FONT=Consolas]                  (iVal Mod 100) / 1440)[/FONT][/COLOR]
[FONT=Consolas][COLOR=#595959]End Function[/COLOR][/FONT]
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
[FONT=Consolas][COLOR=#595959]Function Dec2HMS(iVal As Long) As Date[/COLOR][/FONT]
[COLOR=#595959][FONT=Consolas]   ' converts 123456 to 12:34:56[/FONT][/COLOR]
[COLOR=#595959][FONT=Consolas]   Dec2HMS = CDate((iVal \ 10000) / 24 + _[/FONT][/COLOR]
[COLOR=#595959][FONT=Consolas]                   ((iVal - 10000 * (iVal \ 10000)) \ 100) / 1440 + _[/FONT][/COLOR]
[COLOR=#595959][FONT=Consolas]                   (iVal Mod 100) / 86400)[/FONT][/COLOR]
[FONT=Consolas][COLOR=#595959]End Function[/COLOR][/FONT]

 

[Rick Rothstein]

You can do what you asked for using event code (just change the "A" assigment to the ColumnLetter constant in the Const statement to the letter of the column you want to have this functionality)...

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Cell As Range
  Const ColumnLetter As String = "A"
  If Intersect(Target, Columns(ColumnLetter)) Is Nothing Then Exit Sub
  Application.EnableEvents = False
  For Each Cell In Intersect(Target, Columns(ColumnLetter))
    If Cell.Value Like "#####" Or Cell.Value Like "######" Then Cell.Value = Format(Cell.Value, "00:00:00")
  Next
  Application.EnableEvents = True
End Sub

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the above event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself.