Entering Time data in excel in mm:ss format (without the leading 0: for the hours)

Question

I want to enter time data into excel sheet in mm:ss format. The problem is - that although the cell format is mm:ss, if I enter the data like this:

12:33 the actual value stored is 12 hours and 33 min. if I want 12 min and 33 sec, I need to enter 0:12:33

Is there a way to make the cell accept 12:33 as mm:ss ?

To display minutes larger than 59, format the cell as [MM]:SS. Like: 02:10:45 would then display as 130:45 instead. But apparently that does not work for inputs. — Arjan, Jan 20 '11 at 09:16

wilson · Answer 1 · 2017-07-17T02:44:21.960

16

As answered by Steve, Excel interpret user input 12:33 as 12h33m. You cannot change this behavior of how Excel accepts user input.

As you mentioned in comment, users would input 12:33 but meaning 12m33s.

Here is an workaround solution to get the correct value for calculation.

Format cell A1 (user input, eg. 12:33) as [h]:mm
Enter formula =A1/60 in cell B1
Format cell B1 as [m]:ss

The displayed value should be the same in A1 & B1. However, B1 will be the actual value you want.

edited Jul 17 '17 at 02:44

answered Jan 21 '11 at 03:40

wilson

5,002
2
22
39

2

Nice. Or: format the input as a string/text value, and use something like TIMEVALUE to convert it. – Arjan Jan 24 '11 at 16:35
1

The displayed values in A1 & B1 aren't the same if mm>23 – Alaa M. Jul 13 '17 at 07:20
1

@AlaaM. thanks for your finding. the answer is updated to fix the issue. use [h] and [m] – wilson Jul 17 '17 at 02:45

score 9 · Accepted Answer · answered Jan 20 '11 at 08:58

9

Time is universally entered in Hours:Minutes:Seconds format, so you can't change this entry format. You can obviously change the display format, but unfortunately you'll need to abide by the entry format.

answered Jan 20 '11 at 08:58

Steve

2,831
17
68
120

1

it looks like there is no solution to my problem, as even using scripts won't help, since the formatting masks what the user actually types. – Dani Jan 20 '11 at 12:50
So is the universal format hh:mm:ss or h:mm:ss? @Steve – Shayan May 08 '19 at 15:12
I would say it is h:mm:ss @Shayan – Steve May 17 '19 at 04:03

score 4 · Answer 3 · answered Mar 05 '13 at 12:01

There is a solution! Place this VBA code in your worksheet module, i.e.:

Open the Visual Basic Editor (Alt-F11)
In the top left treeview, double click on the sheet you want to enter your times
In the central code panel, place the below code.
Close VBE

Use this code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Value < 0 Or Target.Value > 1 And Target.NumberFormat <> "h:mm" Then Exit Sub
    Application.EnableEvents = False
    Target.Value = Target.Value / 60
    Target.NumberFormat = "mm:ss"
    Application.EnableEvents = True
End Sub

In case you already formatted the range you're entering the data in, use this line instead as the first line:

If Target.Value < 0 Or Target.Value > 1 Then Exit Sub

Note that this will change the value and format - every time you enter either a time - or something that is between 0 and 1! If you want to restrict it to a certain column, add this line:

If Target.Column <> 3 Then Exit Sub

or this line to restrict it to a certain range

If Intersect(Target, Range("A2:A100") Is Nothing Then Exit Sub

score 3 · Answer 4 · edited Sep 13 '13 at 14:49

3

Easiest way to work out hours completed

Format the Cell (A1) hh:mm

Input as normal (07:22)

Format the Cell (B1) hh:mm

Input as normal (16:00)

Format the Cell (C1) h:mm

Input all data into cell (C1) the following: =(A1+B1)/60

edited Sep 13 '13 at 14:49

BlueBerry - Vignesh4303

8,175

answered Sep 13 '13 at 13:09

Don_T

31

At first I thought this was a more cumbersome duplicate of wilson's answer, which suggests using separate display column that shows the number in the data entry column divided by 60. But on a closer look it's not even clear to me what this is purported to accomplish, much less how it pertains to the question. The question wasn't about determining the total hours of two columns (which I presume is what you mean by "work out hours completed"), but your answer doesn't accomplish that anyway, it takes the total of A and B and displays the hours portion as minutes. – Adi Inbar Jul 27 '23 at 21:42

score 1 · Answer 5 · edited Mar 26 '17 at 07:33

I find it best to enter the minutes in column A then the seconds in column B. Then you can Sum the columns. All as normal number format.

Next convert seconds > 60 to minutes by dividing the sum in col B by 60 and add the INT to the Col A Sum and leave the remainder MOD in col B.

Alternatively you can have col C as decimal number by having a formula = (A1+(B1/60)) will give decimal minutes. At the bottom of Col C do a sum then apply a formula to convert the decimal portion back to Seconds as follows say C22 is where the sum is held then in C23 enter formula =MOD(C22,1)*60.

Now in A23 enter formula =INT(C22) to show Total minutes including those from the sum in C22.

score 1 · Answer 6 · answered Nov 28 '17 at 06:30

I've had the same issue and here's the solution I've found: Enter the formula using the TIME function, which creates a time matrix as =TIME(H,M,S) and format it as you please. You still have to enter 0 hours every time because maths but this makes the whole thing infinitely more workable.

score 1 · Answer 7 · answered Oct 27 '14 at 03:22

Highlight the cell(s)/column which you want as Duration, right click on the mouse to "Format Cells". Go to "Custom" and look for "h:mm" if you want to input duration in hour and minutes format. If you want to include seconds as well, click on "h:mm:ss". You can even add up the total duration after that.

Hope this helps.

score 0 · Answer 8 · edited Mar 10 '14 at 21:07

0

Solution to above. When using the h:mm format, excel will not let you go above 23. Add brackets to the hours in your format [h]:mm and you are free to go above 23.

To calculate the value in seconds, set the cell to "general" number format and then type the following formula

=HOUR(A1)*60+MINUTE(A1)

edited Mar 10 '14 at 21:07

Excellll

12,717

answered Mar 10 '14 at 20:34

yakshi one

1

The question had nothing to do with calculating the number of seconds of a time value shown in hours and minutes. It was about changing how Excel interprets data entry of time values, specifically, how to get Excel to show a time value entered as two numbers separated by a colon as minutes and seconds rather than hours and minutes. It's not about calculating anything. – Adi Inbar Jul 27 '23 at 21:53

Entering Time data in excel in mm:ss format (without the leading 0: for the hours)

8 Answers8

Linked

Related