Меню

Выделить цветом когда дата меньше текущей



Как подсветить сроки и даты в ячейках в Excel?

Добрый день уважаемый пользователь Microsoft Excel!

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

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

Давайте рассмотрим на практике два способа применения подсветки с помощью условного форматирования:

Подсветка дат и сроков с помощью правил выделения ячеек

Это самый простой способ, который возможно осуществить с помощью нескольких кликов. Для начала необходимо выделить нужный диапазон с вашими датами, на панели управления во вкладке «Главная», выбрать выпадающее меню «Условное форматирование», выбрать в списке «Правила выделения ячеек», далее выбрать тип «Дата…». Следующим шагом вы в диалоговом окне «Дата» в выпадающем списке указываем условия выделения, например, выбрать все даты «На текущей неделе» и выберем для них красный цвет.

Подсветка дат в ячейках с помощью условного форматирования и элементов управления форм

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

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

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

Для начала выделим созданную таблицу и на вкладке «Главная» выбираем в выпадающем списке «Условное форматирование» пункт «Создать правило». В открывшемся диалоговом окне выбираете правило «Использовать формулу для определения форматируемых ячеек» и вводим формулу: =$D5 ИСТИНА. Формат указывать не надо, так как таблица должна выглядеть обыкновенной, нажимаем кнопочку «ОК» добавляем новое правило в общий список. В случае если новое правило не возглавило список, то необходимо стрелочками его туда пропихнуть и установить флажок в столбике «Остановить если истина». Указание последнего параметра необходимо для того, чтобы, когда ваше правило выполнялось (выключен флажок «Включить индикатор строк»), Excel автоматически отключал все нижестоящие правила условного форматирования и таблица остается, девственно чиста от заливок, чего, собственно вы и добивались!

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

Не забудьте подкинуть автору на кофе…

Источник

Подсветка дат и сроков

Простой способ

Выделите диапазон с датами на листе и выберите на вкладке Главная – Условное форматирование – Правила выделения ячеек – Дата ( Home – Conditional Formatting – Highlight Cell Rules – Date Occuring) . В открывшемся окне выберите из выпадающего списка нужный вариант подсветки:

Сложный, но красивый способ

Теперь давайте разберем задачку посложнее и поинтереснее. Предположим, что у нас есть большая таблица поставок каких-либо товаров:

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

Для наглядности, можно настроить три правила условного форматирования, чтобы автоматически заливать всю строку с данными по партии в разные цвета в зависимости от даты отгрузки. Для этого выделим всю таблицу (без шапки) и выберем на вкладке Главная – Условное форматирование – Создать правило (Home – Conditional Formatting – Create Rule) . В открывшемся окне зададим последний тип правила Использовать формулу для определения форматируемых ячеек (Use formula to determine which cell to format) и введем в поле следующую формулу:

Эта формула берет последовательно содержимое ячеек E5, E6, E7… из столбца с датой отгрузки и сравнивает эту дату с сегодняшней датой из ячейки C2. Если дата отгрузки раньше, чем сегодняшняя, то отгрузка уже была. Обратите внимание на знаки доллара, используемые для закрепления ссылок. Ссылка на $C$2 должна быть абсолютной – с двумя знаками доллара. Ссылка на первую ячейку столбца с датой отгрузки должна быть с закреплением только столбца, но не строки, т.е. $E5.

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

Вместо текущей даты можно вставить в ячейку С2 функцию СЕГОДНЯ (TODAY) , которая будет обновлять дату каждый раз при открытии файла, что будет приводить к автоматическому обновлению цветов в таблице.

Если подобная подсветка нужна не всегда, а только на определенное время работы с таблицей, то можно добавить к уже сделанному еще и, своего рода, выключатель. Для этого откройте вкладку Разработчик (Developer) . Если ее не видно, то сначала включите ее через Файл – Параметры – Настроить ленту и нажмите кнопку Вставить (Insert) :

В открывшемся списке инструментов выберите Флажок (Checkbox) из верхнего набора Элементы управления формы и щелкните по тому месту листа, где хотите его разместить. Затем можно задать размеры надписи и поменять ее текст (правой кнопкой мыши – Изменить текст):

Теперь, чтобы использовать флажок для включения-выключения подсветки, нужно связать его с любой ячейкой на листе. Щелкните правой кнопкой мыши по нарисованному флажку и выберите в контекстном меню команду Формат объекта (Format Object) и затем в открывшемся окне задайте любую подходящую ячейку в поле Связь с ячейкой (Cell Link) :

