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?

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)

--
Rick (MVP - Excel)


"Forgone" <(E-Mail Removed)> wrote in message
news:d770bbb0-35fb-447b-9b4c-(E-Mail Removed)...
>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?
>

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
--------------
Jacob Skaria

"Forgone" wrote:

> 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?
>
>

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

On Mar 24, 6:50*pm, Bernd P <bplumh...@gmail.com> wrote:
> 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.