rws1
asked on
Convert numbers to time
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
Does this need to be done with vba or can it be formatted as such?
Thank you for your help,
Robert
ASKER
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.
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.
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
This works for column 1. For an other column change line 2
ASKER
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
Robert
Try this file
Timecon.xlsm
Timecon.xlsm
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
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
Please see your attached and edited file.
Once again, thank you,
Robert
Timecon-a.xlsm
Robert,
Please see my version. - it doesn't mind multiple changes.
Regards,
Brian.
Please see my version. - it doesn't mind multiple changes.
Regards,
Brian.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you so much , both of your codes worked great.
Robert
Robert
ssaqibh - to avoid problems with invalid dates, you may want to insert an "On Error Resume Next" before the CVDate line.
ASKER
Excellent is an understatement, thank you, not sure how to proceed with the points, hope it is correct and no one is offended.
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.
Regards,
Brian.
ASKER
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
to avoid problems with invalid dates, you may want to insert an "On Error Resume Next" before the CVDate line.
Thank you
rws1,
In case ssaqibh doesn't notice your request...
Brian.
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
RegardsBrian.
ASKER
Thank you Brian
Robert
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.