Проверьте как все работает. В связанную ячейку Е2 должно выводиться значение ИСТИНА, когда флажок включен или ЛОЖЬ, когда он выключен.

Теперь осталось добавить одно правило в условное форматирование, чтобы наш флажок включал-выключал подсветку дат. Выделите всю нашу таблицу (кроме шапки) и откройте на вкладке Главная — Условное форматирование — Управление правилами (Home — Conditional Formatting — Manage Rules) . В открывшемся окне должны быть хорошо видны созданные нами ранее правила для подсветки прошлых, будущих и настоящих дат разными цветами:

Жмем кнопку Создать правило (New Rule) , выбираем последний тип правила Использовать формулу для определения форматируемых ячеек (Use formula to determine which cell to format) и вводим в поле следующую формулу:

Формат не задаем и нажимаем ОК. Созданное правило должно добавится к общему списку. Теперь необходимо поднять его на первую строчку стрелками (если оно не еще не там) и включить напротив него справа флажок Остановить если истина (Stop If True) :

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

Источник

Пример формулы для выделения цветом просроченных дат в Excel

Во многих фирмах отдельное внимание уделяется датам, выпадающим после определенного пройденного периода. С помощью условного форматирования можно легко составить отчет «После периода» на котором выделены пройденные даты.

Как сделать подсвечивание цветом ячеек с датами пройденного срока в Excel

Пример представлен ниже на рисунке в виде отчета, в котором даты за более чем 90 дней от текущей даты выделенные другим цветом заливки.

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

  1. Выделите целевой диапазон ячеек (в данном примере A3:A8) и выберите инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Создать правило». В результате чего появится окно для внесения всех необходимых настроек инструмента:
  2. В появившемся окне из верхней части где находится список опций выберите пункт: «Использовать формулу для определения форматируемых ячеек». Данная опция позволяет нам использовать собственные формулы для составления сложны правил условного форматирования. Формула должна содержать логическое выражение и соответственно возвращать логическое значение для каждой ячейки из выделенного диапазона. Если будет возвращено – ИНСТИНА, тогда к этой ячейке будет применятся правило и присваивается новый формат, который предварительно настроен этим же инструментом.
  3. В полю ввода формул введите логическое выражение представленное на этом шаге. Данная формула проверяет значение ячеек: будет ли их дата выпадать после 90 дней, пройденных от сегодняшнего дня. Отсчитывается от даты, указанной в целевой ячейке A3 выделенного просматриваемого диапазона. Если да (ИСТИНА) – сразу же применяется условное форматирование.
