Time Conversion issue

[Farias]

Hi,

I have a Excel file with time values in a number of columns. The times are
in hh:mm (01:45) format but should be in mm:ss (01:45) format.

If i change the cell format to [hh]:mm:ss I get 25:45:00.

I divided by 60 and then changed the cell format to mm:ss but got 25:45,
also tried to divide it by 24 first to try to have only 1:45:00 but instead I
get 01:04:23

I hope this makes sence.

Thanks for your help.

 

[John C]

When you are entering the time, I assume you are entering as 1:45? the issue
is that when you enter time with the colon, it automatically assumes the
first digit is the hours. So in order to enter 1 minute 45 seconds you would
actually need to enter 0:1:45

Hope this helps.

 

[Marcelo]

assuming a2 has 01:45 (hh:mm)

=a2/60 format it as [h]:mm:ss

hth

--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Farias" escreveu:

 

[Sandy Mann]

Enter 60 in an unused cell then copy and Paste Special > Divide into the
cell, or cells, that you want to change. Finally reformat the cell as mm:ss

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Peo Sjoblom]

First of all what you have not only hours but also one day, one hour and 45
minutes

if you day/hour/minute is in A1 use this

=MOD(A1,1)/60

format as mm:ss or [mm]:ss if your minutes will be greater than 59:59
and copy down. Copy and paste special as values to make them static
For future entries enter a minute like 00:01:00


--


Regards,


Peo Sjoblom

 

[Farias]

Thanks David

This is what I was looking for!

25:45 is 1 day (24 hours) + 1 hour 45 minutes.
If you want to lose the complete days, use =MOD(A2,1) and that will turn it
from 25:45 to 1:45.
If you then want to re-interpret from hours and minutes to minutes and
seconds, you can then divide by 60, so =MOD(A2,1)/60 should give you
00:01:45
--
David Biddulph

Hi,

I have a Excel file with time values in a number of columns. The times
are
in hh:mm (01:45) format but should be in mm:ss (01:45) format.

If i change the cell format to [hh]:mm:ss I get 25:45:00.

I divided by 60 and then changed the cell format to mm:ss but got 25:45,
also tried to divide it by 24 first to try to have only 1:45:00 but
instead I
get 01:04:23

I hope this makes sence.

Thanks for your help.