Time calculations

  • Thread starter Thread starter Greg Maxey
  • Start date Start date
G

Greg Maxey

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

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

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

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

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

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

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

~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 
Back
Top