Как в сводной таблице поменять формат даты

Как сгруппировать по месяцам в сводной таблице в Google Таблицах

Вы никогда не сможете понять истинную мощь инструментов для работы с электронными таблицами, таких как Google Таблицы и Excel, пока не освоите использование сводных таблиц.

Эти таблицы могут перевернуть ваши данные, чтобы выжать только ту информацию, которую вы хотите знать. Таким образом, вы сможете понять свои данные так, как вам не позволили бы простые формулы и фильтры.

Иногда может потребоваться сгруппировать данные по дате, чтобы вы могли проводить сравнительные исследования или анализировать тенденции за определенный период времени.

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

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

Как сгруппировать по месяцам в сводной таблице Google Таблиц

Допустим, у вас есть следующий набор данных:

Предположим, что из приведенного выше набора данных вы хотите создать сводную таблицу, которая будет отображать общие продажи по месяцам. Для этого нужно двигаться шаг за шагом. Это означает, что вам необходимо:

• Убедитесь, что значения даты указаны в правильном формате DATE.
• Создайте сводную таблицу, в которой будут отображаться общие продажи по дате.
• Сгруппируйте сводную таблицу для отображения общих продаж по месяцам
• Сгруппируйте сводную таблицу для отображения общих продаж по месяцам за каждый год.

Давайте рассмотрим эти четыре шага один за другим.

Убедитесь, что ваши значения даты находятся в правильном формате DATE

Как видно из набора данных, даты указаны в столбце OrderDate. В этом столбце отображается период времени или продолжительность, в течение которых были совершены (или заказаны) продажи. Для правильной работы группы сводной таблицы важно, чтобы значения в столбце OrderDate были в формате DATE, который по умолчанию равен ММ / ДД / ГГГГ (в большинстве случаев).

Чтобы отформатировать даты, выполните следующие действия:

• Выберите столбец OrderDate (столбец A на нашем листе).
• Щелкните меню «Формат» на ленте меню.
• Наведите указатель мыши на параметр «Число» в появившемся раскрывающемся списке.
• Откроется подменю Number.
• Теперь вы можете выбрать опцию «Дата», если хотите, чтобы ваши значения были отформатированы в формате даты по умолчанию.

Примечание . Если вам нужен другой формат даты, вы можете навести указатель мыши на «Другие форматы» после шага 4, а затем щелкнуть «Дополнительные форматы даты и времени». Откроется окно «Пользовательские форматы даты и времени», в котором вы можете выбрать нужный формат даты.

Создание сводной таблицы для отображения общих продаж по дате

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

• Щелкните меню «Данные» на ленте меню.
• В появившемся раскрывающемся меню выберите параметр «Сводная таблица».
• Теперь вы должны увидеть окно с вопросом, хотите ли вы вставить сводную таблицу на существующий лист или на новый лист. Выберите тот вариант, который вам больше нравится. Для наглядности всегда лучше создавать его на новом листе.
• Щелкните по кнопке Create.
• Это должно создать вашу сводную таблицу либо на том же листе, либо на новом листе, в зависимости от того, что вы выбрали на шаге 3.
• Ваша сводная таблица на этом этапе должна выглядеть как на снимке экрана, показанном ниже:
• Должна быть сетка, отображающая «Строки», «Столбцы» и «Значения».
• Теперь вы можете начать заполнять сводную таблицу необходимыми данными. В правой части окна вы должны увидеть редактор сводной таблицы. Это поможет вам указать, что должно быть в вашей сводной таблице.
• Теперь мы хотим, чтобы в нашей сводной таблице было два столбца (изначально) — Дата заказа и Общий объем продаж на эту дату. Итак, в категории «Строки» нажмите «Добавить».
• В появившемся раскрывающемся списке выберите OrderDate. Это добавит каждую уникальную дату заказа в отдельные строки вашей сводной таблицы.
• Затем мы хотим увидеть общий объем продаж для каждой даты заказа. Итак, в категории «Значения» нажмите «Добавить».
• В появившемся раскрывающемся списке выберите Всего. Это отобразит сумму всех продаж для каждой даты заказа.

Сводная таблица уже начинает обретать смысл. Но было бы больше смысла и легче читать, если бы даты были сгруппированы по месяцам.

Итак, как только вы закончите добавлять нужные строки, столбцы и значения, вы можете начать группировать значения по месяцам.

Группировка значений сводной таблицы по месяцам

Google Таблицы предоставляют действительно простой способ группировать значения сводной таблицы по датам. Вот шаги:

