• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

calculate number of paydates in month

R

Ronv777

New Member
I need to calculate the number of bi-weekly pay dates occurring each month. At this time I am just looking at a calendar and entering the number in the cell. Is it possible to calculate this for each month?
 
Which day of the week is payday? Also, when is the first payday of the year (or some other reference point)?
 
Assumptions:

First payday is in cell A1 (in case you want to change it).

Date for the month you are interested in is in B1.


I think this works:

=INT((EOMONTH(B1,0)-$A$1)/14)-INT((EOMONTH(B1,-1)-$A$1)/14)


EOMONTH functions are used to get last of this month, and the previous month (so we don't count those days)
 
I don't have an EOMONTH function in my version of Excel. I am using Office 2003. Am I missing something? OOPS I went to help and maybe I do, I need to read up on this a little more. Thanks for the help. If I can't figure this out I will be back. I was very surprised at how quickly I got help. Thanks again.
 
The EOMONTH function is part of the Analysis ToolPak add-in. Should be able to go to Tools - Addins, and check the ToolPak.


Usually I try to avoid the add-in functions when using public forums, but EOMONTH is much easier than this (if you can't use the ToolPak)


=DATE(YEAR(A1),MONTH(A1)+1,1)-1

for last day of this month and

=A1-DAY(A1)

for last day of previous month
 
Thanks I found the Toolpak. I had done a complete install when I installed Office so I thought I had everything. Thanks for showing me what I was missing. :) I have a lot to learn.
 
Once I figured out how to use your formula it worked like a charm. By the way I didn't spend the last 40 minutes trying to figure it out. :) I am also cooking dinner. I am trying to create some spreadsheets for my church to use in their payroll accounting. I have been trying to figure this out for a couple of days. (Not the whole time :> ) I really appreciate how quickly you helped me. Thanks again.


Ron
 
You must log in or register to reply here.
Back
Top