Pay day

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

 

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

 

[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

 

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

 

[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

 

[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

 

[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

 

[nbslarson]

Perfect, Ron. Thank you so much. That worked exactly!

 

[nbslarson]

Oops. Spoke too soon. How do I rule out holidays (Labor day)?

 

[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

 

[David Biddulph]

.... see HELP for the WORKDAY function ...

 

[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

 

[Ron Rosenfeld]

Perfect, Ron. Thank you so much. That worked exactly!

You're welcome. Glad to help. Thanks for the feedback.
--ron

 

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

 

[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