Gary,
Thanks for the code. I modified is slightly so that it doesn't effect the
row for Saturday which is normally not a workday and set at 0:00. It works
perfectly.
However, I am still seeing the odd value in Cell D8. When I reset the
values D8 reads "16:00" while D9 reads "40:00"
Again. D8 uses a formula =Sum(D2:D7) and the formula in D9 is =(D8*24).
The value in D2, D3, D4, D5, and D6 is "8:00" the value in D7 is "0"00"
How can D8 read "16:00" if is the sum of 8:00+8:00+8:00+8:00+8:00 ??
How can D9 read "40:00" if it is the sum of 16:00*24 ??
--
~~~~~~~~~~~~~~~~~~~~~~~~~~~
Greg Maxey - Word MVP
My web site
http://gregmaxey.mvps.org
Word MVP web site
http://word.mvps.org
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
"Gary''s Student" wrote in message
...
With regard to resetting values to 8:00 and 16:00, try this code :
Sub time_reset()
n = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To n
Cells(i, 1) = #8:00:00 AM#
Cells(i, 2) = #4:00:00 PM#
Next
End Sub
It simply fills the used portions of columns A & B with the desired reset
values.
Starting with an empty column that has been formatted to Text, enter
values
like:
0534
0811
1130
without any colons and then select the cells and then run:
Sub time_converter()
For Each r In Selection
v = r.Value
hrs = Left(v, 2)
mins = Right(v, 2)
r.Value = TimeSerial(hrs, mins, 0)
r.NumberFormat = "h:mm;@"
Next
End Sub
--
Gary''s Student - gsnu200774
"Greg Maxey" wrote:
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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~