wednesday through thursday as a week in function

[Ronderbecke]

I think I might be over-complicating this but im having trouble setting up a specific week time frame. Currently, I have a report that has the date it was approved in column L and payout in column K. On another page I have the date of every Friday (when payroll happens and I need to calculate that for each friday, the wednesday from the previous week to the tuesday of the week of that friday) so if the pay date is 1/20/2018 then the pay period approval is from 1/10/2018-1/16/2018. I do a lot with date but I cannot seem to get the weeks needed generated. Here is what I have running right now and it works perfect as the month. How do I convert this to use the weeks instead of the whole month?

=SUMPRODUCT(IF(MOD(ROW('Payroll Report'!$L$3:$L)-ROW('Payroll Report'!$L$3),1)=0,IF(ISNUMBER('Payroll Report'!$L$3:$L),IF('Payroll Report'!$L$3:$L-DAY('Payroll Report'!$L$3:$L)+1=DATE(2017,6,1),'Payroll Report'!$K$3:$K))))

That formula is in B7. The Date is in A7 and is 6/16/2017 (Friday), so payroll approval would need to be 6/7/2017-6/13/2017

Anyone know a good way to insert the week instead of the month into this function?

 

[Special-K99]

Isn't this simply:

Given a payroll date (A7) and assuming ALL payroll dates are Fridays then the period approval is

from A7-10 to A7-3


What happens when there are bank holidays that fall on a Friday? Won't the payroll date move to another day?

 

[Ronderbecke]

Isn't this simply:

Given a payroll date (A7) and assuming ALL payroll dates are Fridays then the period approval is

from A7-10 to A7-3


What happens when there are bank holidays that fall on a Friday? Won't the payroll date move to another day?

Hey There,

So, would I simply change it from: DATE(2017,6,1) to DATE(A7-10,A7-3)? Also, the payroll date stays the same but our company would simply pay a day in advance, nothing changes by way of function though.

 

[Special-K99]

"I have the date of every Friday (when payroll happens and I need to calculate that for each friday, the wednesday from the previous week to the tuesday of the week of that friday) so if the pay date is 1/20/2018 then the pay period approval is from 1/10/2018-1/16/2018 "

This is wrong.1/20/2018 is a Sat not a Friday. So are you working with Saturday dates or Friday dates?

