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

 

[bebo021999]

Try:
Range("B" & x) = timevalue(range("A" & x))

If it does not work, what is your expected results?

 

[uk747]

Sorry it was Range("B" & x) = timevalue(range("A" & x))
It was a typo when I entered it here

Expected results should be the same as col A
It's just the ones above aren't

I have every value from 0:01 to 13:00 going up in 1 min increments. Just not sure why certain ones i.e 03:36 shows as 00:15 when it should show as 03:36

 

[bebo021999]

Or try to add .value after range?

Range("B" & x).value = timevalue(range("A" & x).value)

or even:

Range("B" & x).value = range("A" & x).value

Does it work?

 

[uk747]

First 2 don't work. If real times they are blank except values 00:36 01:12 02:24 03:36 and every value going forward if you add 1:12

Last one seems to work although I thought you was supposed to use timevalue to convert, also why does 00:36 and every value that is divisible by 1:12 not work???

On error resume next

Range("B" & x).value = timevalue(range("A" & x)) ' doesn't work if real times. 

Range("C" & x).value = timevalue(range("A" & x).value) ' doesn't work if real times

Range("D" & x).value = range("A" & x).value ' works

 

[uk747]

Anybody know why sometimes times divisible by 1:12 show weird times

 

[uk747]

Bump

 

[uk747]

Bump

 

[RoryA]

If the value in column A is already a time, not text, you will have problems. (Note: this doesn't cause an error so your OERN statement is unnecessary, and rarely a good idea when used like that). 3:36 has the numeric value 0.15, which is then converted to 00:15 by TimeValue.

 

[uk747]

Ok but what about all the other values. It's only the times I posted in thread1 that have issues

I.e. 03:36 shows 0:15
but 3:37 3:38 3:39 etc show the proper times

If you run the code in thread 1 everything is ok except times divisible by 1:12