Gary,
Thanks. This works. I saw some odd behaviour at first but it seems to be
spot on now. This is how my spreadsheet is laid out:
I have four columns labeled:
In Out Lunch Total
Each following cell the four columns is formatted for time. I enter the
start time like 07:05 an end time like 16:15 a standard 0:30 for lunch
and column D has a forumla:
=(B2-A2-C2) which gives a total time in hours and minutes.
I have six rows laid out similiar to the above (Mon - Sat). In Cell D8 I
nave teh forumual =(SUM(D2:D7)). This gives me the time in hours and
minutes.
I placed the formula that you provided in Cell D9. I formatted it as
"General" and then "Custom" "0.00"
The first time I tried this the odd behaviour was showing up in D8. I
entered 08:00 in the first five "In" fields and "16:30" in the first 5 "Out"
fields. I expected to see "40:00" in D8 and "40.00" in D9. What I saw was
"16:00" in D8 and "40.00" in D9. I tried a few changes and each time the D9
value was correct but the D8 value was low. Finally I set all the value to
0 and started over. It worked perfectly.
Any idea what caused the behaviour described above? I can't imagine how the
application gets "40.00" out of "16:00 * 24" then again I don't understand
how it gets "40.00" out of "40:00 * 24" either.
A few more questions if I may.
1. I would like to "reset" the time in and time out values to 08:00 - 16:00
after I compute the totals for each employee. I know nothing about Excel
VBA but I know enough about Word VBA to be certain that it could be done. I
suppose the code would look something like:
For Each oCell in oRng.Cells
oCell.Value = "0:00"
Next oCell
2. Is there a way to autoformat the text entry? It would be much more
efficient if I could enter 0700 (without the colon) and the cell content
autoformat to 07:00.
Thanks again.
--
~~~~~~~~~~~~~~~~~~~~~~~~~~~
Greg Maxey - Word MVP
My web site
http://gregmaxey.mvps.org
Word MVP web site
http://word.mvps.org
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Gary''s Student wrote:
=A1*24 and format as General.
If your app absolutely requires two and only two decimal digits, then:
Format Cells... Number Number and specify 2 digits
I have a simple spreadsheet for determining the hours and minutes
that an employee works during a pay period. The current result is
formatted like "38:45" meaning 38 hours and 45 minutes. My payroll
software needs the time worked in decimal format (i.e., 38.75).
Can someone please show me how I would use an Excel formula to
covert hours and minutes to hours and fraction of hours. Thank you.
--
~~~~~~~~~~~~~~~~~~~~~~~~~~~
Greg Maxey - Word MVP
My web site http://gregmaxey.mvps.org
Word MVP web site http://word.mvps.org
~~~~~~~~~~~~~~~~~~~~~~~~~~~~