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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~

=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
--
Gary''s Student - gsnu2007g


"Greg Maxey" wrote:

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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~

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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~

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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~

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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~

There are several things to check:

1. compare the formats of the cells that are correct to the cells (like D8)
that are not correct. It might just a formatting issue.

2. Insure that D2 thru D7 are genuine numbers. SUM() ignores non-numbers
and won't even tell you.

If worst come to worst, select D2 thru D8 and change the format to General.
It will then be easier to check the math!!

Update this post if problem persist.
--
Gary''s Student - gsnu200774


"Greg Maxey" wrote:

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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Gary,

Thanks.

I think I have it sorted out and understand what was going on. When I
chagned D8 to General it's value changed to 1.666667. I realized that 40
hours is 1.666667 days and apparently the format I was using for that cell
made 1.666667 days looke like 16 hours.

--
~~~~~~~~~~~~~~~~~~~~~~~~~~~
Greg Maxey - Word MVP

My web site http://gregmaxey.mvps.org
Word MVP web site http://word.mvps.org
~~~~~~~~~~~~~~~~~~~~~~~~~~~~



Gary''s Student wrote:
There are several things to check:

1. compare the formats of the cells that are correct to the cells
(like D8) that are not correct. It might just a formatting issue.

2. Insure that D2 thru D7 are genuine numbers. SUM() ignores
non-numbers and won't even tell you.

If worst come to worst, select D2 thru D8 and change the format to
General. It will then be easier to check the math!!

Update this post if problem persist.

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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~