Bi-weekly pay period

[Luigi802]

How can I calculate the last pay day in a given month based on a bi-weekly pay period, for example July 1st was the 1st pay day of this month, how could I automatically calculate what the last pay day would be?

 

[WarPigl3t]

By weekly means every 14 days. There are 52 weeks in a year. 7 days in a week. So the last pay date before it starts all over again is =(first pay date) + (52 * 7)

 

[CsJHUN]

I totally dont get this 2 week pay thingy, but it sounds like you need =lastpayday+14 or =lastpayday-14 :S

 

[Luigi802]

Not exactly, if the 1st payday of this month is July 1st then the last payday of this month would be July 29th, if it were a bi-weekly (14 day pay period) How could I make it automatically calculate that?

 

[CsJHUN]

uhm, maybe =lastpayday+28 ?

 

[Luigi802]

uhm, maybe =lastpayday+28 ?

I mean yes, but that won't reciprocate very well every month because it is just adding 28 days, not every month has the same amount of days. I think it'd have to be something like this
=DATE(2016,1,1)+CEILING((EOMONTH(B2,-1)+1)-DATE(2016,1,1)+1,30) Where as "DATE(2016,1,1)" was the 1st payday of this year and "B2" is july 1st or the 1st payday of this month. This works well for this month but again, it won't reciprocate on to the following months.

 

[CsJHUN]

So you get payed on the first day of the month and 14+14 later?

 

[CsJHUN]

so you get payed on the first day of the month and 14+14 later?

=if(month(b2+28)-1=month(b1),eomonth(b2,0),b2+28)

 

[Luigi802]

=if(month(b2+28)-1=month(b1),eomonth(b2,0),b2+28)

Awesome, that seemed to work, THANK YOU!! I guess I was just overly complicating it.