• Щелкните правой кнопкой мыши любую дату в столбце OrderDate.
• В появившемся контекстном меню выберите или наведите указатель мыши на «Создать группу дат сводной таблицы».
• Вы должны увидеть подменю с множеством опций для группировки по дате.
• Вы заметите, что есть несколько вариантов даты, по которым вы можете сгруппировать их. Вы можете группировать по дню, неделе, месяцу, кварталу, году и даже по их комбинации. Чтобы сгруппировать по месяцам, выберите вариант «Месяц».
• У вас также есть возможность выбрать «Год-месяц». При этом ваши данные будут сгруппированы сначала по годам, а затем по месяцам для каждого года.

Вот как должна выглядеть ваша сводная таблица:

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

Группировка значений сводной таблицы по месяцам на каждый год

Лучший способ провести сравнительные и аналитические исследования ваших данных — это организовать сводную таблицу для отображения месяцев в строках и лет в столбцах, как показано ниже:

Для этого вам сначала нужно отсортировать строки по «Месяцу», а не «Год-месяц». Вот шаги, которые вам необходимо выполнить:

• Щелкните правой кнопкой мыши любую дату в столбце OrderDate.
• В появившемся контекстном меню выберите или наведите указатель мыши на «Создать группу дат сводной таблицы».
• Вы должны увидеть подменю с множеством опций для группировки по дате.
• Чтобы сгруппировать по месяцам, выберите вариант «Месяц».
• Затем нажмите кнопку «Добавить» в категории «Столбцы» редактора сводной таблицы.
• В появившемся раскрывающемся списке выберите OrderDate.
• Это должно отображать дату каждого заказа в столбцах.
• Щелкните правой кнопкой мыши одну из дат.
• В появившемся контекстном меню выберите или наведите указатель мыши на «Создать группу дат сводной таблицы».
• Вы должны увидеть подменю с множеством опций для группировки по дате.
• Чтобы сгруппировать по году, выберите вариант «Год».

Ваша сводная таблица теперь должна показывать каждый год в столбцах.

Как видите, теперь стало проще видеть общий объем продаж по месяцам, и вы можете легко сравнивать ежемесячные продажи за каждый год или наблюдать тенденции ежемесячных продаж за несколько лет.

В этом руководстве мы показали вам простой пример того, как легко сгруппировать данные по месяцам в сводной таблице в Google Таблицах. Мы надеемся, что это было полезно для вас.

Формат даты в таблице Excel

Excel позволяет создать свой (пользовательский) формат ячейки. Многие знают об этом, но очень редко пользуются из-за кажущейся сложности. Однако это достаточно просто, главное понять основной принцип задания формата.

Для того, чтобы создать пользовательский формат необходимо открыть диалоговое окно Формат ячеек и перейти на вкладку Число. Можно также воспользоваться сочетанием клавиш Ctrl + 1.

В поле Тип вводится пользовательские форматы, варианты написания которых мы рассмотрим далее.

В поле Тип вы можете задать формат значения ячейки следующей строкой:

[цвет]”любой текст”КодФормата”любой текст”

Посмотрите простые примеры использования форматирования. В столбце А – значение без форматирования, в столбце B – с использованием пользовательского формата (применяемый формат в столбце С)

Какие цвета можно применять

В квадратных скобках можно указывать один из 8 цветов на выбор:

Синий, зеленый, красный, фиолетовый, желтый, белый, черный и голубой.

Числовые форматы

Символ	Описание применения	Пример формата	До форматирования	После форматирования
#	Символ числа. Незначащие нули в начале или конце число не отображаются	######	001234	1234
0	Символ числа. Обязательное отображение незначащих нулей	000000	1234	001234
,	Используется в качестве разделителя целой и дробной части	####,#	1234,12	1234,1
пробел	Используется в качестве разделителя разрядов	# ###,#0	1234,1	1 234,10

Форматы даты

Формат	Описание применения	Пример отображения
М	Отображает числовое значение месяца	от 1 до 12
ММ	Отображает числовое значение месяца в формате 00	от 01 до 12
МММ	Отображает сокращенное до 3-х букв значение месяца	от Янв до Дек
ММММ	Полное наименование месяца	Январь – Декабрь
МММММ	Отображает первую букву месяца	от Я до Д
Д	Выводит число даты	от 1 до 31
ДД	Выводит число в формате 00	от 01 до 31
ДДД	Выводит день недели	от Пн до Вс
ДДДД	Выводит название недели целиком	Понедельник – Пятница
ГГ	Выводит последние 2 цифры года	от 00 до 99
ГГГГ	Выводит год даты полностью	1900 – 9999

