Серия дат в Excel

Серия дат в день

= Дата + 1

Если вам нужно создать динамическую серию дат с формулой, которая увеличивается на один день с одной даты начала, вы можете сделать очень простую формулу, которая просто добавляет 1 к каждой дате.

Серия дат в день

В этом примере B6 является жестко запрограммированной датой начала, а формула в B7:

= B6 + 1

Поскольку даты в Excel — это просто серийные номера (первая дата в стандартной системе даты Excel — 1 января 1900 г.), вы можете корректировать даты, просто добавляя или вычитая значения. Чтобы решить эту формулу, Excel просто добавляет 1 к дате в B6. Поэтому первая формула возвращает новую дату 1/31/2000, на один день позже начальной даты.

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

= B6 + 7

Серия дат по месяцам

= ДАТА(ГОД (дата); МЕСЯЦ (дата) + 1; ДЕНЬ (дата))

Если вам нужно создать динамическую серию дат с формулой, которая увеличивается на один месяц с одной даты начала, вы можете сделать это с помощью формулы, в которой используются функции ДЕНЬ, МЕСЯЦ, ГОД и ДАТА.

Серия дат по месяцам

В этом примере B6 является жестко запрограммированной датой начала, а формула в B7:

= ДАТА (ГОД (B6); МЕСЯЦ (B6) + 1; ДЕНЬ (B6))

Чтобы решить эту формулу, Excel сначала извлекает значения года, месяца и дня с даты в B6, а затем добавляет 1 к значению месяца. Затем новая дата будет собрана функцией ДАТА, используя тот же день и год, и месяц + 1 в месяц.

= ДАТА (ГОД (B6); МЕСЯЦ (B6) + 1; ДЕНЬ (B6))

= ДАТА(2010;1 + 1;15)

= ДАТА(2010;2;15)

= 2/15/2010

Поэтому первая формула возвращает новую дату 15.02.2010, на один день позже, чем дата начала.

Как только первая формула введена, она копируется вниз настолько, насколько это необходимо. Каждая последующая формула создает новую дату, увеличенную на один день.

Примечание. Если вы начинаете с даты 31 января, приведенная выше формула будет пропускать февраль и переходить к марту. Это происходит из-за того, что новая дата, 2/31/2010 не существует, поэтому Excel использует значение дня для перехода к 3/3/2010, через 3 дня после последнего дня февраля.

Если вам нужна серия дат, где каждая дата является последним днем месяца, вы можете использовать КОНМЕСЯЦА следующим образом:

= КОНМЕСЯЦА(B6;1)

Серия дат по выходным

= ЕСЛИ(ДЕНЬНЕД(дата) = 7; дата + 1; дата + (7-ДЕНЬНЕД(дата)))

Если вам нужно создать динамическую серию дат с формулой, включающей только будущие выходные (т. е. Сб и Вс), вы можете сделать это с помощью формулы, использующей функции ЕСЛИ и ДЕНЬНЕД.

Серия дат по выходным

В этом примере B5 является жестко запрограммированной датой начала, а формула в B6:

= ЕСЛИ (ДЕНЬНЕД(B5) = 7; B5 + 1; B5 + (7-ДЕНЬНЕД(B5)))

Чтобы решить эту формулу, Excel сначала вычисляет значение дня недели для даты в B5. По умолчанию рабочий день будет возвращать 1 для воскресенья и 7 для субботы. Затем Excel проверяет день недели внутри оператора ЕСЛИ, используя B5 = 7 в качестве логического теста. Если B5 = 7, дата в B6 является субботой и результат, если возвращается истина: B5 + 1. Таким образом, если B5 является субботой, формула возвращает следующий день (воскресенье).

Если нет, возвращается результат, если ложь:

B5 + (7-ДЕНЬНЕД(B5))

Чтобы решить эту часть формулы, Excel вычисляет значение дня недели B5, затем вычитает это значение из 7. Результат добавляется к B5. Итак, с понедельника по пятницу это выглядит так:

B5 + (7-2) = B5 + 5 <- Пн

B5 + (7-3) = B5 + 4 <- Вт

B5 + (7-4) = B5 + 3 <- Ср

B5 + (7-5) = B5 + 2 <- Чт

B5 + (7-6) = B5 + 1 <- Пт

Примечание: вам нужно указать дату как минимум за день до первой субботы, которую вы хотите сгенерировать.

Серия дат по рабочим дням

= РАБДЕНЬ(дата; 1)

Если вам нужно создать динамическую серию дат, которые являются рабочими днями (например, с понедельника по пятницу), вы можете сделать это с помощью простой формулы, которая использует функцию РАБДЕНЬ.

Серия дат по рабочим дням

В этом примере B6 является жестко запрограммированной датой начала, а формула в B7:

= РАБДЕНЬ(B6;1)

Excel решает эту формулу, используя функцию РАБДЕНЬ для возврата к следующему рабочему дню. РАБДЕНЬ запрограммирована на то, чтобы пропустить вперед по мере необходимости до следующего рабочего дня.

Если вам нужно учитывать праздники, вы добавляете необязательный аргумент функции РАБДЕНЬ для праздников:

= РАБДЕНЬ(дата; 1; праздничные дни)

Где праздники — это ссылка на список дат, которые представляют нерабочие дни.

Если вам нужно учитывать пользовательские выходные дни (например, выходные — это только суббота, воскресенье и понедельник и т. д.), вам нужно переключиться на более надежную функцию РАБДЕНЬ.МЕЖД, которая позволяет вам установить, какие дни недели считаются, считая выходные, подавая аргумент в выходные дни в виде числового кода.

Вы также можете создать серию рабочих дней с формулой, которая использует функцию ДЕНЬНЕД. В этом случае формула проверяет начальную дату, чтобы определить, является ли она субботой или нет. Если да, то к дате начала добавляется 3 дня. Если нет, добавляется только один день.

= ЕСЛИ(ДЕНЬНЕД(дата) = 6; дата + 3; дата + 1)

Серия дат по годам

= ДАТА (ГОД (дата) + 1; МЕСЯЦ (дата); ДЕНЬ (дата))

Если вам нужно создать динамическую серию дат с формулой, которая увеличивается на один год с одной даты начала, вы можете сделать это с помощью формулы, в которой используются функции ДЕНЬ, МЕСЯЦ, ГОД и ДАТА.

Серия дат по годам

В этом примере B6 является жестко запрограммированной датой начала, а формула в B7:

= ДАТА (ГОД (B6) + 1; МЕСЯЦ (B6); ДЕНЬ (B6))

Чтобы решить эту формулу, Excel сначала извлекает значения года, месяца и дня с даты в B6, а затем добавляет 1 к значению года. Затем новая дата будет собрана функцией ДАТА, используя тот же день, месяц и год + 1 в течение года.

= ДАТА (ГОД (B6) + 1; МЕСЯЦ (B6); ДЕНЬ (B6))

= ДАТА(2010 + 1;1;15)

= ДАТА(2011;1;15)

= 1/15/2011

Поэтому первая формула возвращает новую дату 1/15/2011, на год позже начальной даты.

Как только первая формула введена, она копируется вниз настолько, насколько это необходимо. Каждая последующая формула создает новую дату, увеличенную на один день.

Вы можете легко настроить эту формулу, если это необходимо. Например, если вам нужна серия дат, где каждая дата является первым днем нового года, вы можете использовать формулу, подобную этой

= ДАТА(ГОД (дата) +1;1;1)

Оцените статью
Добавить комментарий