To answer your question DATE takes the format DATE(year, month, day) so the first parameter has to be a year (just a year, not a complete date. Similarly the last paramter is just a day number (1-31 depending on the month).


What I'm saying is if you have a payroll date in A7 and it will ALWAYS be a Friday then to calculate the Wed from the previous week you just subtract 9 (I assumed the date you supplied was a Friday since you said you have Friday dates - no mention of a Saturday date)
To calculate the Tuesday just gone subtract 3

So if A7 is a Friday A7-9 will be the Wed from the previous week and A7-3 will be the Tues before the payroll day (Friday).
You don't need to manipulate that long formula for this.

 

[Ronderbecke]

"I have the date of every Friday (when payroll happens and I need to calculate that for each friday, the wednesday from the previous week to the tuesday of the week of that friday) so if the pay date is 1/20/2018 then the pay period approval is from 1/10/2018-1/16/2018 "

This is wrong.1/20/2018 is a Sat not a Friday. So are you working with Saturday dates or Friday dates?

To answer your question DATE takes the format DATE(year, month, day) so the first parameter has to be a year (just a year, not a complete date. Similarly the last paramter is just a day number (1-31 depending on the month).


What I'm saying is if you have a payroll date in A7 and it will ALWAYS be a Friday then to calculate the Wed from the previous week you just subtract 9 (I assumed the date you supplied was a Friday since you said you have Friday dates - no mention of a Saturday date)
To calculate the Tuesday just gone subtract 3

So if A7 is a Friday A7-9 will be the Wed from the previous week and A7-3 will be the Tues before the payroll day (Friday).
You don't need to manipulate that long formula for this.

Sorry I just realized when I glanced at the calendar I did in fact look at a Saturday and Friday would be 1/19/2018. The formula works fine as long as I am okay calculating based on the whole month; however, I can not seem to get it to allow me to calculate a specific week rather than forcing the entire month. Date(2017,6,1) Calculates all of the payrolls that happen in June of 2017 and I simply want the payroll date to be 6/7 - 6/13. How do you make A7-9 through A7-3 as a range so that it will calculate that range?

 

[Special-K99]

This is still very confusing.

Can you post the file and explain clearly what your input is and what output you are expecting?

You cant attach files on this forum. There are tools on this forum for adding small spreadsheet images

https://www.mrexcel.com/forum/about-board/508133-attachments.html

Or upload the file to an online storage site and post a link to it, though some people may not download the file for fear of viruses.

 

[Ronderbecke]

This is still very confusing.

Can you post the file and explain clearly what your input is and what output you are expecting?

You cant attach files on this forum. There are tools on this forum for adding small spreadsheet images

https://www.mrexcel.com/forum/about-board/508133-attachments.html

Or upload the file to an online storage site and post a link to it, though some people may not download the file for fear of viruses.

Thank you Special-K99; however it was difficult to try and figure out uploading and also making everything work as the file is done in google sheets. I figured posting clips of it would work just the same:

Payroll Report:
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<colgroup><col style="width: 73px"><col width="75"><col width="74"><col width="100"><col width="100"><col width="100"><col width="100"><col width="59"><col width="74"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"></colgroup><tbody>[TR]
[TD="colspan: 3, align: center"]Customer
[/TD]
[TD="align: center"]System[/TD]
[TD="align: center"]Rate[/TD]
[TD="align: center"]Panel[/TD]
[TD="align: center"]Commission[/TD]
[TD="align: center"]Split[/TD]
[TD="align: center"]Sales Rep[/TD]
[TD="align: center"]Total[/TD]
[TD="align: center"]Front End[/TD]
[TD="align: center"]Site Approved[/TD]
[TD="align: center"]Back End[/TD]
[TD="align: center"]Completed[/TD]
[/TR]
[TR]
[TD="align: center"]Size[/TD]
[TD="align: center"]Sold[/TD]
[TD="align: center"]Type[/TD]
[TD="align: center"]Rate[/TD]
[TD="align: center"]Pay %[/TD]
[TD="align: center"]Split with[/TD]
[TD="align: center"]Payout[/TD]
[TD="align: center"]Commission[/TD]
[TD="align: center"]Sale Date[/TD]
[TD="align: center"]Commission[/TD]
[TD="align: center"]Install Date[/TD]
[/TR]
[TR]
[TD="colspan: 3, align: center"]Douglas Rogers[/TD]
[TD="align: center"]5.61[/TD]
[TD="align: center"]$4.17[/TD]
[TD="align: center"]LG PANELS[/TD]
[TD="align: center"]310[/TD]
[TD="align: right"]100.00%[/TD]
[TD][/TD]
[TD="align: center"]$1,739.10[/TD]
[TD="align: center"]$521.73[/TD]
[TD="align: center"]7/12/2017[/TD]
[TD="align: center"]$1,217.37[/TD]
[TD="align: center"]11/29/2017[/TD]
[/TR]
[TR]
[TD="colspan: 3, align: center"]Brigid Creeden[/TD]
[TD="align: center"]5.61[/TD]
[TD="align: center"]$4.00[/TD]
[TD="align: center"]LG PANELS[/TD]
[TD="align: center"]310[/TD]
[TD="align: right"]100.00%[/TD]
[TD][/TD]
[TD="align: center"]$1,739.10[/TD]
[TD="align: center"]$521.73[/TD]
[TD="align: center"]9/1/2017[/TD]
[TD="align: center"]$1,217.37[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="colspan: 3, align: center"]Andrew Bean[/TD]
[TD="align: center"]13.63[/TD]
[TD="align: center"]$3.91[/TD]
[TD="align: center"]REC PANELS[/TD]
[TD="align: center"]300[/TD]
[TD="align: right"]100.00%[/TD]
[TD][/TD]
[TD="align: center"]$4,089.00[/TD]
[TD="align: center"]$1,226.70[/TD]
[TD="align: center"]7/12/2017[/TD]
[TD="align: center"]$2,862.30[/TD]
[TD="align: center"]11/16/2017[/TD]
[/TR]
[TR]
[TD="colspan: 3, align: center"]Jessica Thein[/TD]
[TD="align: center"]6.44[/TD]
[TD="align: center"]$4.24[/TD]
[TD="align: center"]REC PANELS[/TD]
[TD="align: center"]350[/TD]
[TD="align: right"]100.00%[/TD]
[TD][/TD]
[TD="align: center"]$2,254.00[/TD]
[TD="align: center"]$676.20[/TD]
[TD="align: center"]7/12/2017[/TD]
[TD="align: center"]$1,577.80[/TD]
[TD="align: center"]11/16/2017[/TD]
[/TR]
[TR]
[TD="colspan: 3, align: center"]Kristin Tronco[/TD]
[TD="align: center"]8.12[/TD]
[TD="align: center"]$4.00[/TD]
[TD="align: center"]REC PANELS[/TD]
[TD="align: center"]350[/TD]
[TD="align: right"]100.00%[/TD]
[TD][/TD]
[TD="align: center"]$2,842.00[/TD]
[TD="align: center"]$852.60[/TD]
[TD="align: center"]7/12/2017[/TD]
[TD="align: center"]$1,989.40[/TD]
[TD="align: center"]10/12/2017[/TD]
[/TR]
[TR]
[TD="colspan: 3, align: center"]Anthony Alzate[/TD]
[TD="align: center"]6.67[/TD]
[TD="align: center"]$4.00[/TD]
[TD="align: center"]REC PANELS[/TD]
[TD="align: center"]350[/TD]
[TD="align: right"]100.00%[/TD]
[TD][/TD]
[TD="align: center"]$2,334.50[/TD]
[TD="align: center"]$700.35[/TD]
[TD="align: center"]10/11/2017[/TD]
[TD="align: center"]$1,634.15[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="colspan: 3, align: center"]Thomas Lee[/TD]
[TD="align: center"]6.09[/TD]
[TD="align: center"]$3.75[/TD]
[TD="align: center"]REC PANELS[/TD]
[TD="align: center"]300[/TD]
[TD="align: right"]100.00%[/TD]
[TD][/TD]
[TD="align: center"]$1,827.00[/TD]
[TD="align: center"]$548.10[/TD]
[TD="align: center"]5/26/2017[/TD]
[TD="align: center"]$1,278.90[/TD]
[TD="align: center"]08/21/17[/TD]
[/TR]
[TR]
[TD="colspan: 3, align: center"]Jesse Duff[/TD]
[TD="align: center"]7.54[/TD]
[TD="align: center"]$3.66[/TD]
[TD="align: center"]REC PANELS[/TD]
[TD="align: center"]300[/TD]
[TD="align: right"]100.00%[/TD]
[TD][/TD]
[TD="align: center"]$2,262.00[/TD]
[TD="align: center"]$678.60[/TD]
[TD="align: center"]6/16/2017[/TD]
[TD="align: center"]$1,583.40[/TD]
[TD="align: center"]08/22/2017[/TD]
[/TR]
[TR]
[TD="colspan: 3, align: center"]Debbie Herndon[/TD]
[TD="align: center"]7.59[/TD]
[TD="align: center"]$4.00[/TD]
[TD="align: center"]LG PANELS[/TD]
[TD="align: center"]310[/TD]
[TD="align: right"]100.00%[/TD]
[TD][/TD]
[TD="align: center"]$2,352.90[/TD]
[TD="align: center"]$705.87[/TD]
[TD="align: center"]10/9/2017[/TD]
[TD="align: center"]$1,647.03[/TD]
[TD="align: center"]12/27/2017[/TD]
[/TR]
</tbody>[/TABLE]

That page is the page that has the payroll report. Customer is columns A,B,&C so everything else is 1 column after that (column K is front end commissions)

Pay Periods:
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<colgroup><col style="width: 75px"><col width="70"><col width="86"><col width="109"><col width="100"><col width="100"><col width="100"></colgroup><tbody>[TR]
[TD]Week Start:[/TD]
[TD]Week End:[/TD]
[TD]Payroll Date:[/TD]
[TD]Pay Amount:[/TD]
[TD="colspan: 3"]Notes:[/TD]
[/TR]
[TR]
[TD="align: right"]5/3/2017[/TD]
[TD="align: right"]5/9/2017[/TD]
[TD="align: right"]5/12/2017[/TD]
[TD="align: right"]0[/TD]
[TD="colspan: 3"][/TD]
[/TR]
[TR]
[TD="align: right"]5/10/2017[/TD]
[TD="align: right"]5/16/2017[/TD]
[TD="align: right"]5/19/2017[/TD]
[TD][/TD]
[TD="colspan: 3"][/TD]
[/TR]
[TR]
[TD="align: right"]5/17/2017[/TD]
[TD="align: right"]5/23/2017[/TD]
[TD="align: right"]5/26/2017[/TD]
[TD][/TD]
[TD="colspan: 3"][/TD]
[/TR]
[TR]
[TD="align: right"]5/24/2017[/TD]
[TD="align: right"]5/30/2017[/TD]
[TD="align: right"]6/2/2017[/TD]
[TD][/TD]
[TD="colspan: 3"][/TD]
[/TR]
[TR]
[TD="align: right"]5/31/2017[/TD]
[TD="align: right"]6/6/2017[/TD]
[TD="align: right"]6/9/2017[/TD]
[TD][/TD]
[TD="colspan: 3"][/TD]
[/TR]
[TR]
[TD="align: right"]6/7/2017[/TD]
[TD="align: right"]6/13/2017[/TD]
[TD="align: right"]6/16/2017[/TD]
[TD="align: right"]0[/TD]
[TD="colspan: 3"][/TD]
[/TR]
[TR]
[TD="align: right"]6/14/2017[/TD]
[TD="align: right"]6/20/2017[/TD]
[TD="align: right"]6/23/2017[/TD]
[TD][/TD]
[TD="colspan: 3"][/TD]
[/TR]
[TR]
[TD="align: right"]6/21/2017[/TD]
[TD="align: right"]6/27/2017[/TD]
[TD="align: right"]6/30/2017[/TD]
[TD][/TD]
[TD="colspan: 3"][/TD]
[/TR]
[TR]
[TD="align: right"]6/28/2017[/TD]
[TD="align: right"]7/4/2017[/TD]
[TD="align: right"]7/7/2017[/TD]
[TD][/TD]
[TD="colspan: 3"][/TD]
[/TR]
[TR]
[TD="align: right"]7/5/2017[/TD]
[TD="align: right"]7/11/2017[/TD]
[TD="align: right"]7/14/2017[/TD]
[TD][/TD]
[TD="colspan: 3"][/TD]
[/TR]
[TR]
[TD="align: right"]7/12/2017[/TD]
[TD="align: right"]7/18/2017[/TD]
[TD="align: right"]7/21/2017[/TD]
[TD][/TD]
[TD="colspan: 3"][/TD]
[/TR]
[TR]
[TD="align: right"]7/19/2017[/TD]
[TD="align: right"]7/25/2017[/TD]
[TD="align: right"]7/28/2017[/TD]
[TD][/TD]
[TD="colspan: 3"][/TD]
[/TR]
</tbody>[/TABLE]

I added in the week start date and week end date so that it would make things easier. Essentially anything on the payroll report in column L that is in between the start & end date needs to pay that pay period. Does that help?

 

[Special-K99]

That helps but am still confused.
(If I've understood this correctly) none of the Completed dates in the Payroll Report are in the Pay periods ranges ?

What are we trying to do here?
Are we trying to total the Back End amounts in the Payroll Report and "slot" them into the correct Payroll Period based on the Completed date in the Pay Amount column?

 

[Ronderbecke]

That helps but am still confused.
(If I've understood this correctly) none of the Completed dates in the Payroll Report are in the Pay periods ranges ?

What are we trying to do here?
Are we trying to total the Back End amounts in the Payroll Report and "slot" them into the correct Payroll Period based on the Completed date in the Pay Amount column?

The goal is to calculate the front end pay based on site approved date. The start and end date for site approved are on the sheet that gets paid on that friday (also on the sheet as the 3rd column). Essentially, I want it to add the front end pay to the appropriate friday payroll based on site approved date. I might be overcomplicating this formula, but there are a lot of variables it seems.

In a nut shell:
if site approved date on payroll report falls in between week start and week end, then add front end to pay amount.

Does that make it more simple?

 

[Ronderbecke]

Thanks for everyones help, I was able to figure this one out finally:

=SUMPRODUCT(IF(MOD(ROW('Payroll Report'!$L$3:$L)-ROW('Payroll Report'!$L$3),1)=0,IF('Payroll Report'!$L$3:$L<=B5,IF('Payroll Report'!$L$3:$L>=A5,'Payroll Report'!$K$3:$K))))