How to fix incorrect TIMEVALUE() tiem format?

[didj]

Hi all, I have a column of time data imported from csv which is formatted as general. eg: 02:35 = 2 minutes, 35 seconds. This data is not time of day, but runtime since start of data recording. I want to convert all values to absolute seconds. Typically I use the *86400 method to do this.

The problem is that when using the TIMEVALUE() function, it appears to recognise the data as hh:mm. Therefore, to convert to an integer I must multiply by 86400/60 = 1440, but as soon as the datafile goes past 24 min, it recognises this instead as 24 hr and then resets back to zero.

I've been trying

runTime = ActiveCell.Value
runTime = Format(runTime, "mm:ss")

but to no avail since this still returns hours/mins instead of mins/sec.

Can anyone suggest a workaround?

 

[mole999]

have you tried [h]:mm:ss as cell format

I don't recognise 24 minutes as an issue, but 24 hours can cause a problem if the format isn't set right

 

[didj]

Hi mole999, even if the time values are first formatted to "[h]:mm:ss" and then the timevalue() function is used, it recognises the minute values as hours. Therefore, converting with the timevalue() function and multiplying by 86400, a value of 24:06 (ie: 24 mins, 6 seconds) for example, will return 360 ie: 6 minutes past midnight.

I just discovered that if I manually enter "00:" before the value of 24:06, then I get the right number which is 1446 seconds. Seems like this will have to be the workaround. Add 00: at the start of every cell. Seems rather clunky though, and certainly I will write to the manufacturers and tell them to lift their game (just export the file with time format [h]:mm:ss).

Now what is the most efficient way to add "00:" at the start of a string?