Formula for paychecks

[shadedlight]

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

 

[xlstime]

Hi shadedlight,

Can you please populate your desire output, this will help

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

 

[shadedlight]

How about this

 

[xlstime]

Hi shadedlight,

What is Bi-weekly, is this first day of every month?

Please see attached file if this is ok, have create dropdown in B1 you can change months,

if you need current month only then just remove dropdown and paste formula
=text(today(),'MMMM')

How about this

 

[xlstime]

Little changes -

Hi shadedlight,

What is Bi-weekly, is this first day of every month?

Please see attached file if this is ok, have create dropdown in B1 you can change months,

if you need current month only then just remove dropdown and paste formula
=text(today(),'MMMM')

 

[shadedlight]

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

 

[bines53]

Hi shadedlight,

I did not understand, what is the start date ?


David

 

[shadedlight]

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)

 

[bines53]

Is there a problem rather than specifying September, recorded 9 ?

 

[shadedlight]

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

 

[bines53]

Hi shadedlight,

Check this file,

David

 

[shadedlight]

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?

 

[bines53]

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

 

[shadedlight]

So when I populate Sheet 2 (B2) with 1000 and Sheet 2 (C2) with 2000 I don't see any values populate on sheet 1 (B3) or (B4).

 

[bines53]

Hi shadedlight,

I suggest you fill in all the cells,Paycheck 1 and Paycheck 2 ,And upload the file.

David

 

[shadedlight]

Take a look at this

 

[bines53]

Hi shadedlight,

You have to understand, if you want to find the amount after a month, you must specify the date to be true, 30 days or 31 days, here in the example I took 28 days.

David