Enter time with . but display as :

  • Thread starter Thread starter harwookf
  • Start date Start date
H

harwookf

Hi
I would like to enter time into a timesheet using the decimal point instead
of the colon as it makes it easier and quicker to type, however I still want
it to display correctly with the colon.
For example - if I enter 12.35 I would like it to display as 12:35 so it is
literally just changing the . to :
Many thanks in advance.
 
You'll probably want to delete this once you are done..

Go to Tools - Autocorrect Options

Replace . with :
 
Another way, with 12.35 in A1, in B1 enter:

=--SUBSTITUTE(A1,".",":") and format B1 as Time.
 
I appreciate the fast response and as this is required on a daily basis, this
is the best way I think. However, I am struggling with it.

I use Excel 2007 so not sure if this makes a difference. I changed the
range initially to match where I will be entering the data, but it is not
working. E.g. if I type 135 in a cell, it shows 3240:00 instead of 1:35. The
cell is formatted as [h]:mm.

What am I doing wrong?
 
It is sheet event code. I just tested with both 2003 and 2007 and they both
came up the same way as

1:35:00 AM or 01:35 depending on the format you choose

Did you put the code in the sheet (right click the sheet tab and select view
code and paste in the macro there,
then change this part


If Application.Intersect(Target, Range("A1:A10")) Is Nothing Then


to the range you want it to cover


Then close the VBA editor (alt + Q)


save the workbook and start typing



--


Regards,


Peo Sjoblom

harwookf said:
I appreciate the fast response and as this is required on a daily basis,
this
is the best way I think. However, I am struggling with it.

I use Excel 2007 so not sure if this makes a difference. I changed the
range initially to match where I will be entering the data, but it is not
working. E.g. if I type 135 in a cell, it shows 3240:00 instead of 1:35.
The
cell is formatted as [h]:mm.

What am I doing wrong?

Peo Sjoblom said:
If you want to do this on a regular basis you would be better off using
an
event macro


http://www.cpearson.com/excel/DateTimeEntry.htm

--


Regards,


Peo Sjoblom
Click to expand...
Click to expand...
 
The only thing I didn't do was Alt+Q - I closed and returned to the Workbook
then saved. Just done it again and it now works - thank you.

Is there a way to have it for the full workbook as I have a number of
worksheets (approx. 12) within the workbook.

Many thanks

Peo Sjoblom said:
It is sheet event code. I just tested with both 2003 and 2007 and they both
came up the same way as

1:35:00 AM or 01:35 depending on the format you choose

Did you put the code in the sheet (right click the sheet tab and select view
code and paste in the macro there,
then change this part


If Application.Intersect(Target, Range("A1:A10")) Is Nothing Then


to the range you want it to cover


Then close the VBA editor (alt + Q)


save the workbook and start typing



--


Regards,


Peo Sjoblom

harwookf said:
I appreciate the fast response and as this is required on a daily basis,
this
is the best way I think. However, I am struggling with it.

I use Excel 2007 so not sure if this makes a difference. I changed the
range initially to match where I will be entering the data, but it is not
working. E.g. if I type 135 in a cell, it shows 3240:00 instead of 1:35.
The
cell is formatted as [h]:mm.

What am I doing wrong?

Peo Sjoblom said:
If you want to do this on a regular basis you would be better off using
an
event macro


http://www.cpearson.com/excel/DateTimeEntry.htm

--


Regards,


Peo Sjoblom

Hi
I would like to enter time into a timesheet using the decimal point
instead
of the colon as it makes it easier and quicker to type, however I still
want
it to display correctly with the colon.
For example - if I enter 12.35 I would like it to display as 12:35 so
it
is
literally just changing the . to :
Many thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
Thank you for the response. I've got it working via some vba code, but
appreciate your prompt reply.
 
Thank you for your prompt reply, however, I decided to use the vba code option.
Thanks again.
 
One last question Peo, I have two times that I need to subtract which may
result in a negative figure. Is there a way to display this correctly.

For example, 7:00 - 37:00 should produce a result of -30:00 but I am just
getting ########.



Peo Sjoblom said:
It is sheet event code. I just tested with both 2003 and 2007 and they both
came up the same way as

1:35:00 AM or 01:35 depending on the format you choose

Did you put the code in the sheet (right click the sheet tab and select view
code and paste in the macro there,
then change this part


If Application.Intersect(Target, Range("A1:A10")) Is Nothing Then


to the range you want it to cover


Then close the VBA editor (alt + Q)


save the workbook and start typing



--


Regards,


Peo Sjoblom

harwookf said:
I appreciate the fast response and as this is required on a daily basis,
this
is the best way I think. However, I am struggling with it.

I use Excel 2007 so not sure if this makes a difference. I changed the
range initially to match where I will be entering the data, but it is not
working. E.g. if I type 135 in a cell, it shows 3240:00 instead of 1:35.
The
cell is formatted as [h]:mm.

What am I doing wrong?

Peo Sjoblom said:
If you want to do this on a regular basis you would be better off using
an
event macro


http://www.cpearson.com/excel/DateTimeEntry.htm

--


Regards,


Peo Sjoblom

Hi
I would like to enter time into a timesheet using the decimal point
instead
of the colon as it makes it easier and quicker to type, however I still
want
it to display correctly with the colon.
For example - if I enter 12.35 I would like it to display as 12:35 so
it
is
literally just changing the . to :
Many thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
That worked well - thanks David.

David Biddulph said:
You could switch to the 1904 date system, but beware of problems if you
transfer data to and fro between 1900 and 1904 systems.
--
David Biddulph

harwookf said:
One last question Peo, I have two times that I need to subtract which may
result in a negative figure. Is there a way to display this correctly.

For example, 7:00 - 37:00 should produce a result of -30:00 but I am just
getting ########.



Peo Sjoblom said:
It is sheet event code. I just tested with both 2003 and 2007 and they
both
came up the same way as

1:35:00 AM or 01:35 depending on the format you choose

Did you put the code in the sheet (right click the sheet tab and select
view
code and paste in the macro there,
then change this part


If Application.Intersect(Target, Range("A1:A10")) Is Nothing Then


to the range you want it to cover


Then close the VBA editor (alt + Q)


save the workbook and start typing



--


Regards,


Peo Sjoblom

I appreciate the fast response and as this is required on a daily basis,
this
is the best way I think. However, I am struggling with it.

I use Excel 2007 so not sure if this makes a difference. I changed the
range initially to match where I will be entering the data, but it is
not
working. E.g. if I type 135 in a cell, it shows 3240:00 instead of
1:35.
The
cell is formatted as [h]:mm.

What am I doing wrong?

:

If you want to do this on a regular basis you would be better off
using
an
event macro


http://www.cpearson.com/excel/DateTimeEntry.htm

--


Regards,


Peo Sjoblom

Hi
I would like to enter time into a timesheet using the decimal point
instead
of the colon as it makes it easier and quicker to type, however I
still
want
it to display correctly with the colon.
For example - if I enter 12.35 I would like it to display as 12:35
so
it
is
literally just changing the . to :
Many thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top