2

Given a specific start date (say Jan, 11th 2012 for example), I want to fill down a row in excel automatically with a date range matching the following pattern for the days (bi-monthly pay period).

1. 11th - 25th
2. 26th - 10th

Of course with actual dates in each cell including the month and year (not simply repeating the two values above). What I'm looking for is a way to put in one or two specific date ranges, such as:

January 11th, 2012 - January 25th, 2012
January 26th, 2012 - February 10th, 2012

And have excel automatically fill down many more cells in the same pattern:

January 11th, 2012 - January 25th, 2012
January 26th, 2012 - February 10th, 2012
February 11th, 2012 - February 25th, 2012
....
December 11th, 2012 - December 25th, 2012

How can I do this?

2
  • Do they need to be in the same cell? Or can January 11th in A1 with January 25th in A2, January 26th in B1 with February 10th in B2 and so on?
    – CharlieRB
    Jan 26, 2012 at 22:01
  • The way you described would be fine too.. but I do need to have the months and years.. not just an alternating 11, 26, 11, 26, 11, 26 etc..
    – Joe M.
    Jan 26, 2012 at 22:27

3 Answers 3

2

Format your columns to the date format you prefer. Enter the date of 1/11/2012 into cell A1 and the date of 1/25/2012 in cell B2. Copy the following code into A2;

=DATE(YEAR(B1),MONTH(B1), DAY(B1)+1)

and copy this into B2;

=IF(DAY(B1)=10, DATE(YEAR(B1), MONTH(B1), DAY(25)), DATE(YEAR(B1),MONTH(B1)+1, DAY(10)))

Go ahead and highlight A2 and B2 to drag them down as far as you need.

Should look like this when done;

Screen Shot

4
  • The formula for B2 isn't working for me.. it just results in "40949".. any idea?
    – Joe M.
    Jan 27, 2012 at 17:39
  • You need to set the format for the cells in that column by highlighting the column, right click, choose "format cells" and select the date format you prefer on the number tab.
    – CharlieRB
    Jan 27, 2012 at 17:57
  • Did you get this to work for you?
    – CharlieRB
    Jan 31, 2012 at 21:57
  • Yes sorry for the delay I just got back to this and it does work after formatting the cells to a date format. Thanks!
    – Joe M.
    Feb 3, 2012 at 17:38
1

Here's a single row solution. Warning, it's a behemoth of a formula, and it requires a separate look-up table.

Somewhere in your workbook, you'll need to create the following table:

0   January
1   February
2   March
3   April
4   May
5   June
6   July
7   August
8   September
9   October
10  November
11  December

In my example, this table is at I1:J12, so adjust your formula accordingly. In A1 enter:

=VLOOKUP(MOD(ROUNDDOWN((ROW()-0.5)/2,0),12),$I$1:$J$12,2,FALSE) & IF(ISODD(ROW())," 11th, "," 26th, ") & 2012 + INT((ROW()-0.5)/24) & " - " & VLOOKUP(MOD(ROUNDDOWN(ROW()/2,0),12),$I$1:$J$12,2, FALSE) & IF(ISODD(ROW())," 25th, "," 10th, ") & 2012 + INT(ROW()/24)

To help parse this, here's the formula broken down by part:

=VLOOKUP(MOD(ROUNDDOWN((ROW()-0.5)/2,0),12),$I$1:$J$12,2,FALSE) & 
 IF(ISODD(ROW())," 11th, "," 26th, ") &
 2012 + INT((ROW()-0.5)/24) &
 " - " &
 VLOOKUP(MOD(ROUNDDOWN(ROW()/2,0),12),$I$1:$J$12,2, FALSE) &
 IF(ISODD(ROW())," 25th, "," 10th, ") &
 2012 + INT(ROW()/24)
4
  • Wow thanks for your effort.. I tried with the lookup table in the same spot so I wouldn't have to change anything, but when I pasted the formula in A1 I just got "#NAME?"
    – Joe M.
    Jan 27, 2012 at 17:43
  • Curious. I did this in Excel 2007. What version of Excel are you using? #NAME? suggests that your version doesn't support one of the functions I used.
    – Excellll
    Jan 28, 2012 at 2:00
  • You could use the 'Evaluate Formula' tool to step into the formula in A1 and see where the error is occurring. That might help identify the guilty party.
    – Excellll
    Jan 28, 2012 at 2:01
  • Yes you're right, it was an older Excel (2003). I tried on a computer with Excel 2007 and this works fine. I wish I could choose 2 answers but I ended on the other one because it works also in older versions of Excel and because I understand how it works =p But I really appreciate your help!
    – Joe M.
    Feb 3, 2012 at 17:40
0

No formula method

  1. Enter in A1: 11/1/2012
    Enter in A2: 11/2/2012
    Highlight A1:A2, fill down (by mouse dragging), for example up to A12 (11/12/2012)

  2. Enter in A13: 26/1/2012
    Enter in A14: 26/2/2012
    Highlight A13:A14, fill down (by mouse dragging), for example up to B24 (26/12/2012)

  3. Sort column A which would be the period start date

  4. (a) Repeat for column B which would be the period end date (every 25th & 10th)
    OR
    (b) Set the next start date - 1 as end date, i.e. enter in B1: =A2-1, then copy down to B2:B23

  5. Format column A:B as desired in Cell Format (Ctrl-1), for example mmmm dd, yyyy


Bonus

To display the start & end date in single cell seperated with hyphen:

  1. Enter in C1: =TEXT(A1,"mmmm dd, yyyy") & " - " & TEXT(B1, "mmmm dd, yyyy")
    It would give you something like January 11, 2012 - January 25, 2012

  2. Copy down to C2:C24

2
  • Step 1 seemed to work fine but somehow every time I try step 2 the fill down is incrementing the year and only alternating the month between 1 and 2.
    – Joe M.
    Feb 3, 2012 at 17:37
  • The input maybe treated as text caused by incorrect date format. Just use the usual date format in your OS, maybe mm/dd/yyyy, should be okay.
    – wilson
    Feb 7, 2012 at 7:05

You must log in to answer this question.

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