VBA convert text time to actual time

[uk747]

Have code below which will convert times in col A to time in col B
Both columns are formatted as hh:mm

All the times in col A are 0:01 all the way up to 13:00

Dim x as integer

On error resume next ' in case the times in col A are actual times and not text

For x = 2 to 781
  Range("B" & x) = timevalue(range("A) & x))
Next x

Not sure if a bug or not but most of the times convert ok but some don't

I.e. did notice exactly 1:12 between each gap apart from 1st one 0:36 to 01:12
00:36 00:25
01:12 00:05
02:24 00:01
03:36 00:15
04:48 00:02
06:00 00:25
07:12 00:03
08:24 00:35
09:36 00:04
10:48 00:45
12:00 00:05

 

[RoryA]

If your column A values actually have time values in them, rather than text, your code is not processing any of them apart from the ones that happen to be decimals that could be interpreted as times. For example:
00:36 = 0.025 which is interpreted as 00:25
01:12 = 0.05 which becomes 00:05
02:24 = 0.1 which becomes 00:01

 

[uk747]

so in the example in Post 1, if they range from 00:00 to 13:00 How can I make it ignore them if proper times including ones that might be decimal and only convert them if text so that 0:36 if text will show as 00:36 and not 00:25

 

[RoryA]

You could do something like this:

   For Each cell In Range("A2:A781").Cells
      If cell.Value < 1 Then
         cell.Offset(, 1).Value = cell.Value
      Else
         cell.Offset(, 1).Value = TimeValue(cell.Value)
      End If
   Next

 

[uk747]

Thanks Rory