Стоит обратить внимание, что форматы даты можно комбинировать между собой. Например, формат “ДД.ММ.ГГГГ” отформатирует дату в привычный нам вид 31.12.2017, а формат “ДД МММ” преобразует дату в вид 31 Дек.

Как вводить даты и время в Excel

Если иметь ввиду российские региональные настройки, то Excel позволяет вводить дату очень разными способами – и понимает их все:

С использованием дефисов

С использованием дроби

Внешний вид (отображение) даты в ячейке может быть очень разным (с годом или без, месяц числом или словом и т.д.) и задается через контекстное меню – правой кнопкой мыши по ячейке и далее Формат ячеек (Format Cells) :

Время вводится в ячейки с использованием двоеточия. Например

По желанию можно дополнительно уточнить количество секунд – вводя их также через двоеточие:

И, наконец, никто не запрещает указывать дату и время сразу вместе через пробел, то есть

27.10.2012 16:45

Быстрый ввод дат и времени

Для ввода сегодняшней даты в текущую ячейку можно воспользоваться сочетанием клавиш Ctrl + Ж (или CTRL+SHIFT+4 если у вас другой системный язык по умолчанию).

Если скопировать ячейку с датой (протянуть за правый нижний угол ячейки), удерживая правуюкнопку мыши, то можно выбрать – как именно копировать выделенную дату:

Если Вам часто приходится вводить различные даты в ячейки листа, то гораздо удобнее это делать с помощью всплывающего календаря :

Если нужно, чтобы в ячейке всегда была актуальная сегодняшняя дата – лучше воспользоваться функцией СЕГОДНЯ (TODAY) :

Как Excel на самом деле хранит и обрабатывает даты и время

Если выделить ячейку с датой и установить для нее Общий формат (правой кнопкой по ячейке Формат ячеек – вкладка Число – Общий), то можно увидеть интересную картинку:

То есть, с точки зрения Excel, 27.10.2012 15:42 = 41209,65417

На самом деле любую дату Excel хранит и обрабатывает именно так – как число с целой и дробной частью. Целая часть числа (41209) – это количество дней, прошедших с 1 января 1900 года (взято за точку отсчета) до текущей даты. А дробная часть (0,65417), соответственно, доля от суток (1сутки = 1,0)

Из всех этих фактов следуют два чисто практических вывода:

• Во-первых, Excel не умеет работать (без дополнительных настроек) с датами ранее 1 января 1900 года. Но это мы переживем!
• Во-вторых, с датами и временем в Excel возможно выполнять любые математические операции. Именно потому, что на самом деле они – числа! А вот это уже раскрывает перед пользователем массу возможностей.

Количество дней между двумя датами

Считается простым вычитанием – из конечной даты вычитаем начальную и переводим результат в Общий (General) числовой формат, чтобы показать разницу в днях:

Количество рабочих дней между двумя датами

Здесь ситуация чуть сложнее. Необходимо не учитывать субботы с воскресеньями и праздники. Для такого расчета лучше воспользоваться функцией ЧИСТРАБДНИ (NETWORKDAYS) из категории Дата и время. В качестве аргументов этой функции необходимо указать начальную и конечную даты и ячейки с датами выходных (государственных праздников, больничных дней, отпусков, отгулов и т.д.):

Примечание: Эта функция появилась в стандартном наборе функций Excel начиная с 2007 версии. В более древних версиях сначала необходимо подключить надстройку Пакета анализа. Для этого идем в меню Сервис – Надстройки (Tools – Add-Ins) и ставим галочку напротив Пакет анализа (Analisys Toolpak) . После этого в Мастере функций в категории Дата и время появится необходимая нам функция ЧИСТРАБДНИ (NETWORKDAYS) .

Функция ГОД в Excel

Возвращает год как целое число (от 1900 до 9999), который соответствует заданной дате. В структуре функции только один аргумент – дата в числовом формате. Аргумент должен быть введен посредством функции ДАТА или представлять результат вычисления других формул.

Пример использования функции ГОД:

Функция МЕСЯЦ в Excel: пример

Возвращает месяц как целое число (от 1 до 12) для заданной в числовом формате даты. Аргумент – дата месяца, который необходимо отобразить, в числовом формате. Даты в текстовом формате функция обрабатывает неправильно.

Примеры использования функции МЕСЯЦ:

