Fortnight

[j.mcgown]

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.

 

[Myrna Larson]

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, 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.

 

[Dana DeLouis]

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

 

[j.mcgown]

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

Cheers

Jay