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"
<[email protected]> 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" <[email protected]> wrote in message
news:[email protected]...
> 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"
> <[email protected]> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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"
> > <[email protected]> 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.
> >>
> >
>
>
>