ДЕНЬНЕД

Задача оператора ДЕНЬНЕД – выводить в указанную ячейку значение дня недели для заданной даты. Но формула выводит не текстовое название дня, а его порядковый номер. Причем точка отсчета первого дня недели задается в поле «Тип». Так, если задать в этом поле значение «1», то первым днем недели будет считаться воскресенье, если «2» — понедельник и т.д. Но это не обязательный аргумент, в случае, если поле не заполнено, то считается, что отсчет идет от воскресенья. Вторым аргументом является собственно дата в числовом формате, порядковый номер дня которой нужно установить. Синтаксис выглядит так:

“ВЫБОР”.

Теперь предположим, что вы хотите получить произвольное название месяца или имя на другом языке вместо числа или обычного имени.

В этой ситуации вам поможет функция ВЫБОР и уже пройденная нами функция МЕСЯЦ. Построим формулу. Для этого нам необходимо указать пользовательское имя для всех 12 месяцев в функции и использовать функцию месяца, чтобы получить номер месяца из даты.

Таким образом, когда функция месяца возвращает номер месяца от даты, функция выбора будет возвращать произвольное имя месяца вместо этого числа.

НОМНЕДЕЛИ

Предназначением оператора НОМНЕДЕЛИ является указание в заданной ячейке номера недели по вводной дате. Аргументами является собственно дата и тип возвращаемого значения. Если с первым аргументом все понятно, то второй требует дополнительного пояснения. Дело в том, что во многих странах Европы по стандартам ISO 8601 первой неделей года считается та неделя, на которую приходится первый четверг. Если вы хотите применить данную систему отсчета, то в поле типа нужно поставить цифру «2». Если же вам более по душе привычная система отсчета, где первой неделей года считается та, на которую приходится 1 января, то нужно поставить цифру «1» либо оставить поле незаполненным. Синтаксис у функции такой:

Формула условия для дат с функцией ДАТАЗНАЧ (DATEVALUE)

Иногда случается, что записать дату непосредственно в функцию ЕСЛИ, не ссылаясь ни на какую ячейку. В этом случае возникают некоторые сложности.

В отличие от многих других функций Excel, ЕСЛИ не может распознавать даты и интерпретирует их как текст, как простые текстовые строки.

Поэтому вы не можете выразить свое логическое условие просто как >«15.07.2019» или же >15.07.2019. Увы, ни один из приведенных вариантов не верен.

Чтобы функция ЕСЛИ распознала дату в вашем логическом условии именно как дату, вы должны обернуть ее в функцию ДАТАЗНАЧ (в английском варианте – DATEVALUE).

Полная формула ЕСЛИ может иметь следующую форму:

Как показано на скриншоте, эта формула ЕСЛИ оценивает даты в столбце В и возвращает «Послупил», если дата поступления до 10 сентября. В противном случае формула возвращает «Ожидается».

Расширенные формулы ЕСЛИ для будущих и прошлых дат

Предположим, вы хотите отметить только те даты, которые отстоят от текущей более чем на 30 дней.

Выделим даты, отстоящие более чем на месяц от текущей, в прошлом. Укажем для них «Более месяца назад». Запишем это условие:

Если условие не выполнено, то в ячейку запишем пустую строку “”.

А для будущих дат, также отстоящих более чем на месяц, укажем «Ожидается».

Если все результаты попробовать объединить в одном столбце, то придется составить выражение с несколькими вложенными функциями ЕСЛИ:

Перевод разных написаний дат

Разные системы в выгрузках выдают даты по-разному, например: 12.07.2016 12-07-16 16-07-12 и так далее. Иногда месяца пишут текстом. Для того, чтобы привести даты к одному формату мы используем функцию ДАТА:

Данный вариант подходит, когда количество символов в дате одинаковое. Если вы работаете с однотипными выгрузками еженедельно, создание дополнительного столбца и протягивание формулы будет простым решением.

Получение значения даты

С помощью формулы ЗНАЧ мы выводим текстовое значение даты, потом его форматируем как Дату:

Умножение текстового значения на единицу

Принцип аналогичный предыдущему, только мы текстовое значение умножаем на единицу и после этого форматируем как дату:

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

Синтаксис

=DATE(year, month, day) – английская версия

=ДАТА(год; месяц; день) – русская версия

Аргументы

• Year (Год) – значение года, которое важно отобразить в дате;
• Month (Месяц) – значение месяца, которое важно отобразить в дате;
• Day (День) – значение дня, которое важно отобразить в дате.

