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?
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?