Solved

Convert numbers to time

Posted on 2013-01-18
17
493 Views
Last Modified: 2013-01-20
Guys, in excel if I type 656p  in a cell how does it convert to 6:56 PM. I don't want to have to use the colon.

Does this need to be done with vba or can it be formatted as such?

Thank you for your help,

Robert
0
Comment
Question by:rws1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 7
  • 3
17 Comments
 
LVL 26

Expert Comment

by:redmondb
ID: 38795394
Robert,

You have a couple of choices...
(1) A Change Event macro which monitors the appropriate cells for any changes and converts values in the format you specify to times.
(2) Use two columns - the first is the input one, while the second is a formula that converts the input cell's value to a time.

If you want either (or both!) of these please let me know.

Regards,
Brian.
0
 

Author Comment

by:rws1
ID: 38795410
Brian, thank you for your response.

I understand how to put a formula in a 2nd cell for the conversion to time.

I am assuming from your response it must happen with vba and if this is the case could you please forward the code, that when I type 656p the result is 6:56 PM or 656a the result is 6:56 AM.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 38795425
right-click on the sheet tab name and select view code and paste this code
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
If Right(Target, 1) = "p" Or Right(Target, 1) = "a" Then
Target = CVDate(Replace(Left(Target, Len(Target) - 1) / 100, ".", ":") & Right(Target, 1) & "m")
End If
End If
End Sub

Open in new window

This works for column 1. For an other column change line 2
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:rws1
ID: 38795444
Thank you for your response, I type your code in my editor then type 656p in column A with no success, however I need the code to work in columns d,f j, any further suggestions would be greatly appreciated.
Robert
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 38795488
Try this file
Timecon.xlsm
0
 
LVL 26

Assisted Solution

by:redmondb
redmondb earned 200 total points
ID: 38795494
Robert,

Edit: Apologies, wrong range now corrected.

Please see attached. The code is...
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim xRange As Range
Dim xCell  As Range
Dim xlast_Row As Long
Dim xTime     As Variant
Dim xHold     As String
Dim xLast     As String
Dim xlength   As Long

xlast_Row = Me.Range("A1").SpecialCells(xlLastCell).Row
If xlast_Row < 2 Then Exit Sub

Set xRange = Intersect(Target, Me.Range("D2:D" & xlast_Row & ", F2:F" & xlast_Row & ", J2:J" & xlast_Row))

If Not xRange Is Nothing Then
    For Each xCell In xRange
        xHold = xCell
        If xHold <> "" Then
            xlength = Len(xHold)
            If xlength > 1 And xlength < 6 Then
                xLast = LCase(Mid(xHold, xlength, 1))
                If xLast = "a" Or xLast = "p" Then
                    Select Case xlength
                        Case 2
                            xHold = "00:0" & Mid(xHold, 1, 1)
                        Case 3
                            xHold = "00:" & Mid(xHold, 1, 2)
                        Case 4
                            xHold = "0" & Mid(xHold, 1, 1) & ":" & Mid(xHold, 2, 2)
                        Case 5
                            xHold = Mid(xHold, 1, 2) & ":" & Mid(xHold, 3, 2)
                    End Select
                    xHold = xHold & ":00"
                    xTime = "Not a time"
                    On Error Resume Next
                        xTime = TimeValue(xHold) + IIf(xLast = "p", 0.5, 0)
                    On Error GoTo 0
                    If xTime <> "Not a time" And xTime < 1 Then
                        Application.EnableEvents = False
                            xCell = xTime
                        Application.EnableEvents = True
                    End If
                End If
            End If
        End If
    Next
End If

End Sub

Open in new window

Regards,
Brian.
Convert-Time.xlsm
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38795536
Folks,

There are a few differences (of interpretation?) between ssaqibh (first time) and myself (second time) - I'v shown the times in 24-hour clock to make them clear...
(1) 1234a    --> 00:34              12:34  
(2) 1234p    --> 12:34              Invalid (so no change).
(3) 299a      --> VBA error      Invalid (so no change).

I think I prefers ssaqibh's choice for (1) and (2), but that's Robert's call.

Another difference is that mine handles multiple updates, whereas they cause a VBA error in ssabih's.

Regards,
BRian.
0
 

Author Comment

by:rws1
ID: 38795538
Thank you so much your code it works, however, one more thought if I enter more than one entry in a column and delete them all at once, or enter a wrong entry, ie, 656aa. Then the error debug window pops up any thoughts to stop that would appreciated.

Please see your attached and edited file.

Once again, thank you,
Robert
Timecon-a.xlsm
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38795560
Robert,

Please see my version. - it doesn't mind multiple changes.

Regards,
Brian.
0
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 300 total points
ID: 38795563
Try changing to
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then
        For Each cel In Target.Cells
            Call Worksheet_Change(cel)
        Next cel
    Else
        If Target.Column = 4 Or Target.Column = 6 Or Target.Column = 10 Then
            If Right(Target, 1) = "p" Or Right(Target, 1) = "a" Then
                Target = CVDate(Replace(Left(Target, Len(Target) - 1) / 100, ".", ":") & Right(Target, 1) & "m")
            End If
        End If
    End If
End Sub

Open in new window

0
 

Author Comment

by:rws1
ID: 38795575
Thank you so much , both of your codes worked great.
Robert
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38795576
ssaqibh - to avoid problems with invalid dates, you may want to insert an "On Error Resume Next" before the CVDate line.
0
 

Author Closing Comment

by:rws1
ID: 38795581
Excellent is an understatement, thank you, not sure how to proceed with the points, hope it is correct and no one is offended.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38795582
Thanks, Robert, but please watch out for the different results (apparently a difference in workings between the two Excel functions, TimeValue and CVDate).

Regards,
Brian.
0
 

Author Comment

by:rws1
ID: 38795642
ssaqibh, could you please send a code with the On Error Resume Next statement in the code?

to avoid problems with invalid dates, you may want to insert an "On Error Resume Next" before the CVDate line.

Thank you
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38797753
rws1,

In case ssaqibh doesn't notice your request...
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then
        For Each cel In Target.Cells
            Call Worksheet_Change(cel)
        Next cel
    Else
        If Target.Column = 4 Or Target.Column = 6 Or Target.Column = 10 Then
            If Right(Target, 1) = "p" Or Right(Target, 1) = "a" Then
                On Error Resume Next
                    Target = CVDate(Replace(Left(Target, Len(Target) - 1) / 100, ".", ":") & Right(Target, 1) & "m")
                On Error GoTo 0
            End If
        End If
    End If
End Sub

Open in new window

Regards
Brian.
0
 

Author Comment

by:rws1
ID: 38798581
Thank you Brian
Robert
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question