Вставка текущей даты и времени.

В Microsoft Excel вы можете сделать это в виде статического или динамического значения.

Как вставить сегодняшнюю дату как статическую отметку.

Для начала давайте определим, что такое отметка времени. Отметка времени фиксирует «статическую точку», которая не изменится с течением времени или при пересчете электронной таблицы. Она навсегда зафиксирует тот момент, когда ее записали.

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

• Ctrl + ; (в английской раскладке) или Ctrl+Shift+4 (в русской раскладке) вставляет сегодняшнюю дату в ячейку.
• Ctrl + Shift + ; (в английской раскладке) или Ctrl+Shift+6 (в русской раскладке) записывает текущее время.
• Чтобы вставить текущую дату и время, нажмите Ctrl + ; затем нажмите клавишу пробела, а затем Ctrl + Shift +;

Скажу прямо, не все бывает гладко с этими быстрыми клавишами. Но по моим наблюдениям, если при загрузке файла у вас на клавиатуре был включен английский, то срабатывают комбинации клавиш на английском – какой бы язык бы потом не переключили для работы. То же самое – с русским.

Как сделать, чтобы дата оставалась актуальной?

Если вы хотите вставить текущую дату, которая всегда будет оставаться актуальной, используйте одну из следующих функций:

• =СЕГОДНЯ()- вставляет сегодняшнюю дату.
• =ТДАТА()- использует текущие дату и время.

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

А если нужно вставить текущее время?

Здесь рекомендации зависят от того, что вы далее собираетесь с этим делать. Если нужно просто показать время в таблице, то достаточно функции ТДАТА() и затем установить для этой ячейки формат «Время».

Если же далее на основе этого вы планируете производить какие-то вычисления, то тогда, возможно, вам будет лучше использовать формулу

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

При использовании формул имейте в виду, что:

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

Как поставить неизменную отметку времени автоматически формулами?

Допустим, у вас есть список товаров в столбце A, и, как только один из них будет отправлен заказчику, вы вводите «Да» в колонке «Доставка», то есть в столбце B. Как только «Да» появится там, вы хотите автоматически зафиксировать в колонке С время, когда это произошло. И менять его уже не нужно.

Для этого мы попробуем использовать вложенную функцию ИЛИ с циклическими ссылками во второй ее части:

Где B – это колонка подтверждения доставки, а C2 – это ячейка, в которую вы вводите формулу и где в конечном итоге появится статичная отметка времени.

В приведенной выше формуле первая функция ЕСЛИ проверяет B2 на наличие слова «Да» (или любого другого текста, который вы решите ввести). И если указанный текст присутствует, она запускает вторую функцию ЕСЛИ. В противном случае возвращает пустое значение. Вторая ЕСЛИ – это циклическая формула, которая заставляет функцию ТДАТА() возвращать сегодняшний день и время, только если в C2 еще ничего не записано. А если там уже что-то есть, то ничего не изменится, сохранив таким образом все существующие метки.

О работе с функцией ЕСЛИ читайте более подробно здесь .

Если вместо проверки какого-либо конкретного слова вы хотите, чтобы временная метка появлялась, когда вы хоть что-нибудь пишете в указанную ячейку (это может быть любое число, текст или дата), то немного изменим первую функцию ЕСЛИ для проверки непустой ячейки:

Примечание. Чтобы эта формула работала, вы должны разрешить циклические вычисления на своем рабочем листе (вкладка Файл – параметры – Формулы – Включить интерактивные вычисления). Также имейте в виду, что в основном не рекомендуется делать так, чтобы ячейка ссылалась сама на себя, то есть создавать циклические ссылки. И если вы решите использовать это решение в своих таблицах, то это на ваш страх и риск.

Складывать и вычитать календарные дни

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

Например, вам необходимо создать резерв по сомнительным долгам в налоговом учете. В том числе нужно просчитать, когда у покупателя возникнет задолженность со сроком 45 дней после дня реализации. Для этого в одну ячейку внесите дату отгрузки. К примеру, это ячейка D2. Тогда формула будет выглядеть так: =D2+45. Вычитаются дни по аналогичному принципу. Главное, чтобы ячейка с датой, к которой будете прибавлять число, имела правильный формат. Чтобы это проверить, нажмите правой кнопкой мыши на ячейку, выберите «Формат ячеек» и удостоверьтесь, что установлен формат «Дата».

Как выглядит формат ячейки в Excel