Читайте также:  Под что подбирается цвет галстука

  • Нажмите на кнопку «Формат» для вызова окна, в которому будут доступные все опции оформления формата: цвет фона и границы, размер шрифта и т.п. После указания желаемых настроек для оформления стиля форматирования нажмите кнопку ОК на всех открытых окнах, чтобы подтвердить все настройки и получить готовый результат.
  • А в результате выделились все даты актуальность которых превышает 90 дней.

    Источник

    Как в excel сделать дату красным когда она наступает?

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

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

    Давайте рассмотрим на практике два способа применения подсветки с помощью условного форматирования:

    1. С помощью правил выделения ячеек;
    2. С помощью правил условного форматирования и элементов управления форм.

    Подсветка дат и сроков с помощью правил выделения ячеек

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

    Для начала необходимо выделить нужный диапазон с вашими датами, на панели управления во вкладке «Главная», выбрать выпадающее меню «Условное форматирование», выбрать в списке «Правила выделения ячеек», далее выбрать тип «Дата…».

    Следующим шагом вы в диалоговом окне «Дата» в выпадающем списке указываем условия выделения, например, выбрать все даты «На текущей неделе» и выберем для них красный цвет.

    Подсветка дат в ячейках с помощью условного форматирования и элементов управления форм

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

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

    Для начала выделим созданную таблицу и на вкладке «Главная» выбираем в выпадающем списке «Условное форматирование» пункт «Создать правило».

    В открывшемся диалоговом окне выбираете правило «Использовать формулу для определения форматируемых ячеек» и вводим формулу: =$D5Мне надо создать таблицу с фильтрацией и выделением ячеек, в которых наступает событие по дате и высвечивалось оповещение в ячейке сколько дней осталось до наступления данного события.

    • Если можно сделать, чтобы при наведении на ячейку высвечивался календарь и выделялись дни до наступления события(для примера день рождения)
    • Для наглядности прикрепил таблицу с данными.
    • Поточнее выражу мысль. В столбце -Дата рождения-надо, чтобы выделялись ячейки например красным цветом или мигали при приближении к дате рождения в этом году, а в столбце -Срок наступления события в этом году- высвечивался календарь с днями до наступления дня рождения в этом году
    • Сообщение отредактировал Andreyuser: 31 Январь 2016 — 18:25

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

    Для наглядности возьмем совсем несложный пример. Предположим, в некоторой таблице отмечается дата регистрации заказа и дата, когда он должен быть выполнен. За сутки до даты выполнения необходимо перезвонить клиенту. Какие варианты «напоминалок» можно использовать?

    Во-первых, можно таблицу раскрасить. Например, строки, относящиеся к заказу, по поводу которого пора звонить, должны окрашиваться красным. Строки заказов, срок исполнения которых отстоит от сегодняшней даты более чем на 1 день, должны быть окрашены голубым. Строки заказов, срок исполнения которых уже прошел, вообще не окрашиваются. Последовательность действий для реализации этой «задумки»:

    • выделите столбцы таблицы (можно целиком, чтобы не ограничивать количество строк с данными);
    • в меню команды Условное форматирование (вкладка Главная) выберите строку Управление правилами;
    • создайте правило в виде формул: «=$C2-СЕГОДНЯ()>1» (формат: заливка голубым) и «=$C2-СЕГОДНЯ()=1» (формат: заливка красным);
    • примените созданные правила.

    Обратите внимание, что ссылка на ячейку «$С2» смешанная (запрет установлен на изменение номера столбца С).

    Использование функции «СЕГОДНЯ()» позволит автоматизировать форматирование, так как при каждом открытии файла будет проверяться текущая дата и строки в таблице будут менять цвет заливки.

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

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

    • вставьте дополнительный столбец, например, перед таблицей;
    • в первую ячейку столбца проверки вставьте формулу: «=ЕСЛИ(D2-СЕГОДНЯ()=1;»Позвонить»;» «)«;

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

    После того, как всем позвонили, снимите фильтр со столбца Напоминание и продолжайте ввод данных о новых заказах.

    В-третьих, можно формировать список звонков с помощью расширенного фильтра.

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

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

    Самый простой и быстрый способ ввести в ячейку текущую дату или время – это нажать комбинацию горячих клавиш CTRL+«;» (текущая дата) и CTRL+SHIFT+«;» (текущее время).

    Гораздо эффективнее использовать функцию СЕГОДНЯ(). Ведь она не только устанавливает, но и автоматически обновляет значение ячейки каждый день без участия пользователя.

    Как поставить текущую дату в Excel

    Чтобы вставить текущую дату в Excel воспользуйтесь функцией СЕГОДНЯ(). Для этого выберите инструмент «Формулы»-«Дата и время»-«СЕГОДНЯ». Данная функция не имеет аргументов, поэтому вы можете просто ввести в ячейку: «=СЕГОДНЯ()» и нажать ВВОД.

    1. Текущая дата в ячейке:
    2. Если же необходимо чтобы в ячейке автоматически обновлялось значение не только текущей даты, но и времени тогда лучше использовать функцию «=ТДАТА()».
    3. Текущая дата и время в ячейке.

    Как установить текущую дату в Excel на колонтитулах

    Вставка текущей даты в Excel реализуется несколькими способами:

    1. Задав параметры колонтитулов. Преимущество данного способа в том, что текущая дата и время проставляются сразу на все страницы одновременно.
    2. Используя функцию СЕГОДНЯ().
    3. Используя комбинацию горячих клавиш CTRL+; – для установки текущей даты и CTRL+SHIFT+; – для установки текущего времени. Недостаток – в данном способе не будет автоматически обновляться значение ячейки на текущие показатели, при открытии документа. Но в некоторых случаях данных недостаток является преимуществом.
    4. С помощью VBA макросов используя в коде программы функции: Date();Time();Now().

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

    Чтобы сделать текущую дату в Excel и нумерацию страниц с помощью колонтитулов сделайте так:

    1. Откройте окно «Параметры страницы» и выберите закладку «Колонтитулы».
    2. Нажмите на кнопку создать нижний колонтитул.
    3. В появившемся окне щелкните по полю «В центре:». На панели выберите вторую кнопку ««Вставить номер страницы»». Потом выберите первую кнопку «Формат текста» и задайте формат для отображения номеров страниц (например, полужирный шрифт, а размер шрифта 14 пунктов).
    4. Для установки текущей даты и времени щелкните по полю «Справа:», а затем щелкните по кнопке «Вставить дату» (при необходимости щелкните на кнопку «Вставить время»). И нажмите ОК на обоих диалоговых окнах. В данных полях можно вводить свой текст.
    5. Нажмите на кнопку ОК и обратите на предварительный результат отображения колонтитула. Ниже выпадающего списка «Нижний колонтитул».
    6. Для предварительного просмотра колонтитулов перейдите в меню «Вид»-«Разметка страницы». Там же можно их редактировать.

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

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

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

    Каждый раз при вводе даты в ячейку возникают вопросы: печатать ДД-ММ-ГГГГ или в другом порядке? Использовать «-» или «/» в качестве разделителя? Распознает ли Excel введённое значение как дату и отобразит ли её в нужном формате? Именно в этих случаях пригодится всплывающий, или выпадающий, календарь. Он незаменим для работы с личными и рабочими списками дел, расписаниями и планами, графиками выполнения проектов и финансовыми отчётами в Excel.

    «Всплывающий календарь» — это надёжный и проверенный инструмент выбора дат, которому доверяют 30 000+ пользователей Excel:

    • Интуитивен в использовании: выберите ячейки — вставьте дату
    • Выпадает рядом с ячейкой, отображает номера недель, дни недели, текущую дату
    • Заполняет датами столбцы, строки и любые диапазоны в секунды
    • Множество вариантов формата даты по умолчанию
    • Работает в 64- и 32-разрядных версиях Excel

    Язык видео: английский. Субтитры: русский, английский. (Внимание: видео может не отражать последние обновления. Используйте инструкцию ниже.)

    Читайте также:  Как восстановить цвет черного трикотажа

    Добавить «Всплывающий календарь» в Excel 2019, 2016, 2013, 2010

    Подходит для: Microsoft Excel 2019 – 2010, desktop Office 365 (32-бит и 64-бит).

    Скачать надстройку XLTools

    Как работать с надстройкой:

    Внимание: отмена последнего действия (UNDO) невозможна. Рекомендуем сохранять резервную копию файлов или использовать «Контроль версий» XLTools для отслеживания изменений.

    1. Выберите ячейку.
    2. В группе Дата/Время нажмите на кнопку «Вставить дату» > Календарь всплывёт рядом с ячейкой.
      Или: по правому клику мыши выберите пункт «Вставить дату».

    Или: используйте сочетание клавиш Ctrl+; (точка с запятой на английской раскладке).

  • Выберите нужную дату из календаря > Готово.
    Обратите внимание на формат: это то, что вам нужно? Вы можете изменить формат даты по умолчанию.
  • Чтобы изменить значение даты, нажмите на иконку календаря справа от ячейки > Измените дату.
  • «Всплывающий календарь» запоминает последнюю использованную дату, чтобы вам было проще вводить следующую дату.

    Внимание: после ввода даты, ячейке автоматически присваивается формат Число/Дата. Если вы позднее удалите значение из ячейки, сама ячейка сохранит свой формат Дата, а иконка календаря для этой пустой ячейки останется доступной. Чтобы убрать иконку, измените формат ячейки на Общий.

    1. Выберите ячейку, в которую нужно вставить текущую дату.
    2. В группе Дата/Время нажмите на кнопку «Вставить дату» > Календарь появится рядом с ячейкой.
      Или: по правому клику мыши выберите пункт «Вставить дату».

    Или: используйте сочетание клавиш Ctrl+; (точка с запятой на английской раскладке).

  • Нажмите по надписи «Сегодня» в нижней части календаря > Готово.
  • С помощью «Всплывающего календаря» вы можете заполнить значениями дат целый диапазон (ограничение до 4000 ячеек). Даты можно вставить в столбцы таблицы, строки, объединённые ячейки или любой произвольный диапазон точно так же, как одну ячейку:

    1. Выберите диапазон.
    2. В группе Дата/Время нажмите на кнопку «Вставить дату» > Появится всплывающий календарь.
      Или: по правому клику мыши выберите пункт «Вставить дату».

    Или: используйте сочетание клавиш Ctrl+; (точка с запятой на английской раскладке).

  • Выберите дату > Готово. Весь диапазон заполнен значением выбранной даты.
    Совет: узнайте, как заполнить ячейки последовательными датами.
  • Чтобы изменить значение даты, нажмите на иконку календаря справа от ячейки > Измените дату.
  • Внимание: после ввода даты, ячейкам автоматически присваивается формат Число/Дата. Если вы позднее удалите значения из ячеек, сами ячейки сохранят свой формат Дата, а иконка календаря для этих пустых ячеек останется доступной. Чтобы убрать иконку, измените формат ячеек на Общий.

    С надстройкой «Всплывающий календарь» вы можете быстро заполнить диапазон последовательными датами (дни по порядку):

    1. Вкладка XLTools > Кнопка «Настройки» > Вкладка Дата/Время > Отметьте флажком «Включить автоувеличение дат при заполнении диапазона ячеек» > OK.
    2. Выберите диапазон.
    3. В группе Дата/Время нажмите на кнопку «Вставить дату» > Всплывающий календарь появятся рядом с ячейкой.
      Или: по правому клику мыши выберите пункт «Вставить дату».

    Или: используйте сочетание клавиш Ctrl+; (точка с запятой на английской раскладке).

  • Выберите начальную дату > Готово, весь диапазон будет автоматически заполнен последовательными значениями дат с шагом в 1 день.
    1. Выберите ячейку.
    2. В группе Дата/Время нажмите на кнопку «Вставить дату и время» > Всплывающий календарь с часами появятся рядом с ячейкой.
      Или: по правому клику мыши выберите пункт «Вставить дату и время».

    Или: используйте сочетание клавиш: нажмите Ctrl+; (точка с запятой на английской раскладке), затем отпустите клавиши и нажмите Ctrl+Shift+; (точка с запятой на английской раскладке).

  • Установите время при помощи колеса прокрутки мыши или стрелок Вверх/Вниз > Выберите дату из всплывающего календаря > Готово.
    Обратите внимание на формат: это то, что вам нужно? Вы можете задать другой формат по умолчанию для Всплывающего Календаря и Часов.
  • Чтобы изменить значение, нажмите на иконку справа от ячейки > Измените дату и время.
  • Внимание: после ввода даты и времени, ячейке автоматически присваивается формат Число/Дата. Если вы позднее удалите значение из ячейки, сама ячейка сохранит свой формат Дата, а иконка календаря с часами для этой пустой ячейки останется доступной. Чтобы убрать иконку, измените формат ячейки на Общий.

    Как вставить только время (без даты) в ячейку

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

    1. Выберите ячейку или диапазон.
    2. В группе Дата/Время нажмите на кнопку «Вставить время» > Всплывающие часы появятся рядом с ячейкой.
      Или: по правому клику мыши выберите пункт «Вставить время».

    Или: используйте сочетание клавиш Ctrl+Shift+; (точка с запятой на английской раскладке).

  • Установите время при помощи колеса прокрутки мыши или стрелок Вверх/Вниз > Нажмите Ввод (Enter) > Готово.
  • Чтобы изменить формат, заданный по умолчанию для «Всплывающего Календаря»:

    1. Нажмите кнопку «Настройки» на панели XLTools > Вкладка Дата/Время.
    2. Отметьте флажком «По умолчанию использовать формат даты и времени, установленный ниже».
      Внимание: при снятии флажка Всплывающий календарь будет использовать формат, установленный в ваших системных настройках.
    3. Выберите подходящий формат, напр., принятый в вашей стране или на вашем языке.
      Или: задайте «Свой формат».
    4. Нажмите OK > Готово.

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

    1. Выберите ячейки > По правому клику мыши выберите «Формат ячеек».
    2. Вкладка «Число» > Формат «Дата» > Установите нужный формат даты.

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

    1. Панель управления > Часы, язык и регион > Изменение форматов даты.
    2. Вкладка Форматы > Установите форматы дат и времени, первого дня недели.
    3. Вкладка Местоположение > Укажите страну местонахождения.
    4. Вкладка Дополнительно > Измените язык системы > Перезагрузите компьютер.

    Для вашего удобства, для «Всплывающего календаря» по умолчанию задано сочетание клавиш Ctrl+; (точка с запятой на английской раскладке) – это та же комбинация, которая используется в Excel для вставки текущей даты. Вы можете изменить горячие клавиши для «Всплывающих часов» в любое время:

    Вкладка XLTools > Кнопка «Настройки» > Вкладка «Горячие клавиши» > Задайте другое сочетание > OK.

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

    • Чтобы отключить отображение иконки Календаря:
    1. Нажмите кнопку «Настройки» на панели XLTools > Вкладка Дата/Время.
    2. Снимите флажок с опции «Отображать пиктограммы Всплывающего Календаря и Часов» > OK.
    3. Готово! Пиктограммы перестанут отображаться, Календарь и Часы будут по-прежнему доступны для использования. Иконки можно вернуть в любое время.

    • Чтобы настроить появление иконки рядом с пустыми ячейками, столбцами, строками или произвольным диапазоном:
    1. Выберите пустую ячейку или диапазон.
    2. Вкладка «Главная» > Группа «Число» > Дата > OK. Так, ячейкам будет присвоен формат даты.
      Или: вставьте любую дату с помощью «Всплывающего календаря» > Нажмите кнопку Delete. Это удалит непосредственно значения, и присвоит выбранным ячейкам формат даты.
    3. Готово! Теперь, если вы выберите какую-либо ячейку в этом диапазоне, рядом с ней появится иконка Календаря.
    • Чтобы убрать иконку Календаря с пустых ячеек:
      Если ячейка пуста, но иконка по-прежнему появляется, значит, что ячейке присвоен формат даты или времени. Чтобы убрать иконку с пустых ячеек, измените их формат на Общий:
    1. Выберите пустую ячейку или диапазон.
    2. Вкладка «Главная» > Группа «Редактирование» > Очистить форматы.
      Или: вкладка «Главная» > Группа «Число» > Выберите «Общий» из выпадающего списка.

      Или: по правому клику «Формат ячеек» > Вкладка «Число» > Выберите формат «Общий» > OK.

    1. Нажмите кнопку «Настройки» на панели XLTools > Вкладка Дата/Время.
    2. Установите или снимите флажки соответствующих опций:

      • «Добавить пункт «Вставить дату» в контекстное меню»
      • «Добавить пункт «Вставить время» в контекстное меню»
      • «Добавить пункт «Вставить дату и время» в контекстное меню»
    3. Нажмите OK > Готово.

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

    Если вам нужно отправить книгу другим пользователям, попробуйте наш встраиваемый календарь — онлайн надстройка XLTools.net Календарь. Установка на компьютер не требуется: отправьте файл коллегам, и они тоже смогут воспользоваться календарём. Это веб-приложение удобно для рассылки расписаний, графиков, планов, т.д.

    Появились вопросы или предложения? Оставьте комментарий ниже.

    Информирование пользователя MS EXCEL о принадлежности ДАТЫ к определенному диапазону

    Используем Условное форматирование для подачи сигнала пользователю MS EXCEL о принадлежности даты к определенному диапазону.

    Предположим, что пользователь вводит некие даты событий. Требуется, чтобы EXCEL автоматически выделял ячейки следующим образом:

    • красным, если дата совпадает с сегодняшним днем;
    • оранжевым, если вводимая дата находится в пределах 1 недели от сегодняшнего числа;
    • зеленым, если вводимая дата находится в пределах 2 недель от сегодняшнего числа.

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

    • выделите диапазон, в который пользователь будет вводить данные;
    • вызовите инструмент Условное форматирование (). Откроется окно Диспетчер правил условного форматирования;
    • нажмите Создать правило;
    • выберите Форматировать только ячейки, которые содержат;
    • в выпадающем списке выберите Равно;
    • введите формулу =СЕГОДНЯ();
    • нажав кнопку Формат выберите, например, красный шрифт;

    • нажмите ОК и вернитесь в Диспетчер правил условного форматирования;

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

    • нажмите Создать правило;
    • выберите Форматировать только ячейки, которые содержат;
    • в выпадающем списке выберите Между;
    • введите формулы =СЕГОДНЯ()-7 и =СЕГОДНЯ()+7;
    • нажав кнопку Формат выберите, например, оранжевый шрифт;

    Проделайте аналогичные шаги для создания правила, по которому ячейка будет выделяться если дата находится в пределах двух недель от сегодняшнего числа, используйте формулы =СЕГОДНЯ()-14 и =СЕГОДНЯ()+14.

    ВНИМАНИЕ! Когда к диапазону ячеек применяются два или более правил Условного форматирования, приоритет обработки определяется порядком их перечисления в Диспетчере правил условного форматирования.

    Правило, расположенное в списке выше, имеет более высокий приоритет, чем правило, расположенное в списке ниже.

    Читайте также:  Дизайн ногтей голубого цвета с белым

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

    Если мы расположим правила как показано на предыдущем рисунке, то при вводе сегодняшней даты она выделится зеленым цветом, а не красным, т.к. правило (Между =СЕГОДНЯ()-14 и =СЕГОДНЯ()+14) у нас идет первым и имеет наивысший приоритет. Для правильного отображения поменяем порядок критериев, используя соответствующие стрелочки.

    В результате получим вот такую картину.

    СОВЕТ: Чтобы найти все ячейки на листе, к которым применены правила Условного форматирования необходимо:

    • на вкладке Главная в группе Редактирование щелкните стрелку рядом с командой Найти и выделить;
    • выберите в списке пункт Условное форматирование;
    • будут выделены все ячейки, которым применены правила Условного форматирования.

    Как использовать функцию DATE (ДАТА) в Excel

    Функция DATE (ДАТА) возвращает числовое значение в формате даты, в виде конкретного дня, месяца и года.

    Когда использовать функцию

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

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

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

    Какие данные отображает формула

    Значение формулы хранится в Excel в числовом формате. Например значение “42370” при использовании этой формулы вернёт значение “Январь 2016”. Таким образом, формула преобразует числовое значение в привычный формат даты.

    Синтаксис

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

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

    Аргументы

    • Year (Год) – значение года, которое важно отобразить в дате;
    • Month (Месяц) – значение месяца, которое важно отобразить в дате;
    • Day (День) – значение дня, которое важно отобразить в дате.
    • то, как отображаются значения формулы в ячейке зависит от того, в каком формате ячейка. Например:
      • Если ячейка имеет формат как General, то значение даты будет отображаться в привычном формате даты (день-месяц-год). Из текста выше вы помните что значение даты, обрабатываемое в Excel, хранится в числовом формате.
      • Чтобы получить значение даты в числовом формате, надо сменить формат ячейки с датой на Number.
    • Рекомендую использовать 4-х значное число при указании года, для того чтобы избежать проблем с отображением даты. Если значение года меньше 1900, система автоматически добавит к значению 1900.

      Например, если при указании года, вы введёте значение “10”, то Excel добавит к этому числу 1900 и отобразит год как “1910”.
      Значение месяца может быть меньше 0 и не больше 12. Если вы укажете значение месяца более чем 12, то система автоматически округлит значение месяца кратное 12 в год и прибавит его к значению года.

      Например, если функция =DATE(2016,16,3) или =ДАТА(2016;16;3), то она отобразит дату как “3 Апреля 2017 года”. Если функция равна =DATE(2016,-2,1) или =ДАТА(2016;-2;1), то отразит дату как “1 Октября 2015 года”.
      Значение дня может быть как отрицательным, так и положительным.

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

    Пример 1: Получение числового значения даты, с использованием данных Года, Месяца, Дня

    • Вы можете получить числовое значение даты с указанием Года, Месяца, Дня.
    • В приведенном выше примере, функция DATE (ДАТА) использует три аргумента: значение “2016” в качестве значения года, значение “4” в качестве месяца и “1” в качестве значения дня.
    • Функция возвращает “42461”, когда ячейка отформатирована как “General” (Общие), и возвращает “4/1/16”, когда ячейка отформатирована как ‘Date’.
    • Обратите внимание, что Excel хранит дату или время как числовое значение.

    Формат даты может меняться в зависимости от региональных настроек. Например, формат даты в России ДД-ММ-ГГГГ, в то время как в Великобритании это ММ-ДД-ГГГГ.

    Если в вашей таблице есть данные Года, Месяца и Дня в разных ячейках, то вы можете использовать их с помощью формулы, как это показано на примере ниже:

    Пример 2: Использование отрицательных значений в функции DATE (ДАТА)

    Вы можете использовать отрицательные значения при указании Месяца и Дня в ячейке. При использовании отрицательных значений при указании месяца, скажем, “-1”, Excel отнимет 1 месяц от Года.

    Например, если вы используете формулу =DATE(2017,-1,1) или =ДАТА(2017;-1;1), она вернет дату 1 ноября 2016 года. Вместе с тем, вы можете использовать любое отрицательное число в значении Месяца.

    Та же логика работает, когда вы используете значение месяца более чем “12”. В этом случае, Excel прибавляет к значению Года столько месяцев, на сколько исходное значение Месяца больше 12.

    Например, если вы используете формулу =DATE(2016,14,1) или =ДАТА(2016;14;1), она вернет дату 1 февраля 2017 года.

    Примечание: Формат даты указанный в приведенных выше примерах, основан на настройках моей системы. Она может варьироваться в зависимости от настроек вашей системы.

    Кроме того, вы можете также использовать отрицательные значения Дня, или те что больше, чем “31” – функция DATE (ДАТА) будет корректировать дату по логике описанной выше.

    Как функция ДЕНЬНЕД определяет конкретные даты в Excel — Трюки и приемы в Microsoft Excel

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

    Определение дня в году

    1 января — это первый день года, а 31 декабря — последний. А как насчет остальных дней, идущих между ними? Следующая формула возвращает день года для даты, хранящемся в ячейке A1: =A1-ДАТА(ГОД(A1);1;0). Например, если ячейка A1 содержит дату 16 февраля 2010 года, формула возвращает 47, потому что эта дата является 47-м днем в году.

    Следующая формула возвращает количество дней, оставшихся в году с момента определенной даты (предполагается, что она содержится в ячейке A1): =ДАТА(ГОД(A1);12;31).

    Определение дня недели

    Если вам необходимо определить день недели для даты, функция ДЕНЬНЕД справится с этой задачей. Функция принимает в качестве аргумента дату и возвращает целое число от 1 до 7, соответствующее дню недели. Следующая формула, например, возвращает 6, потому что первый день 2010 года приходится на пятницу: =ДЕНЬНЕД(ДАТА(2010;1;1)).

    Функция ДЕНЬНЕД использует еще и необязательный второй аргумент, обозначающий систему нумерации дней для результата. Если вы укажете 2 в качестве второго аргумента, то функция вернет 1 для понедельника, 2 — для вторника и т. д. Если же вы укажете 3 в качестве второго аргумента, то функция вернет 0 для понедельника, 1 — для вторника и т. д.

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

    Определение даты последнего воскресенья

    Формула в этом разделе возвращает последний указанный день. Вы можете использовать следующую формулу для получения даты прошлого воскресенья. Если текущий день — воскресенье, то формула возвращает текущую дату. Результатом будет серийный номер даты (вам нужно отформатировать ячейку для отображения читабельной даты): =СЕГОДНЯ()-ОСТАТ(СЕГОДНЯ()-1;7).

    Чтобы изменить эту формулу для нахождения даты другого дня, а не воскресенья, поменяйте 1 на другое число в диапазоне от 2 (понедельник) до 7 (суббота).

    Определение дня недели после даты

    Следующая формула возвращает указанный день недели, который наступает после определенной даты. Например, вы можете применять эту формулу для определения даты первой пятницы после 4 июля 2010 года. Формула предполагает, что ячейка А1 содержит дату, а ячейка А2 — число от 1 до 7 (1 соответствует воскресенью, 2 — понедельнику и т. д.): =A1+A2-ДЕНЬНЕД(A1)+(А2

    преобразовать дату в текст excel — Bing

    результаты: 3 120 000Дата Язык Регион

    Рассмотрим, как преобразовать дату в текст Excel. Нам нужно, чтобы дата была написана в текстовом формате. Сначала вспомним, как записаны и как хранятся даты в Excel.

    Здесь рассмотрим, как преобразовать в Excel текст в дату и дату в текст Excel. Если мы скопировали или получили в «наследство» таблицу, в которой даты написаны текстом, мы не сможем отсортировать …

    Преобразовать дату в текст Excel. Рассмотрим, как преобразовать дату в текст Excel . Нам нужно, чтобы дата была написана в текстовом формате. Сначала…

    Вам когда-нибудь понадобилось преобразовать дату в число или текст в Excel? Эта статья может дать вам одолжение для этой работы.

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

    Главная » Текст » Как в excel преобразовать текст в дату Преобразование дат из текстового формата в формат даты Смотрите также KuklP он их не надо вводить как вниз )) —— сделал 61 текста? чтобы не справиться с маем.

    в окно аргументов. щелкните средство запуска Excel даты хранятся как листе. дат в текстовом измените ширину столбцов, -365, 30,5- неправильно: преобразовать количество дней датой.

    Преобразовать число в дату Excel. Смотрите также Проверить можно легко, числа для представления : Я не математик, наносекунд) Sub Conv() …

    При составлении формул для отображения в ячейке фразы содержащей текст и дату, например, «Сегодня 02.10.10», можно получить вот такой результат: «Сегодня 40453», т.е. дата будет отражена в

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

    • РекламаВыполняем проекты в Excel на заказ. Коллектив специалистов. · круглосуточно
  • Источник