Excel timing help please!!!

[e_sauven]

Hi,

I am trying desperately to change the format of the time I have copied and pasted off a website to do some analysis.

The time looks like 11:07 (11 mins, 07 sec), but when I paste it into excel it reads it as 11hours and 7 minutes.

All the posts I have read can convert this time, but I cannot seem to get excel to acknowledge the time as minutes and seconds.

Any help greatly appreciated!

thanks Emilie

 

[Legacy 234512]

What happens when you right click the cell and select format cells. Then on the left hand-side select custom and type `mm:ss`

 

[e_sauven]

Hi mehowski,

It changes the time format into e.g. 11:07 into 07:00

 

[Giorgi]

e_sauven

try this:
=TIME(0,HOUR(A1),MINUTE(A1)) and then choose custom time format mm:ss

 

[e_sauven]

e_sauven

try this:
=TIME(0,HOUR(A1),MINUTE(A1)) and then choose custom time format mm:ss

Thanks so much, that's brilliant!

 

[joeu2004]

=TIME(0,HOUR(A1),MINUTE(A1)) and then choose custom time format mm:ss

Thanks so much, that's brilliant!

As long as minutes -- HOUR(A1) -- does not exceed 23. More generally:

=--TEXT(A1,"[h]:mm.0")

formatted as Custom [m]:ss

That works for up to 9999 minutes. The double negative (--) converts text to numeric time.

As you discovered, Excel always interprets data entry of the form xx:yy as xx hours and yy minutes.

But 0:xx:yy and xx:yy.0 are interpreted as xx minutes and yy seconds.

xx can be up to 9999 in any of those forms.

 

[Jonmo1]

You might try

=A1/60

And format the cell to your desired time format.

 

[Giorgi]

joeu2004

Cool, Unfortunately, I ignored these nuances

Jonmo1

Here's what it means MVP - just make it easy

 

[e_sauven]

Thank you that's great as it will exceed 23.
Many thanks
Emilie

As long as minutes -- HOUR(A1) -- does not exceed 23. More generally:

=--TEXT(A1,"[h]:mm.0")

formatted as Custom [m]:ss

That works for up to 9999 minutes. The double negative (--) converts text to numeric time.

As you discovered, Excel always interprets data entry of the form xx:yy as xx hours and yy minutes.

But 0:xx:yy and xx:yy.0 are interpreted as xx minutes and yy seconds.

xx can be up to 9999 in any of those forms.

 

[joeu2004]

=--TEXT(A1,"[h]:mm.0")
formatted as Custom [m]:ss

Thank you that's great as it will exceed 23. Many thanks

You're welcome. But I hope you did not overlook Jonmo1's simpler solution: =A1/60, formatted as Custom [m]:ss.

The point is: when we enter min:sec and Excel interprets that as hr:min, in effect Excel is multiplying our input by 60 because there are 60 minutes in an hour and 60 seconds in a minute. So we simply need to reverse that by dividing by 60.