Есть Excel таблица, в первой колонке — даты за месяц, красным выделяю два выходных. Даты заносятся прошлого месяца, т.е. сейчас в июне нужны майские даты. Я предложил заносить в первую строчку 01-05-2018 и тянуть мышку вниз, вроде бы проще нет ничего. Дальше бухгалтер заносит какие-то свои данные и я по их формулам делаю расчет, но формулы чувствительны к дню недели. Догадались? Да! забыла она поменять апрель на май, набила очень много и начальник уже подписал, а потом ошибка обнаружилась. Нет проблемы из now вытаскивать месяц, но проблема, как стереть лишний день(дни), если 30 или 28 дней? Я хочу закрыть эту колонку от изменений lock, хотя … если не думать, то можно что угодно.
P.S. Предлагал сделать страничку на сайте, я бы генерировал календарь и все дела, не хотят. Боятся, что хакеры украдут.
Даты — важная часть данных. Бывают случаи, когда нам нужно использовать только часть даты. Возьмем пример месяца. Иногда вам нужен только месяц от даты.
Месяц — один из полезных компонентов даты, который вы можете использовать для суммирования данных и когда они появятся. в Excel у нас есть разные методы для получения месяца с даты.
Я нашел для этого всего 5 методов. И сегодня в этом посте я хотел бы поделиться с вами всеми этими методами получения/извлечения месяца из даты. Итак, приступим к делу.
Мнение эксперта
Витальева Анжела, консультант по работе с офисными программами
Со всеми вопросами обращайтесь ко мне!
Задать вопрос эксперту
Если в качестве списка значений указать названия месяцев по очередности , а в качестве индекса ячейку с номером месяца, то функция вернет соответствующее ему название для каждого заданного числа. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
Единственная причина, по которой данные в ячейке могут отображаться как дата, заключается в том, что они отформатированы соответствующим образом. Следовательно, чтобы настроить отображение необходимых ему данных, пользователь должен их изменить.
Excel название месяца по дате — Все о Windows 10
- Повторное использование чего угодно: добавьте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное, и быстро использовать их в будущем.
- Более 20 текстовых функций: извлечение числа из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
- Инструменты слияния: несколько книг и листов в одну; Объединить несколько ячеек/строк/столбцов без потери данных; Объедините повторяющиеся строки и суммируйте.
- Инструменты разделения: разделение данных на несколько листов в зависимости от значения; Из одной книги в несколько файлов Excel, PDF или CSV; Один столбец в несколько столбцов.
- Вставить пропуск скрытых/отфильтрованных строк; Подсчет и сумма по цвету фона; Массовая рассылка персонализированных писем нескольким получателям.
- Суперфильтр: создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделе, дню, частоте и т. Д. Фильтр жирным шрифтом, формулами, комментариями …
- Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.
Если мы попытаемся изменить числовой формат поля День/Дата, это не сработает. Ничего не меняется, когда мы заходим в Настройки поля> Числовой формат и меняем числовой формат на пользовательский или формат даты.
Запись с помощью настройки формата ячейки
В Excel существует достаточно большое количество форматов отображения даты.
Давайте запишем дату в произвольную ячейку и перейдем в ее формат (щелкаем по ячейке правой кнопкой мыши и выбираем Формат ячейки, или просто нажимаем сочетание клавиш Ctrl + 1).
В открывшемся окне нас интересует вкладка Число:
Среди форматов дат выбираем запись месяца прописью и получаем:
Можно выбрать запись как со звездочкой (символ «*»), так и без, при этом различие отображения будет заключаться в изменении вида записи при смене настроек времени и даты операционной системы.
Идем дальше и перейдем к формульному решению задачи.
Мнение эксперта
Витальева Анжела, консультант по работе с офисными программами
Со всеми вопросами обращайтесь ко мне!
Задать вопрос эксперту
Единственная причина, по которой данные в ячейке могут отображаться как дата, заключается в том, что они отформатированы соответствующим образом. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
Для тех, кто просто хочет скачать календарь в Excel на текущий год, на картинке ниже показан как он выглядит. Красным отмечены праздничные дни, розовым — выходные. Текущий день, также автоматически выделяется заливкой.
Включение, отключение и настройка автозамены в Excel. Как отключить Автозамену на дату в Excel?
Возможно, вы могли бы разделить этот макрос на два макроса и запускать только первый цикл сброса по мере необходимости. Макрос занимает около 15 секунд для запуска на моем компьютере из-за всех циклов. Но это не тот, который вам придется часто бегать.
Третий способ самый эффективный и наиболее автоматизированный — это использование меню надстройки «Power Query».
Правда нужно отметить, что этот способ подходит только пользователям Excel 2016 и пользователям Excel 2013и выше с установленной надстройкой «Power Query».
Смысл способа в следующем:
Необходимо открыть вкладку «Power Query». В разделе «Данные Excel» нажимаем кнопку (пиктограмму) «Из таблицы».
Из таблицы -Power Query
Далее нужно выбрать диапазон ячеек, из которых нужно «притянуть» информацию и нажимаем «Ок».
Источник данных для запроса Power Query
После выбора области данных появится окно настройки вида новой таблицы. В этом окне Вы можете настроить последовательность вывода столбцов и удалить ненужные столбцы.
После настройки вида таблицы нажмите кнопку «Закрыть и загрузить»
Обновление полученной таблицы происходит кликом правой кнопки мыши по названию нужного запроса в правой части листа (список «Запросы книги»). После клика правой кнопкой мыши в выпадающем контекстном меню следует нажать на пункт «Обновить»
Обновление запроса в PowerQuery
Мнение эксперта
Витальева Анжела, консультант по работе с офисными программами
Со всеми вопросами обращайтесь ко мне!
Задать вопрос эксперту
Время от времени возникает необходимость изменить порядковый номер месяца на название и наоборот например, 3 на Март или Октябрь на 10. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
Бывает, что введя формулу и нажав клавишу ENTER, пользователь видит в ячейке не результат вычисления формулы, а саму формулу. Причина: формат текста ячейки. … Заставить EXCEL интерпретировать выражение, введенное в ячейку, как формулу, а не как текст, очень просто: нажмите F2, затем ENTER) .
Автоматический перенос данных из одной таблицы в другую в Excel
Если мы попытаемся изменить числовой формат поля День/Дата, это не сработает. Ничего не меняется, когда мы заходим в Настройки поля> Числовой формат и меняем числовой формат на пользовательский или формат даты.
Как преобразовать название месяца в порядковый номер
Теперь будем преобразовывать обратно – имя месяца в соответствующий ему порядковый номер в году.
Значение Декабрь1 Excel автоматически преобразует в дату дек.01. Далее функция ДАТАЗНАЧ преобразует дату в число после с помощью функции МЕСЯЦ мы получаем порядковый номер в году для текущего названия месяца. По сути формулу можно упростить:
Наиболее оптимальным вариантом является лаконичная формула =МЕСЯЦ(A2&”1″). В ней не нужно резервировать названия месяцев и присваивать им соответственные порядковые номера. К тому же в ней используется всего лишь одна функция.
В этой статье описаны синтаксис формулы и использование функции МЕСЯЦ в Microsoft Excel.
Мнение эксперта
Витальева Анжела, консультант по работе с офисными программами
Со всеми вопросами обращайтесь ко мне!
Задать вопрос эксперту
Повторное использование быстро вставляйте сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки паролем; Создайте список рассылки и отправьте электронные письма. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
Если мы попытаемся изменить числовой формат поля День/Дата, это не сработает. Ничего не меняется, когда мы заходим в Настройки поля> Числовой формат и меняем числовой формат на пользовательский или формат даты.
Как форматировать даты в Excel для сводных таблиц
Создадим новый лист Excel (Праздники) и добавим на него умную таблицу со столбцом Праздничные дни. В этот столбец занесем все праздничные дни. Я это уже сделал, можете спокойно копировать и использовать.
Найдем дату, отстоящую от начальной даты на заданное количество месяцев.
Пусть в ячейке
А6
задана дата
05.10.2012
. Дата, отстоящая от нее на 2 месяца, очевидно,
05.12.2012
. Этот результат можно получить формулой
=ДАТАМЕС(A6;2))
Альтернативная формула
=ДАТА(ГОД(A6);МЕСЯЦ(A6)+2;ДЕНЬ(A6))
может дать в некоторых (редких) случаях другой результат. Например, если к дате
29.02.2008
(високосный год) прибавить 12 месяцев, то функция
ДАТАМЕС()
вернет
28.02.2009
, а альтернативная формула
01.03.2009.
Первый вариант представляется правильным. В статье о функции
ДАТАМЕС()
приведен еще один пример расхождения результатов расчетов (см.
Функция ДАТАМЕС() в MS EXCEL
).
Примечание
: Функция
ДАТАМЕС()
—
это функция из надстройки «Пакет анализа», который по умолчанию включен в MS EXCEL 2007 (в отличие от более ранних версий EXCEL).
Конец месяца
Если необходимо к последнему дню месяца прибавить 1 месяц таким образом, чтобы новая дата была также последним днем месяца, то нужно использовать формулу
=КОНМЕСЯЦА(A6;1)
Таблица с датами отстоящими на месяц (формулы)
Для составления таблицы, состоящей из последовательных дат отстоящих на 1 месяц можно воспользоваться формулой
=ДАТАМЕС($B$18;СТРОКА()-СТРОКА($A$20))
и
таблицей в формате Excel2007
(не обязательно), см.
файл примера
.
Также в файле примера приведены формулы для
последовательностей дат
— последних дней месяцев, а также последних дней кварталов.
Таблица с датами отстоящими на месяц (Прогрессия)
Заполнить таблицу датами, отстоящими на 1, 2 или 3, … месяцев можно также с помощью инструмента
Прогрессия
. Для этого введите начальную дату, выделите необходимый диапазон таким образом, чтобы активной ячейкой была ячейка с начальной датой и вызовите инструмент
Прогрессия
.
При создании таблиц и анализе данных в Excel, иногда требуется рассчитывать значения в разных временных интервалах. Для этого потребуется примерить функции Excel, которые позволяют осуществить сдвиг дат на необходимое количество дней, месяцев, лет.
Сдвиг даты на N дней в будущее или прошлое
Поскольку одни сутки в системе отсчета даты Excel принимаются за единицу, то для вычисления даты, отстоящей от заданной на, допустим, 20 дней, достаточно прибавить к дате это число.
Сдвиг даты на N дней в будущее или прошлое
Сдвиг даты на N рабочих (банковских) дней
Эту операцию осуществляет функция РАБДЕНЬ (WORKDAY). Она позволяет вычислить дату, отстоящую вперед или назад относительно начальной даты на нужное количество рабочих дней (с учетом выходных, суббот и воскресений, и государственных праздников). Использование этой функции полностью аналогично применению функции ЧИСТРАБДНИ (NETWORKDAYS), описанной здесь.
Сдвиг даты на N рабочих (банковских) дней
Сдвиг даты на N месяцев
Допустим, нужно вычислить дату окончания 10-месячного контракта, подписанного 22 марта 2016 г. Для этого можно воспользоваться стандартной функцией ДАТАМЕС (EMONTH) из категории Дата и время.
Сдвиг даты на N месяцев
Сдвиг даты на N лет
Готовой функции для этого в Excel нет, но если необходимо сдвинуть имеющуюся дату на заданное количество лет, то можно использовать несложную формулу.
Сдвиг даты на N лет
Сначала мы разбираем дату на составляющие при помощи функций ДЕНЬ, МЕСЯЦ и ГОД, а потом собираем ее обратно функцией ДАТА, но предварительно добавляем к году нужное число. В английской версии это будет соответственно: =DATE(YEAR(A1)+B1;MONTH(A1);DAY(A1)).
В таблицах Excel предусмотрена возможность работы с различными видами текстовой и числовой информации. Доступна и обработка дат. При этом может возникнуть потребность вычленения из общего значения конкретного числа, например, года. Для этого существует отдельные функции: ГОД, МЕСЯЦ, ДЕНЬ и ДЕНЬНЕД.
Примеры использования функций для обработки даты в Excel
Таблицы Excel хранят даты, которые представлены в качестве последовательности числовых значений. Начинается она с 1 января 1900 года. Этой дате будет соответствовать число 1. При этом 1 января 2009 года заложено в таблицах, как число 39813. Именно такое количество дней между двумя обозначенными датами.
Функция ГОД используется аналогично смежным:
- МЕСЯЦ;
- ДЕНЬ;
- ДЕНЬНЕД.
Все они отображают числовые значения, соответствующие григорианскому календарю. Даже если в таблице Excel для отображения введенной даты был выбран календарь Хиджра, то при вычленении года и других составных значений посредством функций, приложение представит число, которое является эквивалентом по григорианской системе летоисчисления.
Чтобы воспользоваться функцией ГОД, нужно ввести в ячейку следующую формулу функции с одним аргументом:
=ГОД(адрес ячейки с датой в числовом формате)
Аргумент функции является обязательным для заполнения. Он может быть заменен на «дата_в_числовом_формате». В примерах ниже, вы сможете наглядно увидеть это. Важно помнить, что при отображении даты в качестве текста (автоматическая ориентация по левому краю ячейки), функция ГОД не будет выполнена. Ее результатом станет отображение #ЗНАЧ. Поэтому форматируемые даты должны быть представлены в числовом варианте. Дни, месяцы и год могут быть разделены точкой, слешем или запятой.
Рассмотрим пример работы с функцией ГОД в Excel. Если нам нужно получить год из исходной даты нам не поможет функция ПРАВСИМВ так как она не работает с датами, а только лишь текстовыми и числовыми значениями. Чтобы отделить год, месяц или день от полной даты для этого в Excel предусмотрены функции для работы с датами.
Пример: Есть таблица с перечнем дат и в каждой из них необходимо отделить значение только года.
Введем исходные данные в Excel.
Для решения поставленной задачи, необходимо в ячейки столбца B ввести формулу:
=ГОД (адрес ячейки, из даты которой нужно вычленить значение года)
В результате мы извлекаем года из каждой даты.
Аналогичный пример работы функции МЕСЯЦ в Excel:
Пример работы c функциями ДЕНЬ и ДЕНЬНЕД. Функция ДЕНЬ получает вычислить из даты число любого дня:
Функция ДЕНЬНЕД возвращает номер дня недели (1-понедельник, 2-второник… и т.д.) для любой даты:
Во втором опциональном аргументе функции ДЕНЬНЕД следует указать число 2 для нашего формата отсчета дня недели (с понедельника-1 по восркесенье-7):
Если пропустить второй необязательный для заполнения аргумент, тогда будет использоваться формат по умолчанию (английский с воскресенья-1 по суботу-7).
Создадим формулу из комбинаций функций ИНДЕКС и ДЕНЬНЕД:
Получим более понятный вид реализации данной функции.
Примеры практического применения функций для работы с датами
Эти примитивные функции очень полезны при группировки данных по: годам, месяцам, дням недели и конкретным дням.
Допустим у нас имеется простой отчет по продажам:
Нам нужно быстро организовать данные для визуального анализа без использования сводных таблиц. Для этого приведем отчет в таблицу где можно удобно и быстро группировать данные по годам месяцам и дням недели:
Теперь у нас есть инструмент для работы с этим отчетом по продажам. Мы можем фильтровать и сегментировать данные по определенным критериям времени:
Кроме того, можно составить гистограмму для анализа самых продаваемых дней недели, понять на какой день недели приходится наибольшее количество продаж:
В таком виде очень удобно сегментировать отчеты по продажам за длительные, средние и короткие периоды времени.
Стоит сразу отметить что для того чтобы получить разницу между двумя датами нам не поможет ни одна из выше описанных функций. Для данной задачи следует воспользоваться специально предназначенной функцией РАЗНДАТ:
Скачать примеры функций для работы с датами в Excel
Тип значений в ячейках «дата» требует особого подхода при обработке данных. Поэтому следует использовать соответствующие данному типу функции в Excel.