Statutory Hrs Calculation

[Accounting Student in BC, Canada]

I have a payroll timesheet tracking hours worked, with rows corresponding to
the names of employees and columns corresponding to dates of the pay period
(14 days in each pay period). Each pay period is currently stored as a
separate worksheet, e.g. Oct1 for the first pay period of October, then Oct2,
Nov1, etc.

What I'm trying to do is create a function that would check if there is a
"STAT" word above each date. If yes, then count the nonzero cells in 30 days
preceding the STAT date, and if count is >= 15, sum all values in those 30
cells. What is the best way to do it? I thought of combining the worksheets
into one containing 44 days and carrying forward the 44-day cut off as the
first step. Do I need to do that?

Thank you.

 

[joel]

Are the number of employees iin each worksheet always going to be the
same, or to get new employees and old employees leave.

A VBA macro can create the summary but I don'tknow if you want to take
that approach. If you combine the sheets the you need to perform manaul
steps which are prone to error. The macro could be a UDF function like
this

=CountStat(Sheet1!A1)

Where A1 is the Stat date so the macro doesn't have to located the
date. Or just

=CountStat()