Таким же образом можно посчитать и количество дней между двумя датами. Просто вычтите из более поздней даты более раннюю. Результат Excel покажет в виде числа, поэтому ячейку с итогом переведите в общий формат: вместо «Дата» выберите «Общий».

К примеру, необходимо посчитать, сколько календарных дней пройдет с 05.11.2019 по 31.12.2019. Для этого введите эти даты в разные ячейки, а в отдельной ячейке поставьте знак «=». Затем вычтите из декабрьской даты ноябрьскую. Получится 56 дней. Помните, что в этом случае в подсчет войдет последний день, но не войдет первый. Если вам необходимо, чтобы итог включал оба дня, прибавьте к формуле единицу. Если же, наоборот, нужно посчитать количество дней без учета обеих дат, то единицу необходимо вычесть.

Добавить к дате рабочие дни

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

• начальная дата – ставят ссылку на ячейку с датой, к которой функция будет прибавлять рабочие дни;
• число рабочих дней – ставят количество рабочих дней, которое необходимо прибавить к начальной дате;
• праздники (необязательный) – ставят ссылку на диапазон с датами праздников.

Например, директор дал вам поручение, которое необходимо выполнить за 25 рабочих дней. Допустим, сегодня вторник, 5 ноября 2019 года. Эту дату вносим в ячейку A1. Функция =РАБДЕНЬ(A1;25) определит крайний день, когда вы должны его выполнить, — 10 декабря 2019 года. При этом не забудьте поставить в ячейке с результатом формат «Дата».

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

Как прибавить (вычесть) несколько недель к дате

Когда требуется прибавить (вычесть) несколько недель к определенной дате, Вы можете воспользоваться теми же формулами, что и раньше. Просто нужно умножить количество недель на 7:

Прибавляем N недель к дате в Excel:

= A2 + N недель * 7

Например, чтобы прибавить 3 недели к дате в ячейке А2, используйте следующую формулу:

= А2 — N недель * 7

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

Как прибавить (отнять) годы к дате в Excel

Добавление лет к датам в Excel осуществляется так же, как добавление месяцев. Вам необходимо снова использовать функцию ДАТА (DATE), но на этот раз нужно указать количество лет, которые Вы хотите добавить:

На листе Excel, формулы могут выглядеть следующим образом:

Прибавляем 5 лет к дате, указанной в ячейке A2:

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

Как отличить обычные даты Excel от «текстовых дат»

Импортированные данные (или данные, введенные неправильно) могут выглядеть как обычные даты Excel, но они не ведут себя так, как выглядят. Microsoft Excel обрабатывает такие записи как текст. Поэтому вы не сможете правильно отсортировать таблицу в хронологическом порядке, а также использовать эти «неправильные даты» в формулах, сводных таблицах, диаграммах или любом другом инструменте Excel, который работает с временем.

Сначала давайте изучим несколько признаков, которые могут помочь определить, записана в ячейке датировка либо текст.

Даты

Текстовые значения

· Выровнено по правому краю.

· Указан формат даты в поле «Числовой формат» на вкладке «Главная » — «Число» .

· По левому краю по умолчанию.

· Общий формат отображается в поле «Число» на вкладке «Главная» — «Число».

·В строке формул может быть виден апостроф перед содержимым ячейки.

Их можно легко распознать, немного расширив столбцы, выделив один из них, вы­брав команду Формат ► Ячейки ► Выравнивание (Format ► Cells ► Alignment) и для па­раметра По горизонтали (Horizontal) выбрав значение Общий (General) (это вид ячеек по умолчанию). Щелкните кнопку ОК и внимательно просмотрите на таблицу. Если какие-либо значения не выровнены по правому краю, значит Excel не считает их датами.

Как конвертировать текст в дату в Excel

Когда возникает подобная проблема, скорее всего, вы захотите перевести эти текстовые значения в обычные даты Excel, чтобы вы могли ссылаться на них в формулах для выполнения различных вычислений. И, как это часто бывает в Экселе, есть несколько способов решения этой задачи.

Математические операции для преобразования текста в дату

Помимо использования функций Excel, о которых мы говорили чуть выше, вы можете выполнить простую математическую операцию, чтобы заставить программу выполнить реорганизацию строки в дату. Обязательное условие: операция не должна изменять ее значение (порядковый номер дня). Звучит немного сложно? Следующие примеры помогут разобраться!

Предполагая, что ваши данные находятся в ячейке A1, вы можете использовать любую из следующих формул, а затем применить формат даты к ячейке:

