Search:

# biweekly to monthly date conversions

Asked By: Cathy    Date: Dec 31    Category: MS Office    Views: 1085

This is for a productivity summary that looks like a payroll issue,
but it is purely productivity. I can't find something that helps me
so far.

SCENARIO: I have been given one workbook with HOURS worked by pay
period (biweekly) and another workbook has PRODUCT totals per month.
Of course, there are different kinds of hours worked and not worked;
overtime, sick, etc., for the first and there are different kinds of
widgets produced for the second. Various people do the different
inputs at different times, so I'm not changing the workbooks in any
structural way. My job is to tie these together.

The HOURS Workbook has biweekly worksheets for the entire year (e.g,
the tabs are named "05-25 to 06-09", obviously a string, not a
date.) On each of these worksheets, they have the columns as the
workdays labelled with a date format (thankfully, though it is
actually a formula that adds days to the start date in the heading).
Every xth row is a worker and x no of rows below each has each of
the different kinds of work hours (to be added or subtracted).

The PRODUCT Workbook has montly worksheets for the entire year. In
each worksheet, the rows are all the workers (alphabetically) and
the columns are in three sets. The first set has columns for the
various types of work hours that get subtotalled across the page to
come up with net work hours. Then, a group of columns represents
each widget produced that also get subtotalled. The last set is the
productivity calculation of total widgets per net work hour.

QUESTIONS: For pay periods that overlap two months, what is the best
way to tease out the hours for all the days in a month in the
biweekly HOURS worksheets to get summed respective totals for the
PRODUCT worksheet?

I'm guessing that the answer is using arrays!

I envision that splits each HOURS worksheet into MONTH A sum and
MONTH B sum for the hours somewhere else on the hours worksheet.
Then, using a macro button on each PRODUCT worksheet that sucks them
that someone can click on sometime after the end of the month.

Share:

It seems from your description that you have a substantial database with
multiple users who want different reports from the database. I would not be
using Excel for this. Access will handle it more effectively, and allow you
to have concurrent users.

Even if I was just doing the job  you are talking about I would put the data
into Access.

Didn't find what you were looking for? Find more on biweekly to monthly date conversions Or get search suggestion and latest updates.