0

I'm trying to convert bi-weekly pay period to weekly dates. Lets say I have 3/2/2012, 3/16/2012, 3/30/2012 and some gaps and then again biweekly dates.

generate 3/9/2012, 3/16/2012, 3/23/2012, 3/30/2012 and so on...I was trying to use INDEX and MATCH but it would give me biweekly again. The perfect way would be to match and generate weeks from my randomized biweekly dates. Here is an example of my worksheet: enter image description here

Improve this question
6
  • If A1 contains the date 3/2/2012, using the formula =A1+7 does not help?
    – Hauke P.
    Nov 5, 2013 at 19:27
  • @Hauke P. but then I have random bi-weekly dates with some gaps between them.
    – A_Pointar
    Nov 5, 2013 at 19:34
  • Could you give an outline of how your original worksheet looks like? It's hard to give an answer without knowing your current data situation.
    – Hauke P.
    Nov 5, 2013 at 19:55
  • @Hauke P. uploaded it! :)
    – A_Pointar
    Nov 5, 2013 at 20:07
  • Ah, so you're looking for a way to automatically add new rows so that the resulting table will not contain be-weekly but weekly dates?
    – Hauke P.
    Nov 5, 2013 at 21:06

2 Answers 2

Reset to default
0

Here's one way to get a new column of weekly dates in P2 down based on the bi-weekly dates you already have in I2 down

Put this formula in P2

=INDEX(I$2:I$1000,(ROWS(P$2:P2)+1)/2)

and this formula in P3

=IF(P2>0,P2+7,0)

Format both of those cells with this custom format

m/d/yyyy;;

make sure you include the two semi-colons at the end

Now select both P2 and P3 and drag down the column until you get blanks

Improve this answer
1
  • Thank you! I like this thinking! It's be ideal if it had unique weekly dates without any duplicates! Any thought? This is the outcome I have so far: 10/15/2010 10/22/2010 10/29/2010 11/5/2010 11/12/2010 11/12/2010 11/19/2010 11/26/2010 12/3/2010 12/10/2010 12/23/2010 12/30/2010 1/21/2011 1/28/2011 6/24/2011 6/24/2011 7/1/2011 7/8/2011
    – A_Pointar
    Nov 6, 2013 at 14:35
0

Okay, here's how to accomplish this:

  1. Create a new sheet.
  2. Select column K and apply date data type formatting.

  3. In cell K2, enter this formula: (Replace Sheet1 with the name of the worksheet with your data).

    =IF(MOD(ROW(K2),2)=1,K1+7,INDIRECT("Sheet1!R"&(1+(ROW(K2))/2)&"C"&COLUMN(K2),FALSE))

  4. Drag this formula across the sheet as much as you need to.

The formula uses the INDIRECT function to calculate the location of the cell where it should get the data from.

If you need the cells in other columns to be blank, replace the equivalent for K1+7 in the formula with "".

Last but not least... while this formula works, here here's a warning about this method: The INDIRECT function is be pretty slow - so there might be some performance issues if you have a lot of data.

Improve this answer
2
  • Thanks! It is giving me weekly date based on the bi-weekly pay period I have but the weeks are very randomly put and I can't sort them.
    – A_Pointar
    Nov 6, 2013 at 14:28
  • Phew, that'll be pretty complex. Let's hope you'll get other answers.
    – Hauke P.
    Nov 6, 2013 at 18:56

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .