Get value of cell with [h]:mm:ss custom format 1

[mrsnrub]

Hi everyone,
I have a spreadsheet template where we dump some hours/minutes data from another application and then manipulate in VBA.

The format of the hours in this other application is hh:mm:ss. This cannot be changed. There is no date component, only the time.

When the hours/minutes exceeds 24 hours and the data is pasted into Excel, the format of the cell automatically changes to a custom format of [h]:mm:ss which I believe is correct to handle displaying hours over 24.

My problem is that when I attempt to get the value of this cell in VBA (using Range.Value), I can't seem to get anything other than a decimal representation. CDate fails due to exceeding 24 hours, and converting to string gives a string representation of a decimal equivalent.

For example: 141:12:00 = 5.883333.

The data is displayed correctly in the cell, does anyone know how I may be able to get what I can see in the cell into a variable so that I can then manipulate.

Eventually I need to convert this to a decimal equivalent, so if I could get this into a string I could then manually manipulate the string to convert to decimal.

Any thoughts?

Many thanks in advance.
Steve.

 

[PHV]

Use Range.Text instead of Range.Value

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mrsnrub]

Awesome, that will do the trick. Cheers.