C
Cletus
I have a spreadsheet in excel that I record length of time in. How can I
enter the time and have the : automatically entered for me?
enter the time and have the : automatically entered for me?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
R
Rick Rothstein
If you are talking about entering the current time (of day), then Jacob has
given you your answer. If, on the other hand, you mean a time other than the
current time (such as would be taken from a log sheet of some kind), then
you will need a macro to do what you want. Are you entering 24-hour time
values (1600 for 4:00pm) or would you enter 4:00pm as 400pm (or perhaps 400
pm with a space between them)? Are you entering the seconds as well (and, if
so, will they always be available for each entry or not)?
given you your answer. If, on the other hand, you mean a time other than the
current time (such as would be taken from a log sheet of some kind), then
you will need a macro to do what you want. Are you entering 24-hour time
values (1600 for 4:00pm) or would you enter 4:00pm as 400pm (or perhaps 400
pm with a space between them)? Are you entering the seconds as well (and, if
so, will they always be available for each entry or not)?
C
Cletus
I don't need the current time entered. I have varying times and it would be
so much more convenient if we didn't have to manually enter the colon. I
tried formatting the cell but that doesn't work
so much more convenient if we didn't have to manually enter the colon. I
tried formatting the cell but that doesn't work
J
Jacob Skaria
=NOW()
formatted to time should change automatically
If this post helps click Yes
formatted to time should change automatically
If this post helps click Yes
T
T. Valko
Another trick...
**Temporarily** set up an AutoCorrect option to replace a decimal point with
the colon. Then enter the time in 24 hr format using the decimal point
instead of the colon. Most people probably use the numeric keypad for
entering numbers. It's a lot more ergonomic to hit the decimal point key
than to have to reach over to the qwerty keys and do <shift> colon.
Just remember to reset the AutoCorrect option when you're done. Then
reformat the times as desired.
**Temporarily** set up an AutoCorrect option to replace a decimal point with
the colon. Then enter the time in 24 hr format using the decimal point
instead of the colon. Most people probably use the numeric keypad for
entering numbers. It's a lot more ergonomic to hit the decimal point key
than to have to reach over to the qwerty keys and do <shift> colon.
Just remember to reset the AutoCorrect option when you're done. Then
reformat the times as desired.
C
C
I am entering 24hr time values - when I enter "1315" and enter I would like
it to automatically change to "13:15"
it to automatically change to "13:15"
T
T. Valko
R
Rick Rothstein
Assuming you only want to enter hours:minutes (that is, no seconds), try
this macro...
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("C:C")) Is Nothing Then Exit Sub
Application.EnableEvents = False
On Error GoTo BadEntry
Target.NumberFormat = "hh:mm"
Target.Value = CDate(Format(Target.Value, "00\:00"))
Application.EnableEvents = True
Exit Sub
BadEntry:
Target.Value = CVErr(xlErrValue)
Application.EnableEvents = True
End Sub
As coded, the first statement is restricting the functionality to Column C
(change the Range statement to the actual range you want covered by this
functionality). To install this macro, right click the tab at the bottom of
the worksheet that you want this functionality on, select View Code from the
popup menu and copy/paste the above code into the code window that appeared.
this macro...
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("C:C")) Is Nothing Then Exit Sub
Application.EnableEvents = False
On Error GoTo BadEntry
Target.NumberFormat = "hh:mm"
Target.Value = CDate(Format(Target.Value, "00\:00"))
Application.EnableEvents = True
Exit Sub
BadEntry:
Target.Value = CVErr(xlErrValue)
Application.EnableEvents = True
End Sub
As coded, the first statement is restricting the functionality to Column C
(change the Range statement to the actual range you want covered by this
functionality). To install this macro, right click the tab at the bottom of
the worksheet that you want this functionality on, select View Code from the
popup menu and copy/paste the above code into the code window that appeared.