• Сложение: =A1 + 0
• Умножение: =A1 * 1
• Деление: =A1 / 1
• Двойное отрицание: =–A1

Как вы можете убедиться, математические операции могут помочь с датами (строки 3,4.,5,7), временем (строки 2 и 6), а также числами, отформатированными как текст (строка 8).

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

Как превратить текстовые строки с пользовательскими разделителями в даты

Если запись содержит какой-либо разделитель, отличный от косой черты (/) или тире (-), функции Excel не смогут распознать их как даты и вернут ошибку #ЗНАЧ!. Чаще всего такие «неправильные» разделители – это пробел и запятая.

Чтобы это исправить, вы можете запустить инструмент поиска и замены, чтобы заменить этот неподходящий разделитель, к примеру, косой чертой (/):

• Выберите все ячейки, которые вы хотите превратить в даты.
• Нажмите Ctrl + H, чтобы открыть диалоговое окно «Найти и заменить».
• Введите свой пользовательский разделитель (запятую, к примеру) в поле Найти и косую черту в Заменить.
• Нажмите Заменить все.

Теперь у ДАТАЗНАЧ или ЗНАЧЕН должно быть проблем с конвертацией текстовых строк в даты. Таким же образом вы можете исправить записи, содержащие любой другой разделитель, например, пробел или обратную косую черту.

Если вы предпочитаете решение на основе формул, вы можете использовать функцию ПОДСТАВИТЬ (SUBSTITUTE в английской версии), как это мы делали на одном из скриншотов ранее:

И текстовые строки преобразуются в даты, все при помощи одной формулы.

Как видите, функции ДАТАЗНАЧ или ЗНАЧЕН довольно мощные, но они, к сожалению, имеют свои ограничения. Например, если вы пытаетесь работать со сложными конструкциями, такими как четверг, 01 января 2015 г., ни одна из них не сможет помочь.

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

Исправление записей с двузначными годами.

Современные версии Microsoft Excel достаточно умны, чтобы обнаружить некоторые очевидные ошибки в ваших данных, или, точнее, сказать, что Эксель считает ошибкой. Когда это произойдет, вы увидите индикатор ошибки (маленький зеленый треугольник) в верхнем левом углу клетки, и, когда вы выделите её, появится восклицательный знак.
При нажатии на восклицательный знак отобразятся несколько параметров, относящихся к вашим данным. В случае двухзначного года программа спросит, хотите ли вы преобразовать его в 19XX или 20XX.

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

Как изменить форматы даты в Excel

Одна приятная особенность Microsoft Excel состоит в том, что обычно существует несколько способов выполнения многих популярных функций. Это особенно верно с форматами даты. Независимо от того, импортировали ли вы данные из другой электронной таблицы или базы данных или просто вводите даты оплаты для своих ежемесячных счетов, Excel может легко отформатировать большинство стилей дат. Читайте дальше, чтобы узнать, как изменить формат даты в Excel.

Инструкции в этой статье относятся к Excel 2019, 2016 и 2013.

Как изменить формат даты Excel с помощью функции «Формат ячеек»

С помощью множества меню Excel вы можете изменить формат даты в несколько кликов.

Выберите вкладку « Главная ».

В группе ячеек, выберите формат в раскрывающемся меню, затем выберите Формат ячейки .

На вкладке «Число» в диалоговом окне «Формат ячеек» выберите « Дата» .

Как видите, в поле «Тип» есть несколько вариантов форматирования.

Вы также можете просмотреть раскрывающийся список «Местонахождение» и выбрать формат, наиболее подходящий для страны, для которой вы пишете.

Выбрав формат, нажмите « ОК», чтобы изменить формат даты выбранной ячейки в электронной таблице Excel.

Создайте свой собственный формат Excel с датой

Если вы не можете найти нужный формат, выберите « Пользовательский» в поле «Категория», чтобы отформатировать дату так, как вы хотите. Ниже приведены некоторые сокращения, которые вам понадобятся для создания индивидуального формата даты .

На вкладке «Число» в диалоговом окне «Формат ячеек» выберите « Пользовательский» . Как и в категории «Дата», есть несколько вариантов форматирования.

Выбрав формат, нажмите кнопку «ОК», чтобы изменить формат даты для выбранной ячейки в электронной таблице Excel.

Как отформатировать ячейки с помощью мыши

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

Выберите ячейки, содержащие даты, формат которых вы хотите изменить.

Щелкните правой кнопкой мыши выделение и выберите « Формат ячеек» . Либо нажмите Ctrl + 1, чтобы открыть диалоговое окно «Формат ячеек».

