|
Pay day
Our working period is divided into 2 sections a month: 1st thru 15th and
16th thru last. We are paid on the 6th of the month and the 21st of the month OR the closest workday to the end of the pay period. Can you help me construct a formula to return this value? Thank you for any help you can offer. |
Pay day
Try this:
If year is in A2, month in B2, then for 6th: =IF(WEEKDAY(DATE(A2,B2,6),2)<6,DATE(A2,B2,6),8-WEEKDAY(DATE(A2,B2,6),2)) for 21st: =IF(WEEKDAY(DATE(A2,B2,21),2)<6,DATE(A2,B2,6),8-WEEKDAY(DATE(A2,B2,21),2)) Regards, Stefi €žnbslarson€ť ezt Ă*rta: Our working period is divided into 2 sections a month: 1st thru 15th and 16th thru last. We are paid on the 6th of the month and the 21st of the month OR the closest workday to the end of the pay period. Can you help me construct a formula to return this value? Thank you for any help you can offer. |
Pay day
I tried that, but the formula returned the 01/01/00.
I had the year (2009) in a cell and the month (9) in another cell. This is the page I'm working with: 4th Quarter 2009 09/16/09 09/30/09 10/06/09 01/01/00 (what the formula returned) Date should be 10/06/09. 10/01/09 10/15/09 10/21/09 10/16/09 10/31/09 12/06/09 11/01/09 11/15/09 11/21/09 11/16/09 11/30/09 12/04/09 12/06 = Sunday 12/01/09 12/15/09 12/21/09 "Stefi" wrote: Try this: If year is in A2, month in B2, then for 6th: =IF(WEEKDAY(DATE(A2,B2,6),2)<6,DATE(A2,B2,6),8-WEEKDAY(DATE(A2,B2,6),2)) for 21st: =IF(WEEKDAY(DATE(A2,B2,21),2)<6,DATE(A2,B2,6),8-WEEKDAY(DATE(A2,B2,21),2)) Regards, Stefi €žnbslarson€ť ezt Ă*rta: Our working period is divided into 2 sections a month: 1st thru 15th and 16th thru last. We are paid on the 6th of the month and the 21st of the month OR the closest workday to the end of the pay period. Can you help me construct a formula to return this value? Thank you for any help you can offer. |
Pay day
Sorry, my mistake! This one works. In this layout you need only one formula:
A B C D 1 year month 6 21 2 2009 10 10/06/09 10/21/09 3 11 11/06/09 11/23/09 4 12 12/07/09 12/21/09 the formula in C2: =IF(WEEKDAY(DATE($A$2,$B2,C$1),2)<6,DATE($A$2,$B2, C$1),DATE($A$2,$B2,C$1)+(8-WEEKDAY(DATE($A$2,$B2,C$1),2))) Fill it to the right and down! Stefi €žStefi€ť ezt Ă*rta: Try this: If year is in A2, month in B2, then for 6th: =IF(WEEKDAY(DATE(A2,B2,6),2)<6,DATE(A2,B2,6),8-WEEKDAY(DATE(A2,B2,6),2)) for 21st: =IF(WEEKDAY(DATE(A2,B2,21),2)<6,DATE(A2,B2,6),8-WEEKDAY(DATE(A2,B2,21),2)) Regards, Stefi €žnbslarson€ť ezt Ă*rta: Our working period is divided into 2 sections a month: 1st thru 15th and 16th thru last. We are paid on the 6th of the month and the 21st of the month OR the closest workday to the end of the pay period. Can you help me construct a formula to return this value? Thank you for any help you can offer. |
Pay day
On Thu, 23 Jul 2009 06:52:02 -0700, nbslarson
wrote: Our working period is divided into 2 sections a month: 1st thru 15th and 16th thru last. We are paid on the 6th of the month and the 21st of the month OR the closest workday to the end of the pay period. Can you help me construct a formula to return this value? Thank you for any help you can offer. When you write "closest workday to the end of the pay period", do you mean the absolute closest, or do you mean the closest workday AFTER the end of the pay period? How do you decide if the payday is on the 21st or 6th, or if it is on the closest workday to the end of the pay period? --ron |
Pay day
On Thu, 23 Jul 2009 06:52:02 -0700, nbslarson
wrote: Our working period is divided into 2 sections a month: 1st thru 15th and 16th thru last. We are paid on the 6th of the month and the 21st of the month OR the closest workday to the end of the pay period. Can you help me construct a formula to return this value? Thank you for any help you can offer. Further thoughts. If your decision regarding the pay day is properly expressed as: Pay day is 6 days after the end of the section; but if that day falls on a non-Workday, pay on the previous workday; then the formula is: A1: contains the ending date for the section (e.g. 30 Sep 2009) Payday: =WORKDAY(A1+7,-1) What this will return, in general, is the 6th and 21st of the month. However, if those days fall on a weekend (or holiday if you use the optional holidays argument for the WORKDAY function), then pay day will be on the preceding WORKDAY (e.g. Friday). If the formula returns a #NAME! error, see HELP for the WORKDAY function to correct this. A1 must contain an Excel date. Substitute whatever cell reference you have that contains the date, or a formula to construct the date from other information. --ron |
Pay day
Better, but I'm not gett the correct dates:
4th Quarter 2009 year month 6 21 2009 9 7-Sep-09 09/16/09 09/30/09 10/06/09 10 21-Oct-09 10/01/09 10/15/09 10/21/09 10 6-Oct-09 10/16/09 10/31/09 10/06/09 11 23-Nov-09 11/01/09 11/15/09 11/21/09 Should be 21st 11 6-Nov-09 11/16/09 11/30/09 12/04/09 Should be 12/04 - 06=Sun. 12 21-Dec-09 12/01/09 12/15/09 12/21/09 "Stefi" wrote: Sorry, my mistake! This one works. In this layout you need only one formula: A B C D 1 year month 6 21 2 2009 10 10/06/09 10/21/09 3 11 11/06/09 11/23/09 4 12 12/07/09 12/21/09 the formula in C2: =IF(WEEKDAY(DATE($A$2,$B2,C$1),2)<6,DATE($A$2,$B2, C$1),DATE($A$2,$B2,C$1)+(8-WEEKDAY(DATE($A$2,$B2,C$1),2))) Fill it to the right and down! Stefi €žStefi€ť ezt Ă*rta: Try this: If year is in A2, month in B2, then for 6th: =IF(WEEKDAY(DATE(A2,B2,6),2)<6,DATE(A2,B2,6),8-WEEKDAY(DATE(A2,B2,6),2)) for 21st: =IF(WEEKDAY(DATE(A2,B2,21),2)<6,DATE(A2,B2,6),8-WEEKDAY(DATE(A2,B2,21),2)) Regards, Stefi €žnbslarson€ť ezt Ă*rta: Our working period is divided into 2 sections a month: 1st thru 15th and 16th thru last. We are paid on the 6th of the month and the 21st of the month OR the closest workday to the end of the pay period. Can you help me construct a formula to return this value? Thank you for any help you can offer. |
Pay day
Perfect, Ron. Thank you so much. That worked exactly!
"Ron Rosenfeld" wrote: On Thu, 23 Jul 2009 06:52:02 -0700, nbslarson wrote: Our working period is divided into 2 sections a month: 1st thru 15th and 16th thru last. We are paid on the 6th of the month and the 21st of the month OR the closest workday to the end of the pay period. Can you help me construct a formula to return this value? Thank you for any help you can offer. Further thoughts. If your decision regarding the pay day is properly expressed as: Pay day is 6 days after the end of the section; but if that day falls on a non-Workday, pay on the previous workday; then the formula is: A1: contains the ending date for the section (e.g. 30 Sep 2009) Payday: =WORKDAY(A1+7,-1) What this will return, in general, is the 6th and 21st of the month. However, if those days fall on a weekend (or holiday if you use the optional holidays argument for the WORKDAY function), then pay day will be on the preceding WORKDAY (e.g. Friday). If the formula returns a #NAME! error, see HELP for the WORKDAY function to correct this. A1 must contain an Excel date. Substitute whatever cell reference you have that contains the date, or a formula to construct the date from other information. --ron |
Pay day
Oops. Spoke too soon. How do I rule out holidays (Labor day)?
"Ron Rosenfeld" wrote: On Thu, 23 Jul 2009 06:52:02 -0700, nbslarson wrote: Our working period is divided into 2 sections a month: 1st thru 15th and 16th thru last. We are paid on the 6th of the month and the 21st of the month OR the closest workday to the end of the pay period. Can you help me construct a formula to return this value? Thank you for any help you can offer. Further thoughts. If your decision regarding the pay day is properly expressed as: Pay day is 6 days after the end of the section; but if that day falls on a non-Workday, pay on the previous workday; then the formula is: A1: contains the ending date for the section (e.g. 30 Sep 2009) Payday: =WORKDAY(A1+7,-1) What this will return, in general, is the 6th and 21st of the month. However, if those days fall on a weekend (or holiday if you use the optional holidays argument for the WORKDAY function), then pay day will be on the preceding WORKDAY (e.g. Friday). If the formula returns a #NAME! error, see HELP for the WORKDAY function to correct this. A1 must contain an Excel date. Substitute whatever cell reference you have that contains the date, or a formula to construct the date from other information. --ron |
Pay day
€žnbslarson€ť ezt Ă*rta: Better, but I'm not gett the correct dates: 4th Quarter 2009 year month 6 21 2009 9 7-Sep-09 09/16/09 09/30/09 10/06/09 10 21-Oct-09 10/01/09 10/15/09 10/21/09 10 6-Oct-09 10/16/09 10/31/09 10/06/09 11 23-Nov-09 11/01/09 11/15/09 11/21/09 Should be 21st Why? 21st of November, 2009 is Saturday, the next workday is 23rd of November, 2009, Monday! 11 6-Nov-09 11/16/09 11/30/09 12/04/09 Should be 12/04 - 06=Sun. In my table the first pay day in December is 12/07/09, Monday becuase it is the closest workday to 12/06/09, Sunday. 12 21-Dec-09 12/01/09 12/15/09 12/21/09 Use exactly the same layout as in my example, otherwise the formula doesn't work correctly, or specify the exact layout you want to use in order to adjust the formula! Stefi |
Pay day
.... see HELP for the WORKDAY function ...
-- David Biddulph "nbslarson" wrote in message ... Oops. Spoke too soon. How do I rule out holidays (Labor day)? "Ron Rosenfeld" wrote: On Thu, 23 Jul 2009 06:52:02 -0700, nbslarson wrote: Our working period is divided into 2 sections a month: 1st thru 15th and 16th thru last. We are paid on the 6th of the month and the 21st of the month OR the closest workday to the end of the pay period. Can you help me construct a formula to return this value? Thank you for any help you can offer. Further thoughts. If your decision regarding the pay day is properly expressed as: Pay day is 6 days after the end of the section; but if that day falls on a non-Workday, pay on the previous workday; then the formula is: A1: contains the ending date for the section (e.g. 30 Sep 2009) Payday: =WORKDAY(A1+7,-1) What this will return, in general, is the 6th and 21st of the month. However, if those days fall on a weekend (or holiday if you use the optional holidays argument for the WORKDAY function), then pay day will be on the preceding WORKDAY (e.g. Friday). If the formula returns a #NAME! error, see HELP for the WORKDAY function to correct this. A1 must contain an Excel date. Substitute whatever cell reference you have that contains the date, or a formula to construct the date from other information. --ron |
Pay day
On Thu, 23 Jul 2009 09:46:03 -0700, nbslarson
wrote: Oops. Spoke too soon. How do I rule out holidays (Labor day)? As I wrote previously: "What this will return, in general, is the 6th and 21st of the month. However, if those days fall on a weekend (or holiday if you use the optional holidays argument for the WORKDAY function)," If you look at HELP for the WORKDAY function, you will find out how to use the optional Holidays argument I mentioned. --ron |
Pay day
On Thu, 23 Jul 2009 09:38:01 -0700, nbslarson
wrote: Perfect, Ron. Thank you so much. That worked exactly! You're welcome. Glad to help. Thanks for the feedback. --ron |
Pay day
Thank you, Ron and David. The holiday elimination worked perfectly. Duh!
All I needed to do was make certain I had the correct dates in for the holidays. Thank you so very much for your help. "Ron Rosenfeld" wrote: On Thu, 23 Jul 2009 09:46:03 -0700, nbslarson wrote: Oops. Spoke too soon. How do I rule out holidays (Labor day)? As I wrote previously: "What this will return, in general, is the 6th and 21st of the month. However, if those days fall on a weekend (or holiday if you use the optional holidays argument for the WORKDAY function)," If you look at HELP for the WORKDAY function, you will find out how to use the optional Holidays argument I mentioned. --ron |
Pay day
On Fri, 24 Jul 2009 11:12:00 -0700, nbslarson
wrote: Thank you, Ron and David. The holiday elimination worked perfectly. Duh! All I needed to do was make certain I had the correct dates in for the holidays. Thank you so very much for your help. You're welcome. Glad to help --ron |
| All times are GMT +1. The time now is 12:04 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com