value after comma as seconds

[zico8]

Hi,

I have the sheet with time measurements written as number and need to convert it to time format.

For example 3,40 means 3 minutes and 40 seconds.

How should I do it?

 

[vandango05]

is it just to display the times or do you carry out any calculations on them. E.G add times up, work out averages etc? Also how many numbers could appear in the minutes?

 

[zico8]

Thanks vandango05 for your reply,

I need to have possibility to make calculations from these values, so if I multiply 3,40 by 2 I should get 7:20 (instead of 6,80).

Number of minutes can be large size but it does not matter for me if it shows like 70:10 or 01:10:10

 

[AlanY]

try this with your time in A1

=INT(A1)+(A1-INT(A1))/0.6

 

[vandango05]

The issue is the comma in your text. You could start by removing this and splitting the time into mins and seconds in two columns. Example row 1 is headers, A = Time, B = Mins, C = Seconds.

With your first time in A2 put this in B2 =LEFT(A2,FIND(",",A2)-1) and this in C2 =RIGHT(A2,LEN(A2)-FIND(",",A2))

These formulae will split the time at the comma.

 

[zico8]

I've found the formula, it seems to work fine.

=(INT(A1)*60+MOD(A1;1)*100)/86400

Please let me know what do you think.

 

[AlanY]

have you tried the solution in post#4?

 

[zico8]

Yes, but I get wrong data.
What number format should I set for cell?