0

My question is how do you parse the time given in HH:MM:SS to HH and MM in separate columns in VBA?

Community
  • 1
  • 1
Tanya Mukhia
  • 1
  • 5
    Possible duplicate of [Excel String Parsing](https://stackoverflow.com/questions/12468726/excel-string-parsing) – kenny_k Dec 19 '17 at 11:46

3 Answers3

1
  • Record a macro.
  • Then use the formulas Minute() and Hour() to get what you need.
  • That is all.
Vityata
  • 42,633
  • 8
  • 55
  • 100
  • @TanyaMukhia - I guess we can. :) – Vityata Dec 22 '17 at 10:54
  • first I put n=0 and then Do While not EOF(1) and then input the datas from the file and supposed hrs=hour(time(N)) and min=minute(time(N)) and afterwards did N=N+1 and Loop and then closed the file. Later in another sub I displayed it in different columns & When I did it this way the time does show but all comes as 00:00:00 and even in separate columns the hrs and min comes as 0 throughout the entire row. So what sort of problem could have occurred? – Tanya Mukhia 4 mins ago edit – Tanya Mukhia Dec 22 '17 at 10:55
  • @TanyaMukhia - plenty of things. Write a new question, put a screenshot + code + description of current and expected output. I can bet that you will get an answer really soon. – Vityata Dec 22 '17 at 10:57
1

With constant data column A, try:

Sub parser206()
    Dim r As Range
    For Each r In Columns(1).Cells.SpecialCells(2)
        arr = Split(r.Text, ":")
        r.Offset(0, 1) = arr(1)
        r.Offset(0, 2) = arr(2)
    Next r
End Sub

enter image description here

Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • So I have to display the data from a .CSV file where time is in HH:MM:SS and display it on the spreadsheet as HH and MM separately. So will this code still work ? – Tanya Mukhia Dec 20 '17 at 07:32
0

Another way which has worked for me would be something like below:

Sub foo()
TextVar = Format(Sheet1.Cells(1, 1).Value, "hh:mm:ss") ' get the value from the cell and convert the value to the right format
varHours = Left(TextVar, 2) 'get the first two characters ie the Hours
varMinutes = Mid(TextVar, 4, 2) 'get the middle two characters ie the Minutes
varSeconds = Right(TextVar, 2) 'get the last two characters ie Seconds
MsgBox varHours & " " & varMinutes & " " & varSeconds
End Sub
Xabier
  • 7,587
  • 1
  • 8
  • 20
  • So I have to display the data from a .CSV file where time is in HH:MM:SS and display it on the spreadsheet as HH and MM separately. So will this be able to help to execute the result in different columns? – Tanya Mukhia Dec 20 '17 at 07:35
  • @TanyaMukhia yes it will, but it depends on how you are reading the CSV, if you can post some code as to how you read it, then I will update my answer to work with your code? If not, I could update my answer to include reading the CSV? Do let me know how you get on, and if you need some help... – Xabier Dec 20 '17 at 09:23
  • first I put n=0 and then Do While not EOF(1) and then input the datas from the file and supposed hrs=hour(time(N)) and min=minute(time(N)) and afterwards did N=N+1 and Loop and then closed the file. Later in another sub I displayed it in different columns & When I did it this way the time does show but all comes as 00:00:00 and even in separate columns the hrs and min comes as 0 throughout the entire row. So what sort of problem could have occurred? – Tanya Mukhia Dec 22 '17 at 10:49
  • Could you update your question with the code you are using, and then I will happily look at what the issue is and I'll update my answer? – Xabier Dec 22 '17 at 10:57