Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is there a way to enter times without typing a colon?
It there a way to enter times without 'typing' a colon in an empolyee
time sheet, and still be able to add the number of hours worked? ....For example, type 0930 and 2130 instead of having to type either 9:30 or 09:30 and 21:30, and still be able to do the math on figuring the number of hours worked in a day? Thanks. -Dennis -- Dennis Kessler http://www.denniskessler.com/acupuncture |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is there a way to enter times without typing a colon?
=TIME(INT(A1),100*MOD(A1,1),0)
-- David Biddulph "dk_" wrote in message ... It there a way to enter times without 'typing' a colon in an empolyee time sheet, and still be able to add the number of hours worked? ...For example, type 0930 and 2130 instead of having to type either 9:30 or 09:30 and 21:30, and still be able to do the math on figuring the number of hours worked in a day? Thanks. -Dennis |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is there a way to enter times without typing a colon?
Hi Dennis,
One way A1 0930 (Start time) A2 1430 (Start lunch) A3 1500 (End Lunch) A4 2130 (Finish time) A5 =((CEILING(A4,25)-CEILING(A1,25))-((CEILING(A3,25)-CEILING(A2,25))))/100 This will round to the nearest quarter hour. You will need a different approach if your shift crosses midnight. Format A1 to custom 0000 HTH Martin |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is there a way to enter times without typing a colon?
Hi Dennis,
http://xldynamic.com/source/xld.QDEDownload.html -- Kind regards, Niek Otten Microsoft MVP - Excel "dk_" wrote in message ... | It there a way to enter times without 'typing' a colon in an empolyee | time sheet, and still be able to add the number of hours worked? | | ...For example, type 0930 and 2130 instead of having to type either 9:30 | or 09:30 and 21:30, and still be able to do the math on figuring the | number of hours worked in a day? | | Thanks. | | -Dennis | | -- | Dennis Kessler | http://www.denniskessler.com/acupuncture |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is there a way to enter times without typing a colon?
The code on Chip Pearson's page, (link below), was exactly what I was
looking for. Sorry if I didn't ask my question properly. ....From Ron Coderre's post to another similar question: See Chip Pearson's website for options: http://www.cpearson.com/excel/DateTimeEntry.htm Does that help? *********** Regards, Ron -Dennis -- Dennis Kessler http://www.denniskessler.com/acupuncture In article , "Niek Otten" wrote: Hi Dennis, http://xldynamic.com/source/xld.QDEDownload.html -- Kind regards, Niek Otten Microsoft MVP - Excel "dk_" wrote in message ... | It there a way to enter times without 'typing' a colon in an empolyee | time sheet, and still be able to add the number of hours worked? | | ...For example, type 0930 and 2130 instead of having to type either 9:30 | or 09:30 and 21:30, and still be able to do the math on figuring the | number of hours worked in a day? | | Thanks. | | -Dennis | | -- | Dennis Kessler | http://www.denniskessler.com/acupuncture |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is there a way to enter times without typing a colon?
Yep, that works.
Thank you. I'll study the formula and try to figure it out. -Dennis -- Dennis Kessler http://www.denniskessler.com/acupuncture In article , "David Biddulph" wrote: Sorry, my error. That formula was one I'd given as answer to someone who wanted to enter number in the format 09.30. For your format, it needs to be =TIME(INT(A1/100),100*MOD(A1/100,1),0) -- David Biddulph "dk_" wrote in message ... David, I copied your formula in B1. I entered 0930 in A1 The result was 6:00 PM in B1 I don't see what this formula should do. I was trying to find a way to enter 0930 or 930, with no colon to be computed in a formula as 9:30 AM. Thanks. -Dennis In article , "David Biddulph" wrote: =TIME(INT(A1),100*MOD(A1,1),0) -- Dennis Kessler http://www.denniskessler.com/acupuncture |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I enter a date typing 040906 and have it appear 04/09/06? I | New Users to Excel | |||
Why do I lose hundredths when summing elapsed times in a pivot tab | Excel Discussion (Misc queries) | |||
Avg Times in MOD fx using selected rows | Excel Worksheet Functions | |||
typing addresses & when I hit enter need to be on next line cel B | Excel Discussion (Misc queries) | |||
How do I enter minutes and seconds (lap times)? | Excel Discussion (Misc queries) |