If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
ENTERING TIME
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? |
#2
|
|||
|
|||
ENTERING TIME
If you are looking to enter the time (static) try the below key combination
Ctrl+Shift+semicolon If you are looking to have a time that will update by itself then use the formula =NOW() and format that to any time format you prefer. -- If this post helps click Yes --------------- Jacob Skaria "Cletus" wrote: 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? |
#3
|
|||
|
|||
ENTERING TIME
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)? -- Rick (MVP - Excel) "Cletus" wrote in message ... 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? |
#4
|
|||
|
|||
entering time
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 |
#5
|
|||
|
|||
entering time
=NOW()
formatted to time should change automatically If this post helps click Yes --------------- Jacob Skaria "Cletus" wrote: 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 |
#6
|
|||
|
|||
ENTERING TIME
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. -- Biff Microsoft Excel MVP "Rick Rothstein" wrote in message ... 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)? -- Rick (MVP - Excel) "Cletus" wrote in message ... 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? |
#7
|
|||
|
|||
ENTERING TIME
I am entering 24hr time values - when I enter "1315" and enter I would like
it to automatically change to "13:15" "Rick Rothstein" wrote: 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)? -- Rick (MVP - Excel) "Cletus" wrote in message ... 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? |
#8
|
|||
|
|||
ENTERING TIME
See this:
http://www.cpearson.com/Excel/DateTimeEntry.htm -- Biff Microsoft Excel MVP "C" wrote in message ... I am entering 24hr time values - when I enter "1315" and enter I would like it to automatically change to "13:15" "Rick Rothstein" wrote: 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)? -- Rick (MVP - Excel) "Cletus" wrote in message ... 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? |
#9
|
|||
|
|||
ENTERING TIME
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. -- Rick (MVP - Excel) "C" wrote in message ... I am entering 24hr time values - when I enter "1315" and enter I would like it to automatically change to "13:15" "Rick Rothstein" wrote: 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)? -- Rick (MVP - Excel) "Cletus" wrote in message ... 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? |
#10
|
|||
|
|||
ENTERING TIME
Expanding on that old trick:
When the girls in the office where finished entering the time card data on Monday morning for the previous week, they always forgot to change back until they noticed the mistakes they were making (usually wasting a half hour's work). So now we use the AutoCorrect to replace *2* decimals with a colon. 12..15 is just about as easy to use as 12.15, AND, some of them never have to change back, leaving it in force indefinitely. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "T. Valko" wrote in message ... 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. -- Biff Microsoft Excel MVP "Rick Rothstein" wrote in message ... 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)? -- Rick (MVP - Excel) "Cletus" wrote in message ... 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? |
|
Thread Tools | |
Display Modes | |
|
|