How to be able to enter minutes & seconds as m:ss?

[JenniferMurphy]

Is there a way I can set the cell formatting so that I can enter time as minutes and seconds wsithout having to enter the hours?

For example, if the time I want to enter is 8 minutes and 30 seconds, I'd like to be able to enter "8:30". If I set the format to "m:ss" and then enter "8:30" in the cell, it displays "30:00", which is 8 hours and 30 minutes. To get the right time, I have to enter "0:8:30". I'd like to avoid having to enter the hours.

I suppose I can format it as "h:mm". Then "8:30" gets displayed as "8:30", but I have to remember that it's really hours, not minutes.

 

[awoohaw]

Yeah, as far as I know the hours need to be entered when inputting times, or at least the first value is. Even with the TIME(h,m,s) function.
Your suggestion is a good idea, you can then divide the column by 60 minutes to convert the value to minuetes and hours.

 

[JenniferMurphy]

Yeah, as far as I know the hours need to be entered when inputting times, or at least the first value is. Even with the TIME(h,m,s) function.
Your suggestion is a good idea, you can then divide the column by 60 minutes to convert the value to minuetes and hours.

Rats! That's what I was afraid of.

I wonder what the geniuses at M$FT were thinking, if they were thinking at all, when they decided that "8:30" entered in a cell formatted as "h:mm" should be treated as "8:30:00"?

 

[bebo021999]

Not sure I can understand 100% but for an input range , i.e A1:A100, if you input 8:30, or 8:30:00, only 8:30 (actual m:ss) is put.
If you want , for example, 1:01:00, try to input 61:00

Put this code into worksheet module, trigger any change in range A1:A100

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1:A100")) Is Nothing Then Exit Sub ' suppose input range is A1:A100. Change to actual range.
Application.EnableEvents = False ' turn off enable event
Target.Value = Target.Value / 60 ' convert input value from hour (defaul) to minute
Application.EnableEvents = True ' turn on enable event
End Sub

 

[JenniferMurphy]

Not sure I can understand 100% but for an input range , i.e A1:A100, if you input 8:30, or 8:30:00, only 8:30 (actual m:ss) is put.
If you want , for example, 1:01:00, try to input 61:00

Put this code into worksheet module, trigger any change in range A1:A100

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1:A100")) Is Nothing Then Exit Sub ' suppose input range is A1:A100. Change to actual range.
Application.EnableEvents = False ' turn off enable event
Target.Value = Target.Value / 60 ' convert input value from hour (defaul) to minute
Application.EnableEvents = True ' turn on enable event
End Sub

Wow! Very clever. It's sad that we have to resort to VBA code to get Excel to do what ought to be built in.