Time calculator

[Brian Smith]

I need, using Excel 2003, a spreadsheet capable of calulating the total
number of hours a person works each week, but I need that broken down by
overtime and double time hours. What we do is to enter the total number of
hours each person works in to a cell. we do this for each day and have to
manually break everything down by the amount of OT and DT hours. If there is
a formula that can do this for me, I would greatly appreciate it.

Thank you!!!

Brian

 

[Gordon]

I need, using Excel 2003, a spreadsheet capable of calulating the total
number of hours a person works each week, but I need that broken down by
overtime and double time hours. What we do is to enter the total number of
hours each person works in to a cell. we do this for each day and have to
manually break everything down by the amount of OT and DT hours. If there
is
a formula that can do this for me, I would greatly appreciate it.

Thank you!!!

Brian

Presumably you are entering the data from a time sheet - how is a FORMULA
going to know the split between time and overtime for a particular
individual?

 

[Brian Smith]

I need something that will automatically fill in the correct overtime or
double time box once a person reaches 40 hours for the week.

 

[Gordon]

I need something that will automatically fill in the correct overtime or
double time box once a person reaches 40 hours for the week.

And in what format do you get this data?

 

[Brian Smith]

Here is what the spreadsheet looks like. What I want is for it to
automatically put the amounts in the right box. It needs to add up the time
from each day, and put that number, up to 40 hours, in the regular column,
anything over 40 in the OT column and if a person works into overtime on
Saturday then works on Sunday, to put the Sunday time under the DT column.
All help is appreciated!!!


Employee Name Mon Tue Wed Thur Fri Sat Sun Regular OT DT

Smith, Brian 8 8 8 12 15 8.25 8 40
19.25 8

 

[Steve]

Brian -

Use the "IF" formula. Go to Excel help and type in IF. That will give you
the format of the formula...just fill in your cell addresses.

Set up the the Regular Hours formula to say IF the sum of the cells is
greater than 40, enter 40; otherwise, enter the sum of the cells.

Then set up the OT column to say IF the sum is greater than 40, enter the
total of the cells minus 40; otherwise enter 0.

Hope that helps.

 

[Steve]

I just looked at your spreadsheet again. You need to apply the IF formula to
the Mon - Sat hours total for Regular and OT columns. Then, the DT coulumn is
simply equal to the Sunday hours.

Of course, if you want a Total Hours Column, just add all the days.