Excel - автоматически заполнить строку с диапазоном дат, соответствующим конкретному шаблону

Question

Учитывая конкретную дату начала (скажем, 11 января 2012 года), я хочу автоматически заполнить строку в excel с диапазоном дат, соответствующим следующему шаблону для дней (двухмесячный период оплаты).

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

Конечно, с фактическими датами в каждой ячейке, включая месяц и год (не просто повторяя два значения выше). То, что я ищу, это способ вставить один или два конкретных диапазона дат, таких как:

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

И Excel автоматически заполняет еще много ячеек в том же шаблоне:

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

Как я могу это сделать?

Answer 1

Отформатируйте столбцы в формате даты, который вы предпочитаете. Введите дату 1/11/2012 в ячейку A1 и дату 25.01.2012 в ячейку B2. Скопируйте следующий код в A2;

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

и скопируйте это в B2;

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

Идите вперед и выделите A2 и B2, чтобы перетащить их вниз так далеко, как вам нужно.

Должно выглядеть так, когда сделано;

Answer 2

Вот решение с одной строкой. Предупреждение, это бегемот формулы, и он требует отдельной справочной таблицы.

Где-то в вашей книге вам нужно будет создать следующую таблицу:

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

В моем примере эта таблица находится на I1:J12, поэтому настройте формулу соответствующим образом. В А1 введите:

=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)

Чтобы помочь разобрать это, вот формула, разделенная на части:

=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)

Answer 3

Метод без формул

1. Введите в A1: 11/1/2012
   Введите в A2: 11/2/2012
   Выделите A1: A2, заполните (перетаскивая мышью), например, до A12 (11/12/2012)

2. Введите в A13: 26/1/2012
   Введите в A14: 26/2/2012
   Выделите A13: A14, заполните (перетаскивая мышью), например, до B24 (26/12/2012)

3. Сортировать столбец A, который будет датой начала периода

4. (a) Повторите для столбца B, который будет датой окончания периода (каждые 25 и 10)
   ИЛИ ЖЕ
   (b) Установите следующую дату начала - 1 в качестве даты окончания, то есть введите в B1: =A2-1 , затем скопируйте вниз в B2: B23

5. Отформатируйте столбец A: B по желанию в формате ячейки (Ctrl-1), например, mmmm dd, yyyy

---

бонус

Чтобы отобразить дату начала и окончания в одной ячейке, разделенной дефисом:

1. Введите в С1: =TEXT(A1,"mmmm dd, yyyy") & " - " & TEXT(B1, "mmmm dd, yyyy")
   Это даст вам что-то вроде January 11, 2012 - January 25, 2012

2. Скопируйте в C2: C24