Number of paychecks in a month?

[jkh1978]

In my spreadsheet, I have a column for each month, 8/1/05, 9/1/05
etc...

What I want to know is, how many paychecks will I receive each month?

I get paid every other week on a Friday.

My first attempt with help from reading other posts was to do this

=(4+(DAY(D1-DAY(D1)+1-WEEKDAY(D1-DAY(D1+5))+35)>7))/2

To get the number of fridays, divided by 2. But that returns 2.5 i
some months, and I dont know which of these 4 months each year gets m
the extra paycheck.

Thanks for the hel

 

[Bob Phillips]

Nor do we. If you get paid in the final fri of a 4 fri month, you will only
get 2 the following month even if it has 5 fri.

It all depends upon where you start from, only you know that.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[jkh1978]

That's part of what I'm stuggling with... how do I get that fact into
the formula?

September is a 3 paycheck month.

 

[RagDyeR]

One way.

You get paid every 14 days ... right?

Put the date of your first paycheck in say Z1.
In Z2 enter
=Z1+14
And copy down, giving you a list of paydays.

Then enter this formula in the appropriate row in say Column A for January,
and copy across for each month:

=SUMPRODUCT(--(TEXT($Z$1:$Z$26,"mm-yy")=TEXT(A1,"mm-yy")))

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

message
That's part of what I'm stuggling with... how do I get that fact into
the formula?

September is a 3 paycheck month.

 

[jkh1978]

Thanks for the reply... but thats not what I need. I need to know th
number of paychecks in a month.

Some months have 5 fridays, so its unclear if its a month that i get
or 3 paychecks. What forumula would tell me this

 

[Cutter]

Not a very elegant solution but it gives you what you want:

As RagDyer said put your first pay date of the year in Z1
In Z2 put =Z1+14
copy down until you get a date past December
In AA1 put =MONTH(Z1) and format as number (no decimals)
Copy this down to the same row as your last formula in Col Z

Now in a cell in the column that has your January date put thi
formula:
=COUNTIF(AA1:AA26,COLUMNS($A1:A1))
copy this formula across the remaining 11 columns holding your dates
NOTE: I used column A as an example - change it to match the colum
holding your January date

 

[Ragdyer]

The formula that I suggested will count the paychecks in each month and
display that count wherever you insert the formula.
The formula is set to be copied to each column, and read the column date
from the top row that column, and compare that date to the list of your
paydays that I directed you to create, in an "out-of-the-way" location of
your sheet.

Would you care to try it before commenting that it doesn't do what you wish?

 

[Bob Phillips]

Well I tried it and it tells me that info.

You do not to setup the table the start with, but it's fine then.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Sandy Mann]

One way is to check is the Friday 28 days ahead of the 1st Friday in the
month is still in the same month.

Adapting a formula for the 1st Friday originally posted by Ron Rosenfeld
almost exactly a year ago try:

=IF(MONTH(DATE(YEAR(A1),MONTH(A1),1)+7-WEEKDAY(DATE(YEAR(A1),MONTH(A1),2))+28)=MONTH(A1),"3
paychecks","2 paychecks")

With any date in the target month in A1
--
HTH

Sandy
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk

 

[jkh1978]

Sorry RagDyeR , I may have misundertstood what you meant.

The formula from Sandy Mann is not right... It tells me the months wit
3 weeks, not those two months where the extra paycheck falls

 

[RagDyer]

Did you get it all sorted out, or are you still having a problem?

 

[jkh1978]

I still don't have the answer I need.

 

[RagDyeR]

Would you like me to send you my test sheet?

If so, post back with your address in pseudo text so the spammers can't
decipher it.

 

[Sandy Mann]

The formula from Sandy Mann is not right... It tells me the months with

3 weeks, not those two months where the extra paycheck falls

That is because I failed to check if the first Friday was a pay day. With
any pay Friday in D1 try:

=IF((AND(MOD(A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+2)-D1,14)=0,MONTH(A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+2)+28)=MONTH(A1))),"3
paychecks","2 paychecks")

(Shamlessly stealing a formula for the 1st Friday form Daniel.M this time
<g>)

--
HTH

Sandy
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk

 

[jkh1978]

Sandy Mann, I tried yours but only got 2 for every month

Please send a test spreadsheet to jkh1978 AT hotmail.co

 

[RagDyer]

Sample test sheet is on the way.