Fortnight
 

I would like to know if anyone knows how to calculate what 2 week period you
are in for a particular year given a date reference, for example.

I have a spreadsheet which I record my travel Times and Odometers on. I
useually add this up each 2 week (pay period) and get a sum, I would like to
be able to identify which 2 week period I am in so I can automatically sum it
up in a pivot table

Data as below

Start Date Destination KMs Month Year
DOW
15/03/2005 25 3 2005 2

I have seperated the Month, Year and Day of the week using formulas and
currently use my pivot table to sum KMs per month and year, I would love to
be able to do it per 2 week period, any help would be much appreciated.

First of all, why have you separated the parts of the date? You don't have to.
In a Pivot Table you can group dates by month and year. As for date
information, what you need in your list is a field for the travel date and
another for the paydate to which it belongs. (If you want to see the day of
the week for the travel date, you can format it as ddd mm/dd/yy, or something
similar.)

I think you should redo your table with these columns:

TravelDate Destination KM PayDate

As for getting the paydate that corresponds to a given travel date, you would
use a VLOOKUP formula and table with the the *start* of the pay period in the
1st column and the *end* of the pay period in the 2nd. Let's say you use
K2:L27 for that table.

To construct the table, put the starting date of the first pay period in K2.
In L2 put the formula =K2+13. In K3, the formula =K2+14, in L3, =L2+14. Then
copy these formulas down throu K27:L27.

Assuming your travel data is now in A:D, and the 1st data row is 2, the
formula in D2 that will get the payperiod is =VLOOKUP(A2,$K$2:$L$27,2)

Set up your pivot table to use the pay date instead of the travel date.

On Tue, 15 Mar 2005 18:47:03 -0800, "j.mcgown"
wrote:

I would like to know if anyone knows how to calculate what 2 week period you
are in for a particular year given a date reference, for example.

I have a spreadsheet which I record my travel Times and Odometers on. I
useually add this up each 2 week (pay period) and get a sum, I would like to
be able to identify which 2 week period I am in so I can automatically sum it
up in a pivot table

Data as below

Start Date Destination KMs Month Year
DOW
15/03/2005 25 3 2005 2

I have seperated the Month, Year and Day of the week using formulas and
currently use my pivot table to sum KMs per month and year, I would love to
be able to do it per 2 week period, any help would be much appreciated.

... I would love to
be able to do it per 2 week period, any help would be much appreciated.

In addition to grouping by Month and Year, you can also group by Days. Just
set the "Number of days" option to 14.


--
Dana DeLouis
Win XP & Office 2003


"Myrna Larson" wrote in message
...
First of all, why have you separated the parts of the date? You don't have
to.
In a Pivot Table you can group dates by month and year. As for date
information, what you need in your list is a field for the travel date and
another for the paydate to which it belongs. (If you want to see the day
of
the week for the travel date, you can format it as ddd mm/dd/yy, or
something
similar.)

I think you should redo your table with these columns:

TravelDate Destination KM PayDate

As for getting the paydate that corresponds to a given travel date, you
would
use a VLOOKUP formula and table with the the *start* of the pay period in
the
1st column and the *end* of the pay period in the 2nd. Let's say you use
K2:L27 for that table.

To construct the table, put the starting date of the first pay period in
K2.
In L2 put the formula =K2+13. In K3, the formula =K2+14, in L3, =L2+14.
Then
copy these formulas down throu K27:L27.

Assuming your travel data is now in A:D, and the 1st data row is 2, the
formula in D2 that will get the payperiod is =VLOOKUP(A2,$K$2:$L$27,2)

Set up your pivot table to use the pay date instead of the travel date.

On Tue, 15 Mar 2005 18:47:03 -0800, "j.mcgown"
wrote:

I would like to know if anyone knows how to calculate what 2 week period
you
are in for a particular year given a date reference, for example.

I have a spreadsheet which I record my travel Times and Odometers on. I
useually add this up each 2 week (pay period) and get a sum, I would like
to
be able to identify which 2 week period I am in so I can automatically sum
it
up in a pivot table

Data as below

Start Date Destination KMs Month Year
DOW
15/03/2005 25 3 2005 2

I have seperated the Month, Year and Day of the week using formulas and
currently use my pivot table to sum KMs per month and year, I would love
to
be able to do it per 2 week period, any help would be much appreciated.

Thankyou for you response, I had never even thought of using pay date, or
just changing the way they are grouped.

Cheers

Jay

"Dana DeLouis" wrote:

... I would love to
be able to do it per 2 week period, any help would be much appreciated.

In addition to grouping by Month and Year, you can also group by Days. Just
set the "Number of days" option to 14.


--
Dana DeLouis
Win XP & Office 2003


"Myrna Larson" wrote in message
...
First of all, why have you separated the parts of the date? You don't have
to.
In a Pivot Table you can group dates by month and year. As for date
information, what you need in your list is a field for the travel date and
another for the paydate to which it belongs. (If you want to see the day
of
the week for the travel date, you can format it as ddd mm/dd/yy, or
something
similar.)

I think you should redo your table with these columns:

TravelDate Destination KM PayDate

As for getting the paydate that corresponds to a given travel date, you
would
use a VLOOKUP formula and table with the the *start* of the pay period in
the
1st column and the *end* of the pay period in the 2nd. Let's say you use
K2:L27 for that table.

To construct the table, put the starting date of the first pay period in
K2.
In L2 put the formula =K2+13. In K3, the formula =K2+14, in L3, =L2+14.
Then
copy these formulas down throu K27:L27.

Assuming your travel data is now in A:D, and the 1st data row is 2, the
formula in D2 that will get the payperiod is =VLOOKUP(A2,$K$2:$L$27,2)

Set up your pivot table to use the pay date instead of the travel date.

On Tue, 15 Mar 2005 18:47:03 -0800, "j.mcgown"
wrote:

I would like to know if anyone knows how to calculate what 2 week period
you
are in for a particular year given a date reference, for example.

I have a spreadsheet which I record my travel Times and Odometers on. I
useually add this up each 2 week (pay period) and get a sum, I would like
to
be able to identify which 2 week period I am in so I can automatically sum
it
up in a pivot table

Data as below

Start Date Destination KMs Month Year
DOW
15/03/2005 25 3 2005 2

I have seperated the Month, Year and Day of the week using formulas and
currently use my pivot table to sum KMs per month and year, I would love
to
be able to do it per 2 week period, any help would be much appreciated.