|
Number of paychecks in a month?
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 in some months, and I dont know which of these 4 months each year gets me the extra paycheck. Thanks for the help -- jkh1978 ------------------------------------------------------------------------ jkh1978's Profile: http://www.excelforum.com/member.php...o&userid=26461 View this thread: http://www.excelforum.com/showthread...hreadid=397278 |
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" wrote in message ... 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 in some months, and I dont know which of these 4 months each year gets me the extra paycheck. Thanks for the help -- jkh1978 ------------------------------------------------------------------------ jkh1978's Profile: http://www.excelforum.com/member.php...o&userid=26461 View this thread: http://www.excelforum.com/showthread...hreadid=397278 |
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 ------------------------------------------------------------------------ jkh1978's Profile: http://www.excelforum.com/member.php...o&userid=26461 View this thread: http://www.excelforum.com/showthread...hreadid=397278 |
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! ================================================== === "jkh1978" wrote in 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 ------------------------------------------------------------------------ jkh1978's Profile: http://www.excelforum.com/member.php...o&userid=26461 View this thread: http://www.excelforum.com/showthread...hreadid=397278 |
Thanks for the reply... but thats not what I need. I need to know the number of paychecks in a month. Some months have 5 fridays, so its unclear if its a month that i get 2 or 3 paychecks. What forumula would tell me this? -- jkh1978 ------------------------------------------------------------------------ jkh1978's Profile: http://www.excelforum.com/member.php...o&userid=26461 View this thread: http://www.excelforum.com/showthread...hreadid=397278 |
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 this 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 column holding your January dates -- Cutter ------------------------------------------------------------------------ Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848 View this thread: http://www.excelforum.com/showthread...hreadid=397278 |
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? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "jkh1978" wrote in message ... Thanks for the reply... but thats not what I need. I need to know the number of paychecks in a month. Some months have 5 fridays, so its unclear if its a month that i get 2 or 3 paychecks. What forumula would tell me this? -- jkh1978 ------------------------------------------------------------------------ jkh1978's Profile: http://www.excelforum.com/member.php...o&userid=26461 View this thread: http://www.excelforum.com/showthread...hreadid=397278 |
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) "jkh1978" wrote in message ... Thanks for the reply... but thats not what I need. I need to know the number of paychecks in a month. Some months have 5 fridays, so its unclear if its a month that i get 2 or 3 paychecks. What forumula would tell me this? -- jkh1978 ------------------------------------------------------------------------ jkh1978's Profile: http://www.excelforum.com/member.php...o&userid=26461 View this thread: http://www.excelforum.com/showthread...hreadid=397278 |
Sorry RagDyeR , I may have misundertstood what you meant. 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. -- jkh1978 ------------------------------------------------------------------------ jkh1978's Profile: http://www.excelforum.com/member.php...o&userid=26461 View this thread: http://www.excelforum.com/showthread...hreadid=397278 |
Did you get it all sorted out, or are you still having a problem?
-- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "jkh1978" wrote in message ... Sorry RagDyeR , I may have misundertstood what you meant. 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. -- jkh1978 ------------------------------------------------------------------------ jkh1978's Profile: http://www.excelforum.com/member.php...o&userid=26461 View this thread: http://www.excelforum.com/showthread...hreadid=397278 |
I still don't have the answer I need. -- jkh1978 ------------------------------------------------------------------------ jkh1978's Profile: http://www.excelforum.com/member.php...o&userid=26461 View this thread: http://www.excelforum.com/showthread...hreadid=397278 |
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. -- Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- "jkh1978" wrote in message ... I still don't have the answer I need. -- jkh1978 ------------------------------------------------------------------------ jkh1978's Profile: http://www.excelforum.com/member.php...o&userid=26461 View this thread: http://www.excelforum.com/showthread...hreadid=397278 |
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 Replace@mailinator with @tiscali.co.uk "jkh1978" wrote in message ... Sorry RagDyeR , I may have misundertstood what you meant. 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. -- jkh1978 ------------------------------------------------------------------------ jkh1978's Profile: http://www.excelforum.com/member.php...o&userid=26461 View this thread: http://www.excelforum.com/showthread...hreadid=397278 |
Sandy Mann, I tried yours but only got 2 for every month Please send a test spreadsheet to jkh1978 AT hotmail.com -- jkh1978 ------------------------------------------------------------------------ jkh1978's Profile: http://www.excelforum.com/member.php...o&userid=26461 View this thread: http://www.excelforum.com/showthread...hreadid=397278 |
Sample test sheet is on the way.
-- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "jkh1978" wrote in message ... Sandy Mann, I tried yours but only got 2 for every month Please send a test spreadsheet to jkh1978 AT hotmail.com -- jkh1978 ------------------------------------------------------------------------ jkh1978's Profile: http://www.excelforum.com/member.php...o&userid=26461 View this thread: http://www.excelforum.com/showthread...hreadid=397278 |
| All times are GMT +1. The time now is 04:17 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com