Pay day

  • Thread starter Thread starter nbslarson
  • Start date Start date
N

nbslarson

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.
 
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:
 
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
 
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:
 
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.
Click to expand...

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
 
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.
Click to expand...

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
 
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
 
„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
Click to expand...

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.
Click to expand...

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
Click to expand...

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
 
Oops. Spoke too soon. How do I rule out holidays (Labor day)?
Click to expand...

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
 
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.
 
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.
Click to expand...

You're welcome. Glad to help
--ron
 
Back
Top