General Help

[Shamrock0515]

Hello Everyone,
I am new to excel and have only begun to use a small portion of its vast capabilities. I am creating a schedule sheet that list employees work days and shifts. We go by pay periods "PP-1 to PP-26". each PP goes in two week incriments. For example PP-1 is 1/1-1/14, i have put a drop down that has PP-1 to PP-26, i want each PP to represent a specific date in another cell, how can i accomplish this?

 

[CWatts]

There are several ways. You can have all the dates included in a table somewhere and use a VLOOKUP to search the list for it. You can also calculate the date based on your PP-1 cell.

Assuming your drop down is in cell A1, you can use this formula.

=RIGHT(A1,LEN(A1)-3)*14+40530

It will extract the numerical part of the drop down and skip the "PP-" part. 40530 is the serial date number for 12/18/2010. So for "PP-1" it extracts the 1, multiplies that by 14 and adds it to 12/18/2010, which will give you 1/1/11.

Next one will add 28 days which will give you 1/15/11 etc etc.

Hope that gets you started!

 

[Shamrock0515]

Thank you so much, i appreciate your help

 

[Shamrock0515]

the formula you gave me worked to perfection, thanks again, you were a tremendous help