mm:ss formatting

levy77

Board Regular
Joined
May 7, 2019
Messages
67
Does anyone have a solution to formatting mm:ss without the AM/PM after it. I'm creating a spreadsheet where I need to put time as an input, say for a race where something like 10:30 would be 10 minutes and 30 seconds. There are then some calculations done on the time which says if time is below 10 minutes the person gets 20 points, if above 20 minutes the person gets 0 points then uniformly distributed for all other values between 10 and 20 minutes. The cutoff points will also be dynamic, so that you could change the lower bound to something like 12:30, which would be 12 minutes and 30 seconds.

Is there also a way once the formatting is correct, to apply data validation so that the person must enter mm:ss exactly?

This sheet will be spread to a large number of different computers so I wont be able to just change the computers default time setting as this will be redundant when other people open the sheet.

Does anyone have any suggestions or direction?

Cheers.
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

gaz_chops

Well-known Member
Joined
Apr 29, 2003
Messages
6,485
Platform
  1. MacOS
To format - Right click on cell, select Format Cells, select custom, enter mm:ss
 

levy77

Board Regular
Joined
May 7, 2019
Messages
67
I've tried that, with that formatting, I put in 10:00, the result was the cell showed 30:00 and the value in the formula bar was 10:30:00 AM and the actual value was 0.44.
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,497
maybe
mmss.png
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,497

ADVERTISEMENT

I forgot to say you need enter full time format:
00:10:30
00:125:15
 

levy77

Board Regular
Joined
May 7, 2019
Messages
67
Hi Sandy, I have seen a solution similar to this but my system doesn't seem to like it, got any ideas?
For this one i entered 00:10:30.


Excel12.png
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,497

ADVERTISEMENT

No, this is NOT convert hours to minutes
as I said you need to enter 00: then minutes and seconds 10:30 or 125:15 (as in post#5)
but not 02:15:25

mmss.png

you need proper minutes and seconds in cell not in formula bar so you have it
formula bar doesn't matter in this case

[mm]:ss is a custom format
 
Last edited:

levy77

Board Regular
Joined
May 7, 2019
Messages
67
I entered 00:10:30, 00 hours, 10 minutes, 30 seconds. I used the [mm]:ss format and the output I got was that in the formula bar.
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,497
you need to test it
as you can see I entered 00:10:30 and got 10:30 in the cell but 00:10:30 in formula bar
I don't know how US style works (maybe change it in regional setting to normal without idi*** AM/PM) and use 24-hours clock
 
Last edited:

levy77

Board Regular
Joined
May 7, 2019
Messages
67
Thanks for your help, I ended up formatting as text then pulling the values using left and right.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,652
Messages
5,832,928
Members
430,175
Latest member
Sheenamarie

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top