Auto Fill Pay Periods

B

bdehning

There must be a post already with answers but I can't seem to locate it so I
need some help.

I have an Excel Spreadsheet where I have field called Pay Period and would
be 1-26 for the year.

How do I set up the sheet so that when I enter a specific Pay Period, fields
for Report Dates_________ through ______________ are filled with the
correct 14 day period for that Pay Period entered?

For example if Pay Period 25 the fields would be 11/27/05 and 12/10/05.
 
R

Roger Govier

Hi

I would hold start of Payroll year in a cell on the Sheet,say A1, with 12/12/04

And, supposing you enter the Pay period number in B1, 25
Then in the cell holding the heading you enter
="Report Dates "&Text(A1+B1*14,"mm/dd/yy")&" through
"&text(A1+B1*14+14,"mm/dd/yy")

Alternatively, instead of holding the startdate on th sheet,
Insert>Name>Define> Name Startdate Refers to =12/12/04
Substitute the word stardate for A1 in the 2 references in the above formula.

Regards

Roger Govier
 
B

bdehning

I am still confused. I am confused about cell holding the heading?

Then in the cell holding the heading you enter
="Report Dates "&Text(A1+B1*14,"mm/dd/yy")&" through
"&text(A1+B1*14+14,"mm/dd/yy")
Click to expand...

Can you explain in more detail? I have two fields that need data to appear.
The Report Date and the Through Date.


Brian
 
B

bdehning

Ok I got what your say now as I can have all that Text come from one field
across the spreadsheet and can insert spaces to move it as I need to fill in
the areas.

Now the issue I have is that I lost the ability to show every day of the
week with the date as I used to key off the the Through Date Field by cell
number. Unfortunately now with the heading field I lost that field to key
on.

What can I use now to have the 14 days of the week show the correct date in
a field next to the Day of the week whenever the Pay Period is changed and
not lose the formula you supplied that does work?
 
R

Roger Govier

Hi

The formula
=A1+B1*14 gives your start date
=A1+B1*14+14 gives your end date
dependent upon where you are storing your Payroll year Start date and where
you are entering your Period number.

Just insert these formulae into the cells you originally had dates in.

Regards

Roger Govier
 
Top