Menu

please help.is it possible re-edit macro to convert text like1530-2200 to time format automatically? i got macro which works with 4 digits

Search this Thread

#1

Report Post

Posted to microsoft.public.excel.misc

Claus Busch Claus Busch is offline

Claus Busch is offline

external usenet poster

Posts: 3,872

Default

please help.is it possible re-edit macro to convert text like 1530-2200 to time format automatically? i got macro which works with 4 digits like 1234 to 12:34 ,but i don't know how to make it work for 4 digits dash and 4 digits?

Hi Michael,

Am Wed, 22 Jan 2014 05:29:30 -0800 (PST) schrieb :

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim TimeStr As String

try:

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("A1:A10")) Is Nothing _
Or Target.Count 1 Then Exit Sub

Dim myTime As Double

Application.EnableEvents = False
Select Case Len(Target)
Case 1, 2
myTime = Target / 1440
Case 3
myTime = Left(Target, 1) / 24 + Right(Target, 2) / 1440
Case 4
myTime = Left(Target, 2) / 24 + Right(Target, 2) / 1440
Case 5
myTime = Left(Target, 1) / 24 + Mid(Target, 2, 2) / 1440 + _
Right(Target, 2) / 86400
Case 6
myTime = Left(Target, 2) / 24 + Mid(Target, 3, 2) / 1440 + _
Right(Target, 2) / 86400
Case Is 6
myTime = 0
End Select
If myTime 0 Then
Target = myTime
Target.NumberFormat = "h:mm:ss"
Else
Target.Clear
Target.NumberFormat = "General"
MsgBox "You did not enter a valid time"
End If
Application.EnableEvents = True
End Sub

Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Reply With Quote

Reply With Quote

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Posting Rules
Smilies are On [IMG] code is On HTML code is Off Trackbacks are On Pingbacks are On Refbacks are On

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Convert numbers stored as text to numbers - Automatically - Macro	ExceLearner	New Users to Excel	2	December 12th 12 07:14 PM
macro that works one time every day	Spiros	Excel Discussion (Misc queries)	2	September 19th 08 12:00 PM
How do I convert a time to text format.	Rick	Excel Discussion (Misc queries)	4	February 20th 06 11:02 PM
convert time imported as text to time format for calculations	batfish	Excel Worksheet Functions	3	October 27th 05 11:24 PM
How do I convert time format to text?	Andrew	Excel Discussion (Misc queries)	1	February 16th 05 07:07 PM

All times are GMT +1. The time now is 08:13 PM.

About Us

"It's about Microsoft Excel"

Contact Us ExcelBanter forum home Privacy Statement