A Completely Different Time and Decimals Question

[Nicolai100]

Hello everyone,

I've seen quite a few posts about converting decimals to other time formats, but none that match my situation.

I am importing data from a data base (that I cannot edit) and am trying to subtract two time values. My problem is that the time is expressed in the form of a decimal, but instead of 1.50 equalling 1:30, 1.50 equals 1:50.

As long as the time isn't at the top of the hour, I don't have a problem (I format as text, Find/Replace "." with ":" and format as time), but if the time is 10:00, it is displayed as "10", no decimal point to find and replace. I've tried formatting as Number with 2 decimal places, but the Find/Replace doesn't recognize the 'non-entered' decimal and doesn't replace it, neither does paste special.

Any thoughts on what I can do? I know I could manually enter the information, but that is 10 to 14 thousand entries a month; I don't have that kind of time. Any help would be greatly appreciated.

Thank you in advance,
Matthew

 

[shg]

To convert such values to time, =DOLLARDE(A1,60)/24 and format as time.

To subtract and return the result as time,

=(DOLLARDE(A2, 60) - DOLLARDE(A1,60))/24

 

[Nicolai100]

That works perfect. Thank you so much!