• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula for paychecks

S

shadedlight

Member
This is what I would like to accomplish.

On sheet 1 I have Bi-weekly and monthly pay information

On sheet 2 I have a list of paycheck dates

On sheet 1 I have two charts.
- Chart 1: Bi-weekly pay
- Chart 2: Monthly pay

I need a formula that will fill Column B2 and B3 on Sheet 1 with relevant and sequential paychecks from sheet 2. Ex. On sheet 2 there are 3 paycheck dates from January. When that date hits I want that pay information to appear on Sheet 1 (B2) for paycheck 1 and sheet 1 (B3) for paycheck 2

I would also like the relevant date to appear on A2 and A3

Lastly, a formula that does the same thing but calculates the total paychecks for the relevant month in B7 and B8.

Thank you
 

Attachments

Hi shadedlight,

Can you please populate your desire output, this will help

shadedlight said:
This is what I would like to accomplish.

On sheet 1 I have Bi-weekly and monthly pay information

On sheet 2 I have a list of paycheck dates

On sheet 1 I have two charts.
- Chart 1: Bi-weekly pay
- Chart 2: Monthly pay

I need a formula that will fill Column B2 and B3 on Sheet 1 with relevant and sequential paychecks from sheet 2. Ex. On sheet 2 there are 3 paycheck dates from January. When that date hits I want that pay information to appear on Sheet 1 (B2) for paycheck 1 and sheet 1 (B3) for paycheck 2

I would also like the relevant date to appear on A2 and A3

Lastly, a formula that does the same thing but calculates the total paychecks for the relevant month in B7 and B8.

Thank you
Click to expand...
 
Almost,

I've included an updated spreadsheet with notes.

I want the formula for Bi-weekly pay to adjust for each individual pay date. So, on Sept. 9 I only want the Sept. 9 pay info. (B20 & C20). Then when Sept. 23 occurs I only want the pay info from Sept. 23 (B21 & C21), and so on for the rest of the pay dates.

Thanks again
 

Attachments

The start date would be whatever current paycheck date. Ex. If it was for Sept. 9 it would be Sept. 9 (B20 & C20). If it was for Sept. 23 the start date would be Sept. 23 (B21 & C21)
 
I'm not sure I follow? Would a solution be to create a drop down data validation of the relevant dates and then index match the values? If so, I could use some help on that
 
Hi David,

Thanks for taking a swing. I'm confused how it works? Can we make sheet 1 have a drop down list of the pay dates and based on the pay date selected have it index match sheet 2?
 
Not really understand you,
As you said, we find the first few days of the month, in sheet 2 colunn A ,
Receive the initial amount,in sheet 2 colunn B,
AGGREGATE(15,6,Sheet2!B2:B27/(MONTH(Sheet2!$A$2:$A$27)=C1),1)
To get the final amount after two weeks (Bi-weekly),
We add, 14, the first date, then the formula gives the required date, then he looks in column C,
=INDEX(Sheet2!C2:C27,MATCH(AGGREGATE(15,6,Sheet2!A2:A27/(MONTH(Sheet2!$A$2:$A$27)=C1),1)+14,Sheet2!A2:A27,0),0).

David
 
You must log in or register to reply here.
Back
Top