Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Excel; Input minute and second
- Thread starter olekr
- Start date
I don't think there is a way using built in functionality. You could probably write VBA code to help but that would have its own difficulties.
Perhaps easier would be to divide the input by 60 before processing it. e.g. if input in cell A1 type in B1 =A1/60
Gavin
Perhaps easier would be to divide the input by 60 before processing it. e.g. if input in cell A1 type in B1 =A1/60
Gavin
DaleWatson123321
Programmer
Ole,
I believe the following might be the only non-VBA and non-formula method:
1) Format the cell(s) as Custom: mm:ss
2) When entering the hours:minutes, preface the entry with "0" hours. - e.g. 0:12:15
I hope this helps.
Please advise as to how it fits.
Regards, ...Dale Watson dwatson@bsi.gov.mb.ca
I believe the following might be the only non-VBA and non-formula method:
1) Format the cell(s) as Custom: mm:ss
2) When entering the hours:minutes, preface the entry with "0" hours. - e.g. 0:12:15
I hope this helps.
Please advise as to how it fits.Regards, ...Dale Watson dwatson@bsi.gov.mb.ca
DaleWatson123321
Programmer
Correction...
Steps #2 should have said:
2) When entering the minutes:seconds, preface the entry with "0" hours. - e.g. 0:12:15
Steps #2 should have said:
2) When entering the minutes:seconds, preface the entry with "0" hours. - e.g. 0:12:15
- Thread starter
- #5
Just out of curiosity, if you could enter 12:15 and have it interpreted as mm:ss, what would you propose as the way to enter twelve hours and fifteen minutes?
With relatively simple macros, you could design any input format you care to and have it adjust accordingly. For example you could program Excel to convert 12m15 to 0:12:15 and convert 12h15 to 12:15:00.
To make it easy on the user (assuming he uses the 10-key pad which has a plus sign handy), you could specify a format of mm+ss, or 12+15 your example. (Even easier than typing 12:15 since it doesn't require the shift key and all of the keys are together under the hand.) The macro below would make the necessary transformation:
======================================================
======================================================
The above is only an example, in a production version you would need to allow the user to enter variations such as m+s or m+ss or mm+s. Also, you may need to allow for decimal fractions of seconds.
With relatively simple macros, you could design any input format you care to and have it adjust accordingly. For example you could program Excel to convert 12m15 to 0:12:15 and convert 12h15 to 12:15:00.
To make it easy on the user (assuming he uses the 10-key pad which has a plus sign handy), you could specify a format of mm+ss, or 12+15 your example. (Even easier than typing 12:15 since it doesn't require the shift key and all of the keys are together under the hand.) The macro below would make the necessary transformation:
======================================================
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Code:
' Convert user entry of mm+ss as if had been entered
' as a time value of 0:mm:ss
Code:
With Target
If Mid(.Text, 3, 1) = "+" Then
.Value = TimeValue("0:" + Left(.Text, 2) + ":" + Right(.Text, 2))
.NumberFormat = "h:mm:ss"
End If
End With
End SubThe above is only an example, in a production version you would need to allow the user to enter variations such as m+s or m+ss or mm+s. Also, you may need to allow for decimal fractions of seconds.
"Isn't strange that with this multipurpose-stateoftheart-$$$-software, is not possible to write 12:15 without typing 0:12:15? Hmmm..Gates:Still a way to go!"
I for one would get pretty upset if every time I entered 12:15 it assumed I meant 12 Minutes, 15 Secs as I generally work in hours and minutes.
I suppose what you are looking for is to be able to enter 12m15 and it to be converted to 00:12:15. I have no idea if a visual basic routine could do that.
I for one would get pretty upset if every time I entered 12:15 it assumed I meant 12 Minutes, 15 Secs as I generally work in hours and minutes.
I suppose what you are looking for is to be able to enter 12m15 and it to be converted to 00:12:15. I have no idea if a visual basic routine could do that.
PeterMoran
MIS
Hi olekr,
I can only suggest a variation on what Zathras has already done. Chip Pearson has coding for the quick entry of dates and times without slashes or colons which you might like to peruse and modify to suit your needs at:
Good Luck!
Peter Moran
Two heads are always better than one!
I can only suggest a variation on what Zathras has already done. Chip Pearson has coding for the quick entry of dates and times without slashes or colons which you might like to peruse and modify to suit your needs at:
Good Luck!
Peter Moran
Two heads are always better than one!
- Thread starter
- #9
Hi folks!
"Just out of curiosity, if you could enter 12:15 and have it interpreted as mm:ss, what would you propose as the way to enter twelve hours and fifteen minutes?"
--See your point, Zathras, and my suggestion will be like Gavonas: Just enter 12m15. And what about 15s instead of 0:0:15? But sure, its *not* a big deal!
Regs Ole
"Just out of curiosity, if you could enter 12:15 and have it interpreted as mm:ss, what would you propose as the way to enter twelve hours and fifteen minutes?"
--See your point, Zathras, and my suggestion will be like Gavonas: Just enter 12m15. And what about 15s instead of 0:0:15? But sure, its *not* a big deal!
Regs Ole
- Status