Calculating Time/Days worked

  • Thread starter Thread starter lilbpaw
  • Start date Start date
L

lilbpaw

Hope you will bare with me while I explain.
Once again I am working with a sheet, or series of sheets that hel
employees calculate their time for days and hours worked.
I decided against the protection button, as it was more of a proble
with employees unchecking it. So, I am left with one last functio
before it is complete.
Let me remind you wonderful people I am new to excel and this is m
first real project.

Ok, my last 'function' I would like is this...
Paydays are calculated from the 11th of each month to the 25th, the
again from the 26th of each month to 10th of the the next month. (
hope you can follow that.) Now, most employees do not work on Saturda
or Sunday, but a few do. Thus I opt'd to just have those that don'
delete the times in Sat and Sun to calculate it as a 0 hours.

Now, Since the days range as described above, some times we have 1
days in a pay period, some 11 and some 12.

I have a formula box that says work time is 80. Therefor the overtim
calculation, marks everything over 80 hours as over time.

However, in a work period that has 12 days or 11 day, this really isn'
overtime.

So i need a formula that would add up the days, and account for eac
days as 8 hours worked. then display, anything over that as overtim
worked.

Is that an impossible formula or what?

It sounds way out of my reach, but I am willing to learn and reseach
and beg for help

Any suggestions?
Lilbpa
 
Assuming year in A1, month number in B1, hours from 11th to 25th, and hours
from 26th to 10th next month then

first period hours overtime

=MAX(C1-NETWORK(DATE(A1,B1,11),DATE(A1,B1,25))*8,0)

second period

=MAX(D1-NETWORKDAYS(DATE(A1,B1,26),DATE(A1,B1+1,10))*8,0)

The analysis toolpak addin needs to be installed


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top