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

reznor9

unread,

Dec 9, 2012, 8:35:50 AM12/9/12

to

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.

--
reznor9

Ron Rosenfeld

unread,

Dec 9, 2012, 8:53:07 AM12/9/12

to

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

unread,

Dec 9, 2012, 8:55:25 AM12/9/12

to

As long as your pay period selector drop down list populates a cell, you
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 up
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|
+-------------------------------------------------------------------+

--
Spencer101

reznor9

unread,

Dec 9, 2012, 10:25:15 AM12/9/12

to

Spencer101;1607915 Wrote:
> As long as your pay period selector drop down list populates a cell, you
> 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 up
> 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.
:)

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

--
reznor9