Pay day

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.
 
S

Stefi

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:
 
N

nbslarson

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
 
S

Stefi

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:
 
R

Ron Rosenfeld

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
 
R

Ron Rosenfeld

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
 
N

nbslarson

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
 
S

Stefi

„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
 
R

Ron Rosenfeld

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
 
N

nbslarson

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.
 
R

Ron Rosenfeld

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top