Do math on hours in one column dependent upon dates in another column

[JBC]

Hello,

I'm currently working on a pay calculating worksheet for my wife's new job that pays semi monthly. The 1st thru the 15th is paid on the 25th and the 16th thru the 30th/31st is paid on the 10th of the following month. Of course overtime (OT) is paid on everything over 40 hours per week. Her work week will be Tuesday - Saturday, and the week for figuring OT starts on Sunday and goes through Saturday. So I'll have two cycles constantly moving relative to one another, such that one pay period will end containing part of the next weeks hours that are needed to calculate OT. In addition hours fluctuate during any given week. The employer said that OT is probably going to be a given. I'm creating this to keep them honest with respect to the OT.

I'm not spanning worksheets, each of which is for a given month, such that I have six weeks per sheet so as to have a full week at the beginning and end of each month, even though there may be only a single day belonging to that month. I'm doing this for the sake of calculating the weekly REG and OT hours.

I have hours in column H and want to add only those hours that have dates in column B from the 1st through the 15th, and likewise from the 16th through the 30th/31st, but have no idea how to implement.

I already have a separate area that watches for OT for any given week, and I'll be using what ever formula/VB script you can help me come up with, for both calculating the REG and OT hours in separate areas due to the different rates.

I hope this is clear. If not and you feel like helping, please ask any questions you require.

I'm using Excel 2003 and currently only have the analysis toolpak installed.


Thanks in advance!

Joe

 

[kevinb11]

Hello,

I'm currently working on a pay calculating worksheet for my wife's new job that pays semi monthly. The 1st thru the 15th is paid on the 25th and the 16th thru the 30th/31st is paid on the 10th of the following month. Of course overtime (OT) is paid on everything over 40 hours per week. Her work week will be Tuesday - Saturday, and the week for figuring OT starts on Sunday and goes through Saturday. So I'll have two cycles constantly moving relative to one another, such that one pay period will end containing part of the next weeks hours that are needed to calculate OT. In addition hours fluctuate during any given week. The employer said that OT is probably going to be a given. I'm creating this to keep them honest with respect to the OT.

I'm not spanning worksheets, each of which is for a given month, such that I have six weeks per sheet so as to have a full week at the beginning and end of each month, even though there may be only a single day belonging to that month. I'm doing this for the sake of calculating the weekly REG and OT hours.

I have hours in column H and want to add only those hours that have dates in column B from the 1st through the 15th, and likewise from the 16th through the 30th/31st, but have no idea how to implement.

I already have a separate area that watches for OT for any given week, and I'll be using what ever formula/VB script you can help me come up with, for both calculating the REG and OT hours in separate areas due to the different rates.

I hope this is clear. If not and you feel like helping, please ask any questions you require.

I'm using Excel 2003 and currently only have the analysis toolpak installed.


Thanks in advance!

Joe

Can you share a copy of your work/spreadsheet and an example of what you expect?

 

[JBC]

Thanks for responding!

Sure, but it will take a little while to trim it down. I'll have it ready tomorrow morning. What's the best way for me to get it to you?

By the way, I'm using Windows XP 32 bit SP3 (I forgot to add this as per the site recommendation.)

Thanks again!

 

[JBC]

What's the best way for me to attach a copy of the worksheet?

 

[VoG]

What's the best way for me to attach a copy of the worksheet?

Look here http://www.mrexcel.com/forum/about-board/508133-attachments.html

 

[JBC]

M6=(H9+SUM(H11:H17)+SUM(H19:H25))*24
I need the cell references for H column in the above formula to be variable dependent upon the particular month which will be relative to days of the week. I also need help to vary M14=(SUM(H27:H33)+SUM(H35:H41)+H43)*24
to compensate for either a 30, 31, 28, or 29 day month.
Please read the text in the graphic or ask for further explanation.

Thank you!

 

[JBC]

Instead of working, again and again, from a table of data within the cells of your spreadsheet, can you instead load rows or column's of data into a dimensioned array in memory, much like you can in a basic language, so that you do not have to have duplicate data displayed in your sheet? If so how is this done, a simplified example is all that's required. I'm not really familiar with visual basic or it's interface with excel.

Thanks in advance!