Bi-weekly date dilema

[Spongebob]

I am trying to find a way to have excel recognize a bi-weekly pay period for
a timesheet I am working on, but I have run into a problem. I can easily
have excel count off every other Monday for a few weeks, but how do I tell it
where to start? Our pay periods always change depending on the year. So the
first Monday in September is a pay-day this year, but next year it might fall
on the second Monday in September.

Is there a way to give an example of a 'good' Monday and have excel handle
whether or not it is an increment of 14?

Thanks,
Jeff

 

[Gary''s Student]

Is the very first Monday of the year a payday or not??

 

[Billy Liddel]

I am trying to find a way to have excel recognize a bi-weekly pay period for
a timesheet I am working on, but I have run into a problem. I can easily
have excel count off every other Monday for a few weeks, but how do I tell it
where to start? Our pay periods always change depending on the year. So the
first Monday in September is a pay-day this year, but next year it might fall
on the second Monday in September.

Is there a way to give an example of a 'good' Monday and have excel handle
whether or not it is an increment of 14?

Thanks,
Jeff

Jeff

Here's the way I did it. Create a Table

First Monday in September
Year Date(a4,9,1) Weekday(A4) Diff Formula
2007 01/09/2007 7 2 03/09/2007
2008 01/09/2008 2 0 01/09/2008
2009 01/09/2009 3 6 07/09/2009
2010 01/09/2010 4 5 06/09/2010
2011 01/09/2011 5 4 05/09/2011
2012 01/09/2012 7 2 03/09/2012
2013 01/09/2013 1 1 02/09/2013
2014 01/09/2014 2 0 01/09/2014

Year is in A4 down.
B4: =DATE(A4,9,1)
C4: =WEEKDAY(DATE(A4,9,1))
E4:
=IF(WEEKDAY(B4)<2,B4+1,IF(WEEKDAY(B4)=2,B4,IF(WEEKDAY(B4)>2,B4+7-(WEEKDAY(B4)-2))))

We can now substitute the dates in the formula for Date and now functions.

=IF(WEEKDAY(date(year(today()),9,1))<2,date(year(today()),9,1))+1,IF(WEEKDAY(date(year(today()),9,1))=2,today(),IF(WEEKDAY(date(year(today()),9,1))>2,date(year(Today()),9,1)+7-(WEEKDAY(date(year(Today()),9,1))-2)))

I made a mistake here but I have to go to bed, maybe look at it again in the
morning.

 

[Billy Liddel]

This formula will give you the first Monday Payday of the year closest to 1st
sept.

=IF(WEEKDAY(DATE(YEAR(TODAY()),9,1))>2,(DATE(YEAR(TODAY()),9,1))+7-(WEEKDAY(DATE(YEAR(TODAY()),9,1)))-2,IF(WEEKDAY(DATE(YEAR(TODAY()),9,1))=2,DATE(YEAR(TODAY()),9,1),F(WEEKDAY(DATE(YEAR(TODAY()),9,1))<2,DATE(YEAR(TODAY()),9,1)+1)))

You can use this to calculate the Bi weekly dates.

Be careful with the wrapping. Paste to Notepad if necessary and copy from
there into Excel.

regards
Peter Atherton

 

[Spongebob]

Sometimes. We do not reset our payroll at the beginning/end of the year if
that is what you are asking. It always continues. The odd number of days in
a year makes this difficult for me to predict. I am certainly not a math
wizard and therefore I am having difficulty working out a formula to do so.

 

[Spongebob]

Wow, that was quite a response. It took me a while to even understand what
you did, however I now see it. While you have introduced me to the
usefullness of several more date formulas it still does not accomplish my
goal.

It appears that your formula will find the first Monday of September, but it
still does not tell me if that date is the beginning of our pay-period or not.

Maybe I am approaching this wrong. In Excel dates are represented as a
serial number. Maybe I just need a formula which will look at a date and see
if it is a multiple of 14 from a date which I already know is the beginning
of a pay-period. Is there a formula which does this?

 

[ward376]

Maybe I just need a formula which will look at a date and see

if it is a multiple of 14 from a date which I already know is the beginning
of a pay-period.  Is there a formula which does this?

With the dates in column A and your "good" beginning date in a1:

IF(INT((A2-$A$1)/14)-(A2-$A$1)/14=0,"Beginning of a Pay Period","Not a
Beginning of a Pay Period")

Cliff Edwards

 

[Spongebob]

Awesome, that did it. Thank you.

As far as I can tell it also works beyond leap-year. Is that a correct
determination?

 

[ward376]

Yes, it will keep working regardless of whether there's a leap year
involved. It just checks to see whether the difference between the
date in question and the reference date is evenly divisible by 14.

Cliff Edwards