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.

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.
--
John C


"Farias" wrote:

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.

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

"Farias" wrote in message
...
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.

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:

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.

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


Replace @mailinator.com with @tiscali.co.uk


"Farias" wrote in message
...
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.

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" wrote in message
...
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.

Thanks David

This is what I was looking for!



"David Biddulph" wrote:

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

"Farias" wrote in message
...
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.