Excel timing help please!!!

e_sauven

New Member
Joined
Apr 3, 2014
Messages
27
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
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
L

Legacy 234512

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

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,685
Office Version
2010
Platform
Windows
=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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
You might try

=A1/60

And format the cell to your desired time format.
 

e_sauven

New Member
Joined
Apr 3, 2014
Messages
27
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

Well-known Member
Joined
Mar 2, 2014
Messages
2,685
Office Version
2010
Platform
Windows
=--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.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,985
Messages
5,447,704
Members
405,464
Latest member
milindighe

This Week's Hot Topics

Top