Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Enter time with . but display as :
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Enter time with . but display as :
You'll probably want to delete this once you are done..
Go to Tools - Autocorrect Options Replace . with : "harwookf" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Enter time with . but display as :
Another way, with 12.35 in A1, in B1 enter:
=--SUBSTITUTE(A1,".",":") and format B1 as Time. -- Gary''s Student - gsnu200806 "harwookf" wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Enter time with . but display as :
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 "harwookf" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Enter time with . but display as :
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" wrote: 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 "harwookf" wrote in message ... 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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Enter time with . but display as :
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" wrote in message ... 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" wrote: 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 "harwookf" wrote in message ... 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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Enter time with . but display as :
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" wrote: 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" wrote in message ... 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" wrote: 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 "harwookf" wrote in message ... 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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Enter time with . but display as :
Thank you for the response. I've got it working via some vba code, but
appreciate your prompt reply. "Sean Timmons" wrote: You'll probably want to delete this once you are done.. Go to Tools - Autocorrect Options Replace . with : "harwookf" wrote: 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. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Enter time with . but display as :
Thank you for your prompt reply, however, I decided to use the vba code option.
Thanks again. "Gary''s Student" wrote: Another way, with 12.35 in A1, in B1 enter: =--SUBSTITUTE(A1,".",":") and format B1 as Time. -- Gary''s Student - gsnu200806 "harwookf" wrote: 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. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Enter time with . but display as :
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" wrote: 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" wrote in message ... 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" wrote: 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 "harwookf" wrote in message ... 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. |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Enter time with . but display as :
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" wrote in message ... 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" wrote: 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" wrote in message ... 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" wrote: 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 "harwookf" wrote in message ... 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. |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Enter time with . but display as :
That worked well - thanks David.
"David Biddulph" wrote: 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" wrote in message ... 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" wrote: 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" wrote in message ... 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" wrote: 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 "harwookf" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Enter number in a cell and it is to display time. | Excel Worksheet Functions | |||
Can I enter info in Excel and have it display as an '*'? | Excel Discussion (Misc queries) | |||
How do I enter a SSN but only display the last four digits? | Excel Discussion (Misc queries) | |||
how do I enter a value, display that many fields? | Excel Discussion (Misc queries) | |||
Numbers display as decimal, i.e. enter 123 display 1.23 | Setting up and Configuration of Excel |