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?

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?

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?

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

=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

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?

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?

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?

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?

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?

While I've not had to do anything like this myself, it would seem you can
make the "single dot to colon" replacement both position sensitive and
automatic without anyone having to remember anything. For example, the
following installed in the Workbook module should do the "single dot to
colon" replacement **only** for Column E on the worksheet named "Sheet3" and
treat the dot normally everywhere else...

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)
With Application.AutoCorrect
On Error Resume Next
If Sh.Name = "Sheet3" Then
If Target.Column = 5 Then
.AddReplacement ".", ":"
Else
.DeleteReplacement "."
End If
Else
.DeleteReplacement "."
End If
End With
End Sub

--
Rick (MVP - Excel)


"Ragdyer" wrote in message
...
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?

Hi Rick

That's a very neat method.
I wondered why you had chosen to put it in a workbook module, rather than
just place it in the relevant worksheet

Private Sub Worksheet_Change(ByVal Target As Range)
With Application.AutoCorrect
On Error Resume Next
If Target.Column = 5 Then
.AddReplacement ".", ":"
Else
.DeleteReplacement "."
End If
End With
End Sub

--
Regards
Roger Govier

"Rick Rothstein" wrote in message
...
While I've not had to do anything like this myself, it would seem you can
make the "single dot to colon" replacement both position sensitive and
automatic without anyone having to remember anything. For example, the
following installed in the Workbook module should do the "single dot to
colon" replacement **only** for Column E on the worksheet named "Sheet3"
and treat the dot normally everywhere else...

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)
With Application.AutoCorrect
On Error Resume Next
If Sh.Name = "Sheet3" Then
If Target.Column = 5 Then
.AddReplacement ".", ":"
Else
.DeleteReplacement "."
End If
Else
.DeleteReplacement "."
End If
End With
End Sub

--
Rick (MVP - Excel)


"Ragdyer" wrote in message
...
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?

Two things...

First, you can't use the Change event as your posted code shows because the
change in the AutoCorrect won't take place at the right time. Using the
Change event means that each time you enter Column E, the first change you
make into a cell in that column won't have the "dot to colon" AutoCorrect
replacement available (the replacement won't be installed into AutoCorrect
until *after* the entry is complete; hence, it won't go back and change the
just completed entry as it is no longer being typed). Worse yet, if you
switch to a different column *after* making a **single** (first) entry in
Column E, the entry in that new column will have the "dot to colon"
replacement feature active (the first entry turns it on for the next entry
and that second entry, being in a different column than E, won't turn it off
until *after* the entry has been completed). You must use the
SelectionChange event to get the functionality to stick to the selected
column. I'm assuming you meant that and just typed your code into your
message off the top of your head, but I wanted readers of the thread to
understand why the Change event was the wrong one to use.

Second, the reason why I used the SheetSelectionChange event in the workbook
module is because if you use the SelectionChange event in the relevant
worksheet module, that event will not be activated when you switch to a new
worksheet. So, if you enter Column E and then switch to another worksheet,
whether you make an entry in Column E or not (remember, we are using the
SelectionChange event, not the Change event), the AutoCorrect "dot to colon"
replacement will be active for that entire sheet... actually, for the rest
of the workbook until you return to Column E on the (relevant) worksheet,
and move to a different column on that relevant worksheet.

--
Rick (MVP - Excel)


"Roger Govier" <roger@technology4unospamdotcodotuk wrote in message
...
Hi Rick

That's a very neat method.
I wondered why you had chosen to put it in a workbook module, rather than
just place it in the relevant worksheet

Private Sub Worksheet_Change(ByVal Target As Range)
With Application.AutoCorrect
On Error Resume Next
If Target.Column = 5 Then
.AddReplacement ".", ":"
Else
.DeleteReplacement "."
End If
End With
End Sub

--
Regards
Roger Govier

"Rick Rothstein" wrote in message
...
While I've not had to do anything like this myself, it would seem you can
make the "single dot to colon" replacement both position sensitive and
automatic without anyone having to remember anything. For example, the
following installed in the Workbook module should do the "single dot to
colon" replacement **only** for Column E on the worksheet named "Sheet3"
and treat the dot normally everywhere else...

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)
With Application.AutoCorrect
On Error Resume Next
If Sh.Name = "Sheet3" Then
If Target.Column = 5 Then
.AddReplacement ".", ":"
Else
.DeleteReplacement "."
End If
Else
.DeleteReplacement "."
End If
End With
End Sub

--
Rick (MVP - Excel)


"Ragdyer" wrote in message
...
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?

Hi Rick

I wrote and posted without testing first.
Since trying it, I can see exactly what you mean and fully understand the
reason for your method.
I shouldn't have doubted a "true VB guy"<bg
--
Regards
Roger Govier

"Rick Rothstein" wrote in message
...
Two things...

First, you can't use the Change event as your posted code shows because
the change in the AutoCorrect won't take place at the right time. Using
the Change event means that each time you enter Column E, the first change
you make into a cell in that column won't have the "dot to colon"
AutoCorrect replacement available (the replacement won't be installed into
AutoCorrect until *after* the entry is complete; hence, it won't go back
and change the just completed entry as it is no longer being typed). Worse
yet, if you switch to a different column *after* making a **single**
(first) entry in Column E, the entry in that new column will have the "dot
to colon" replacement feature active (the first entry turns it on for the
next entry and that second entry, being in a different column than E,
won't turn it off until *after* the entry has been completed). You must
use the SelectionChange event to get the functionality to stick to the
selected column. I'm assuming you meant that and just typed your code into
your message off the top of your head, but I wanted readers of the thread
to understand why the Change event was the wrong one to use.

Second, the reason why I used the SheetSelectionChange event in the
workbook module is because if you use the SelectionChange event in the
relevant worksheet module, that event will not be activated when you
switch to a new worksheet. So, if you enter Column E and then switch to
another worksheet, whether you make an entry in Column E or not (remember,
we are using the SelectionChange event, not the Change event), the
AutoCorrect "dot to colon" replacement will be active for that entire
sheet... actually, for the rest of the workbook until you return to Column
E on the (relevant) worksheet, and move to a different column on that
relevant worksheet.

--
Rick (MVP - Excel)


"Roger Govier" <roger@technology4unospamdotcodotuk wrote in message
...
Hi Rick

That's a very neat method.
I wondered why you had chosen to put it in a workbook module, rather than
just place it in the relevant worksheet

Private Sub Worksheet_Change(ByVal Target As Range)
With Application.AutoCorrect
On Error Resume Next
If Target.Column = 5 Then
.AddReplacement ".", ":"
Else
.DeleteReplacement "."
End If
End With
End Sub

--
Regards
Roger Govier

"Rick Rothstein" wrote in message
...
While I've not had to do anything like this myself, it would seem you
can make the "single dot to colon" replacement both position sensitive
and automatic without anyone having to remember anything. For example,
the following installed in the Workbook module should do the "single dot
to colon" replacement **only** for Column E on the worksheet named
"Sheet3" and treat the dot normally everywhere else...

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)
With Application.AutoCorrect
On Error Resume Next
If Sh.Name = "Sheet3" Then
If Target.Column = 5 Then
.AddReplacement ".", ":"
Else
.DeleteReplacement "."
End If
Else
.DeleteReplacement "."
End If
End With
End Sub

--
Rick (MVP - Excel)


"Ragdyer" wrote in message
...
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?