Либо выберите « Домой» > « Номер» , нажмите стрелку и выберите « Формат номера» в правом нижнем углу группы. Или в группе « Число » вы можете выбрать раскрывающийся список, а затем выбрать « Дополнительные форматы номеров» .

Выберите « Дата» или, если вам нужен более индивидуальный формат, выберите « Пользовательский» .

В поле «Тип» выберите параметр, который наилучшим образом соответствует вашим потребностям форматирования. Это может занять немного проб и ошибок, чтобы получить правильное форматирование.

Выберите OK, когда вы выбрали формат даты.

Независимо от того, используется ли категория «Дата» или «Пользовательский», если вы видите один из типов со звездочкой ( * ), этот формат будет меняться в зависимости от выбранной локали (местоположения).

Использование Quick Apply для длинных или коротких дат

Если вам нужно быстрое изменение формата с короткой даты (мм / дд / гггг) или длинной даты (дддд, мммм дд, гггг или понедельник, 1 января 2019 г.), есть быстрый способ изменить это на ленте Excel ,

Выберите ячейки, которые вы хотите изменить формат даты.

Выберите Дом .

В группе «Число» выберите раскрывающееся меню, затем выберите « Короткая дата» или « Длинная дата» .

Использование формулы TEXT для форматирования дат

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

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

## — это метка ячейки, а сокращения форматов — это те, которые перечислены выше в разделе « Пользовательский ». Например, = TEXT (A2, «мм / дд / гггг») отображается как 01.01.1900.

Использование поиска и замены для форматирования дат

Этот метод лучше всего использовать, если вам нужно изменить формат с тире (-), косой черты (/) или периодов (.) Для разделения месяца, дня и года. Это особенно удобно, если вам нужно изменить большое количество дат.

Выберите ячейки, для которых нужно изменить формат даты.

Выберите « Домой» > « Найти и выбрать» > « Заменить» .

В поле «Найти» введите свой оригинальный разделитель даты (тире, косая черта или точка).

В поле «Заменить на» введите то, что вы хотите изменить в качестве разделителя формата (тире, косая черта или точка).

Затем выберите один из следующих вариантов:

• Заменить все : который заменит все первые записи поля и заменит их на ваш выбор из поля Заменить на .
• Заменить : Заменяет только первый экземпляр.
• Найти все : только поиск всех исходных записей в поле « Найти» .
• Find Next : только находит следующий экземпляр из вашей записи в поле Find what .

Использование текста в столбцах для преобразования в формат даты

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

Выберите ячейки, для которых вы хотите изменить формат даты.

Убедитесь, что они отформатированы как текст. (Нажмите Ctrl + 1, чтобы проверить их формат).

Выберите вкладку « Данные ».

В группе «Инструменты данных» выберите « Текст в столбцы» .

Выберите «С разделителями» или « Фиксированная ширина» , затем нажмите « Далее» .

Большую часть времени следует выбирать с разделителями, так как длина даты может колебаться.

Снимите флажки со всех разделителей и выберите Далее .

В области « Формат данных столбца» выберите « Дата» , выберите формат строки даты в раскрывающемся меню, затем нажмите « Готово» .

Использование проверки ошибок для изменения формата даты

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

Это проверка ошибок в Excel, указывающая на проблему. Из-за параметра «Проверка ошибок» Excel определит возможную проблему с двузначными форматами года. Чтобы использовать проверку ошибок для изменения формата даты, сделайте следующее:

Выберите одну из ячеек, содержащих индикатор. Вы должны заметить восклицательный знак с выпадающим меню рядом с ним.

Выберите раскрывающееся меню и выберите « Преобразовать ХХ в 19ХХ» или « Преобразовать ХХ в 20ХХ» , в зависимости от года.

Вы должны увидеть, что дата немедленно изменится на четырехзначное число.

Использование быстрого анализа для доступа к ячейкам форматирования

Быстрый анализ может использоваться не только для форматирования цвета и стиля ваших ячеек. Вы также можете использовать его для доступа к диалоговому окну «Формат ячеек».

Выберите несколько ячеек, содержащих даты, которые нужно изменить.

Выберите Быстрый анализ в правом нижнем углу вашего выбора, или нажмите Ctrl + Q .

В разделе «Форматирование» выберите « Содержащий текст» .

Используя правое выпадающее меню, выберите Custom Format .

Выберите вкладку Number , затем выберите либо Date, либо Custom .