How do I designate dates using info from a cooresponding drop down list?

[reznor9]

What I want to do is have a drop down list which allows the user t
select from 1-26 Payperiods in the year. Now that I know how to d
already using a drop down menu, no problem

What I envison happening upon selecting the Payperiod is that anothe
cell will populate with a designated Start date for that payperiod s
that the calendar I have within the spreadsheet will refect the date
within that payperiod

So for example if Payperiod 1 is selected from the dropdown I want th
start date to populate as Jan 1, 201
if Payperiod 2 is selected I want the start date to populate as Jan 15
2013... and so on and so forth

Can anyone assist me on how to do this

*to be clear I dont need assitance with the calendar, I just want t
know how to get the start date to auto populate based on data from th
payperiod drop down list...Now Im pretty sure I would have to mak
another list which had the 26 start dates, but Im puzzled on how t
connect the dots and get the data from the pay period list to populat
the other cell which houses the start date

 

[Ron Rosenfeld]

What I want to do is have a drop down list which allows the user to
select from 1-26 Payperiods in the year. Now that I know how to do
already using a drop down menu, no problem.

What I envison happening upon selecting the Payperiod is that another
cell will populate with a designated Start date for that payperiod so
that the calendar I have within the spreadsheet will refect the dates
within that payperiod.

So for example if Payperiod 1 is selected from the dropdown I want the
start date to populate as Jan 1, 2013
if Payperiod 2 is selected I want the start date to populate as Jan 15,
2013... and so on and so forth.

Can anyone assist me on how to do this?

*to be clear I dont need assitance with the calendar, I just want to
know how to get the start date to auto populate based on data from the
payperiod drop down list...Now Im pretty sure I would have to make
another list which had the 26 start dates, but Im puzzled on how to
connect the dots and get the data from the pay period list to populate
the other cell which houses the start date.

You don't define your pay periods accurately enough to be certain of the answer.
Superficially, one might assume that If your pay periods are 1-26, to obtain the start date of the pay period, merely subtract 1, multiply by 14, and add that to the first of the year. In other words, assume each pay period is 2 weeks, and that there are 52 weeks in a year.

For example:

(year of interest)
A1: 2013

A2: PayPeriod (your dropdown showing 1-26)

Payperiod start date:
=(a2-1)*14+date(a1,1,1)

HOWEVER, as 26 pay periods in a year implies a 52 week year. 52 weeks is only 364 days. How do you want to treat the extra day(s) each year?
For 2013 the 26th pay period would start on 17 December 2013 and, if it is a 14 day period, end on 30 December 2013. If the payperiods start over in 2014 on 1 January, what happens to 31 December???

Do you want to continue the "every two weeks" in perpetuity from 1/1/2013, or do you need to better define your pay periods?

 

[Spencer101]

What I want to do is have a drop down list which allows the user t
select from 1-26 Payperiods in the year. Now that I know how to d
already using a drop down menu, no problem.

What I envison happening upon selecting the Payperiod is that anothe
cell will populate with a designated Start date for that payperiod s
that the calendar I have within the spreadsheet will refect the date
within that payperiod.

So for example if Payperiod 1 is selected from the dropdown I want th
start date to populate as Jan 1, 2013
if Payperiod 2 is selected I want the start date to populate as Jan 15
2013... and so on and so forth.

Can anyone assist me on how to do this?

*to be clear I dont need assitance with the calendar, I just want t
know how to get the start date to auto populate based on data from th
payperiod drop down list...Now Im pretty sure I would have to mak
another list which had the 26 start dates, but Im puzzled on how t
connect the dots and get the data from the pay period list to populat
the other cell which houses the start date.

As long as your pay period selector drop down list populates a cell, yo
can easily use a VLOOKUP to populate the start date.

Let me know if you need help with this.


EDIT: See attachment. Is this what you mean?

If you're using Excel 2007 or later then the formula can be neatened u
but I've done it this way just in case you're using an older version

+-------------------------------------------------------------------
|Filename: Reznor9.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=708
+-------------------------------------------------------------------

 

[reznor9]

As long as your pay period selector drop down list populates a cell, yo
can easily use a VLOOKUP to populate the start date.

Let me know if you need help with this.


EDIT: See attachment. Is this what you mean?

If you're using Excel 2007 or later then the formula can be neatened u
but I've done it this way just in case you're using an older version.

That is exactly what I mean. It worked like a charm! Thank you Spencer
:

+-------------------------------------------------------------------
+-------------------------------------------------------------------