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

Finding the first pay period of the year ...

C

crsena

New Member
Hello, I'm curious to know of a formula or macro that can help me look at a pay slip, then quickly find the first pay period in which a person actually received pay in that year.


Say, for example, someone gets paid $6501 in gross earnings for the current year, and $847 in gross earnings for the period with a weekly pay frequency. That period ends on 12/24, weekly period begins on the Sunday of every week, ends on Saturday. Using that example, I can just divide 6501 by 847 and the results indicates that there have been 7.6 pay periods, rounding up to 8 because of overtime. Going back eight weeks and I can find the first pay period in which pay had been received, mid September.


There has to be a MUCH easier way to find out all of that with a function or a formula.


Would someone please take the time to explain ?


I greatly appreciate it.
 
Hi crsena,


How have u arranged data? I mean can u tell me the layout of the ur sheet?? Or a sample sheet?


In order to roundup/rounddown things, we can use ceiling()/floor() functions in to get result of 8th & 7th week, If() statement to select b/w floor and ceiling. Here is a sample statement:


IF(RIGHT(TOTAL PAY/PAY,2)>0.50,CEILING(TOTAL PAY/PAY,1),FLOOR(TOTAL PAY/PAY,1))


Regards,

Faseeh
 
Hi ,


A formula to do what you want will do mathematically exactly what you have explained in writing.


Let us assume your gross earnings during a certain period , e.g. 6501 , are in cell H10 ; assume the earnings during any individual period are in cell H11 ( in this case , we can take your example figure of 847 ).


To calculate the number of periods , use the formula :


=ROUNDUP(H10/H11,0)


Let us also assume that the last pay date e.g. 12/24/2011 is in cell H12.


Since the payment is made every Saturday , the last period is from 12/18/2011 ; the one before that is from 12/11/2011 to 12/17/2011 , and so on.


To get the Sunday of the first pay period , use the following formula :


=H12-7*(ROUNDUP(H10/H11,0))+1


Narayan
 
Firstly, you are making some assumptions that may or may not be true. Secondly, if the result is 7.6 then most likely the first pay period was no more than 7 weeks earlier, as you make MORE money with OT, not less. And since OT is possible, how do you know that the present check does not have the OT, causing the "first pay period" to be actually earlier than calculated, as you had more money per paycheck if this was the only check with OT?


So, disregarding everything I just posted, here's a way to start your formula:


To find the day of the week:

=WEEKDAY(date,1)


The above will return 1 if the weekday of some date is a Sunday.


Is any given day a Sunday? Easy to add a check for that:

=if(weekday(date,1)=1,"Sunday","Not Sunday")


The above returns a "Sunday" if the day is a Sunday and "Not Sunday" if it is not a Sunday.


Now, for the pay, you can take a cell for gross pay ytd and divide it by the gross pay weekly:


Let's put some data into cells:


B1 = Pay date on the pay stub = PAYDATE

B2 = Gross pay Year to Date, also from the stub = YTD

B3 = Weekly pay, from the stub = PAY


Now, to find the "probable number of weeks", you'd divide YTD by Weekly:

=B2/B3

"Divide the year-to-date pay by this stub's pay"


You want to round down this number, as I showed earlier:

=INT(B2/B3)

"Disregard any fractional portion"


You now have a number of weeks before the entered date that the pay most probably started. Multiply this number times 7 (number of days in a week), and SUBTRACT that number from the pay date:


=B1-(7*(INT(B2/B3)))

"Go that many weeks earlier, using a 7-day week"


You can add "is it a sunday?" checking:

=IF((WEEKDAY(B1,1)=1),(B1-(7*INT(B2/B3))),"Not Sunday")


Make sure any date cells are formatted as Dates, and you should have a working formula.


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