Calculating Pay Periods

[Forgone]

I have been trying to figure out how to work out the first pay period
after a specific date using if paid on a fortnightly basis. The
primary function would be to work out the first pay period in the
fiscal year and then one would count the number of pay periods. It'll
be used to forecast the f/nightly cash flow for a salary budget.

The financial year:
START: 1st July
END: 30 June

The history of the first pay periods have been:

Thursday, 1 July 2004
Thursday, 14 July 2005
Thursday, 13 July 2006
Thursday, 12 July 2007
Thursday, 10 July 2008
Thursday, 9 July 2009

Without knowing the first pay date, I'm struggling to figure out a way
to work it out automatically, so that the template could be used for
many years to come without having to manually update the first pay
period.

Any suggestions?

 

[Rick Rothstein]

Here is a VB function to return the first pay date in a specified year...

Function FirstPayDate(YearNumber As Long) As Date
Dim JulyFirst As Date
Dim BaseYear As Date
BaseYear = DateSerial(2004, 7, 1)
JulyFirst = DateSerial(YearNumber, 7, 1)
FirstPayDate = 13 + JulyFirst - ((JulyFirst - BaseYear - 1) Mod 14)
End Function

This worksheet formula should do the same thing (assuming the year is in
A1)...

=13+DATE(A1,7,1)-MOD(DATE(A1,7,1)-DATE(2004,7,1)-1,14)

 

[Jacob Skaria]

Please try with the below formula

Column A represent years A1 = 2003, A2 = 2004, A3 = 2005 and so on..
Paste the below formula in B1

=TEXT(IF(WEEKDAY(DATE(A1,7,1))<6,DATE(A1,7,13-WEEKDAY(DATE(A1,7,1))),DATE(A1,7,20-WEEKDAY(DATE(A1,7,1)))),"dd-mm-yyyy")

If this helps click Yes

 

[Bernd P]

Hello,

How accurate do you need this to be?

Rick's VBA solution and worksheet function solution are fine (the VBA
returns 15-Jul-2004 for 2004, though) if you do not need to care about
bank holidays.

But what will happen on Independence Day 2013, 2024 and 2047? Are you
paying on Friday 5-Jul then?

Wouldn't you need a holiday calendar like http://www.sulprobil.com/html/holiday_table.html
for your payments anyway?

Regards,
Bernd

 

[Forgone]

Hello,

How accurate do you need this to be?

Rick's VBA solution and worksheet function solution are fine (the VBA
returns 15-Jul-2004 for 2004, though) if you do not need to care about
bank holidays.

But what will happen on Independence Day 2013, 2024 and 2047? Are you
paying on Friday 5-Jul then?

Wouldn't you need a holiday calendar likehttp://www.sulprobil.com/html/holiday_table.html
for your payments anyway?

Regards,
Bernd

How accurate do you need this to be?

Hi Bernd, not that accurate.... I'm just trying to do a salary
forecast for now....... but that link does look interesting..... I'll
see if I can adopt it for AUS holidays as that may come in handy when
trying to work out penalty rates for working weekends.