Как преобразовать текст в дату в excel или извлечь ее из сложной фразы?

Открываем программу Microsoft Excel

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

• Нажать на значок Excel на рабочем столе, если программа была установлена на компьютер.
• Выбрать Excel из меню «Пуск» в системе Windows.
• Открыть файл Excel, с которым вы уже работали.

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

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

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

Функция ГОД возвращает год из четырех цифр, соответствующий указанной дате, в виде целого числа от 1900 до 9999.

Синтаксис функции ГОД в Excel максимально прост:

ГОД(серийный_номер)

Где серийный_номер — любая действительная дата года, которую вы хотите найти. В формулах Excel YEAR вы можете указывать даты несколькими способами:

• Использование ДАТЫ Например, следующая формула возвращает год для 28 апреля 2015 года:=ГОД(ДАТА(2015,4,28))
• Как порядковый номер, представляющий дату (дополнительную информацию о том, как даты хранятся в Excel, см. в разделе Формат даты Excel). 28 апреля 2015 года хранится как 42122, поэтому вы можете ввести это число непосредственно в формулу:=ГОД(42122)

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

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

Простые формулы ГОД могут даже понимать даты, введенные в виде текста, например =ГОД(“28 апреля-2015”). Однако корпорация Майкрософт не гарантирует правильных результатов, если дата предоставляется в виде текстового значения.

На следующем снимке экрана показаны все вышеперечисленные формулы ГОД в действии, и все они возвращают 2015 год, как и следовало ожидать

Примеры использования функции DATE (ДАТА) в Excel

Пример 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 (ДАТА) будет корректировать дату по логике описанной выше.

Больше лайфхаков в нашем ВК

Расчет времени

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

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

Данный инструмент позволяет определить количество дней, укладываемых между двумя датами. К примеру, 20 октября 2017 года вам задали проект, который необходимо закончить к 18 января 2018 года. Идти к календарю и считать время не слишком удобно и проще воспользоваться калькулятором: достаточно выбрать тип программы и вбить обе даты. В ответе видим, что на выполнение плана у вас есть 2 месяца и 29 дней. Не слишком информативно при планировании. Программа выражает это время так же в днях, неделях или месяцах. Смотрим. У вас есть ровно 90 дней или 12 рабочих недель. С этим уже можно построить эффективную систему тайм-менеджмента и не допустить дедлайна.

Какая дата будет через n дней

Еще один удобный инструмент для эффективной работы. Крупный проект на работе могут поручить с пометкой «выполнить в течение 50 дней после принятия заказа». Это большое количество времени, но вновь бежать к календарю и высчитывать его не слишком удобно. Используем калькулятор. Допустим, заказ был принят в работу 28 апреля 2017 года. До какого дня его требуется сдать заказчику? Поменяем тип калькулятора и вычислим дату дедлайна. Это будет 17 июня 2017, суббота. Имея под рукой общее количество дней и дату икс, вы можете легко распределить силы для своевременного выполнения работы.

Какая дата была n дней назад

Данный калькулятор не пригодится вам в работе, но наверняка придет на помощь в личной жизни. Представьте, что вы получили смс-сообщение, в котором ваша пассия поздравляет вас с 100 днем совместной жизни. Это важная дата, которую забывать не стоит, поэтому лучше воспользоваться программой и узнать ее. Вы получили смску 4 июля 2017 года, теперь легко узнать, когда же вы съехались со своей пассией. Итак, выбираем тип калькулятора, вводим дату и юбилейные 100 дней. Ваша памятная дата – 26 марта 2017, воскресенье. Стоит обвести эту дату в календаре.

Временные величины

Данный калькулятор позволяет перевести одни временные величины в другие. При помощи программы можно выразить минуты в дни, недели в года или века в тысячелетия. На практике это может пригодиться при расчете рабочих часов для фрилансеров и свободных художников. Например, у вас есть 28 рабочих дней на выполнение очередного заказа. Это 672 часа. Отнимем время на сон 28 × 8 = 224, время на перерывы и отдых 28 × 4 = 112 и получим, что у вас есть 336 часов на эффективную работу. С этим уже можно работать и использовать техники тайм-менеджмента для продуктивного труда.

Сумма/разница во времени

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

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

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

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

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

Пример 1. Преобразование простых текстовых строк в даты.

В этом примере мы будем превращать текстовые строки, например 01 01 2015 (день, месяц и год разделены пробелами), в даты.

1. На рабочем листе выберите столбец, содержимое которого вы хотите конвертировать.
2. Перейдите на вкладку «Данные», и нажмите «Текст по столбцам».

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

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

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

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

Пример 2. Преобразование сложных текстовых строк в даты.

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

• 8 март 2020 г., воскресенье
• 8 март 2020 г. 15:00

Вам нужно будет приложить немного больше усилий и использовать рассматриваемый нами инструмент в комбинации с функцией ДАТА.

Важно! Название месяца при использовании этого метода должно быть в именительном падеже!

1. Выберите нужную область данных.
2. Вызовите мастер преобразования.
3. На шаге 1 укажите «С разделителями» и нажмите « Далее» .
4. На шаге 2 мастера выберите разделители, которые у вас используются.

Например, если вы преобразуете строки, разделенные запятыми и пробелами, например «8 март 2020г., воскресенье», то вам следует выбрать оба разделителя — запятую и пробел.

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

Я немного усложнил наш пример, добавив после порядкового номера года еще букву «г» с точкой. Поэтому эта буква и добавлена в список разделителей.

И, наконец, взгляните на окно Образец разбора данных и проверьте, правильно ли поделены ваши слова на столбцы. Затем нажмите «Далее».

1. На шаге 3 мастера убедитесь, что все колонки в разделе предварительного просмотра данных имеют формат «Общий». Если это не так, щелкните столбец и выберите Общий в разделе Формат данных.

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

Если вам не нужен какой-то из столбцов, щелкните по нему и выберите «Пропустить

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

Если вы не хотите перезаписывать исходные данные, укажите, где именно должны быть вставлены результаты — введите адрес верхней левой ячейки в поле «Поместить в…». Выбираем ячейку B2.

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

На следующем рисунке показан результат с исходными данными в A и разделенными на части данными в В, C и D.

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

В нашем случае год записан в колонке D и день — в В, с этим проблем нет.

Но не так просто с месяцем, потому что это слово, а нужен его номер. К счастью, Microsoft Excel предоставляет специальную функцию МЕСЯЦ (MONTH), которая может изменить название месяца на его порядковый номер:

Чтобы функция МЕСЯЦ понимала, что она имеет дело с датой, мы выражаем это так:

Здесь C2 содержит название месяца, март в нашем случае. «1 &» добавляется для получения даты (1 март), чтобы формула могла превратить ее в соответствующий номер месяца. Повторюсь еще раз, что месяц должен быть здесь в именительном падеже, что не всегда удобно.

А теперь давайте встроим это выражение в качестве второго аргумента:

И вуаля, наши сложные комбинации слов и цифр успешно становятся датами:

Обновление даты в Excel при планировании сроков работ

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

Первый способ – использование функции “Заменить”. Для этого нужно выделить ячейку, содержащую дату, затем нажать на клавишу “Ctrl+H” или выбрать пункт меню “Правка” – “Заменить”. В появившемся окне в поле “Найти” ввести текущий год, а в поле “Заменить на” – новый год. После этого нужно нажать кнопку “Заменить все”. Таким образом, все вхождения текущего года в дате будут заменены на новый год.

Второй способ – использование функции “Формат ячейки”. Для этого нужно выделить ячейку, содержащую дату, затем нажать правой кнопкой мыши и выбрать пункт меню “Формат ячейки”. В открывшемся окне выбрать раздел “Число” и в списке выбрать формат “Длинная дата”. После этого нажать кнопку “ОК”. Таким образом, дата будет отображаться с полным названием месяца и годом.

Третий способ – использование формулы. Для этого можно использовать функции “ГОД”, “МЕСЯЦ” и “ДЕНЬ”. Например, чтобы изменить год в дате, можно использовать следующую формулу: =ДАТА(ГОД(A1)+1, МЕСЯЦ(A1), ДЕНЬ(A1)). В данной формуле A1 – это ячейка, содержащая исходную дату. При вводе этой формулы в другую ячейку, будет отображена новая дата с увеличенным годом на один.

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

Производственный календарь

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

1. Создаете заглавие при помощи сочетания функций ГОД и СЕГОДНЯ, которые возвращают текущий год.

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

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

1. Задействуйте еще одну формулу ДАТАМЕС, которая добавляет указанное число месяцев к текущей дате. Аналогичные действия производите для остальных заготовок.

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

Важно! Для применения формулы нажимаете Ctrl+Shift+Enter. В этом случае программа автоматически посчитает значения для выделенного диапазона

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

1. Копируете диапазон в остальные заготовки.

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

1. В поле Применяется к выделяете диапазоны каждого месяца, удерживая клавишу Ctrl.

1. Чтобы обозначить текущую дату, задаете еще одно правило форматирования с применением функции СЕГОДНЯ для всего рабочего листа.

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

1. Создаете правило форматирования и применяете ко всем месяцам.

На заметку! При изменении таблицы с праздниками таблица с числами автоматически отформатируется.

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

Суммирование времени

Для того, чтобы произвести процедуру суммирования времени, прежде всего, все ячейки, которые принимают участие в данной операции, должны иметь формат времени. Если это не так, то их нужно соответствующим образом отформатировать. Текущий формат ячеек можно посмотреть после их выделения во вкладке «Главная» в специальном поле форматирования на ленте в блоке инструментов «Число».

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

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

Способ 1: показание часов через промежуток времени

Прежде всего, давайте посмотрим, как рассчитать, сколько будут показывать часы по прошествии определенного периода времени, выраженного в часах, минутах и секундах. В нашем конкретном примере нужно узнать, сколько будет на часах через 1 час 45 минут и 51 секунду, если сейчас на них установлено время 13:26:06.

1. На отформатированном участке листа в разных ячейках с помощью клавиатуры вводим данные «13:26:06» и «1:45:51».

В третьей ячейке, в которой тоже установлен формат времени, ставим знак «=». Далее кликаем по ячейке со временем «13:26:06», жмем на знак «+» на клавиатуре и кликаем по ячейке со значением «1:45:51».

Для того чтобы результат вычисления вывести на экран, жмем на кнопку «Enter».

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

Способ 2: использование функции

Альтернативным вариантом предыдущему способу является использование функции СУММ.

1. После того, как первичные данные (текущее показание часов и отрезок времени) введены, выделяем отдельную ячейку. Кликаем по кнопке «Вставить функцию».

Открывается Мастер функций. Ищем в перечне элементов функцию «СУММ». Выделяем её и жмем на кнопку «OK».

Запускается окно аргументов функции. Устанавливаем курсор в поле «Число1» и кликаем по ячейке, содержащей текущее время. Затем устанавливаем курсор в поле «Число2» и кликаем по ячейке, где указано время, которое нужно прибавить. После того, как оба поля заполнены, жмем на кнопку «OK».

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

Способ 3: суммарное сложение времени

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

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

Далее нужно выделить диапазон, заполненный значением времени и одну пустую ячейку после него. Находясь на вкладке «Главная», нажимаем на значок «Сумма», расположенный на ленте в блоке инструментов «Редактирование». Как альтернативный вариант, можно набрать на клавиатуре сочетание клавиш «Alt+=».

После этих действий в пустой выделенной ячейке появится результат вычислений.

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

Как преобразовать текст в дату при помощи формул.

Давайте рассмотрим другой способ превратить сложную формулировку в дату. Столбец A содержит даты в виде текстовых выражений формы «08 марта 2020». Сразу отметим, что записи должны быть единообразными, то есть все числа хранятся в двузначном формате: 01, 07, 08, 10, 12 и так далее

Мы используем формулы для работы с символьными значениями: LEFT, RIGHT, PSTR. Они позволяют извлекать указанное количество символов из начала строки текста, из конца или из центра.

Наша задача — разобрать текст на части, из которых потом «наклеить» дату.

Воспользуемся формулой:

Давайте узнаем, как это работает.

LEFT (A2; 2) — извлекает первые 2 символа, т.е число.

PSTR (LEFT (A2; 6); 4; 3) — получаем первые 3 буквы названия месяца.

PSTR (RIGHT (A2; 6); 1; 4) — отображает 4 цифры года.

И все это объединяем с оператором &, помещая между этими частями дефис.

Можно написать немного иначе:

В английской версии:

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

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

Вот несколько готовых решений, которые могут вам пригодиться.

Попробуем вывести дату из пояснения бухгалтерских записей. Как видите, варианты написания могут быть самыми разными. Единственное условие — день записывается двузначным числом: в нашем случае 07.

Формула извлечения даты из текста выглядит так:

если у вас есть две цифры для дня и месяца и четыре для года:

Как должен выглядеть результат в ячейке — определяйте сами. Как именно: мы это уже несколько раз анализировали. См. Чуть выше.

Но что, если мы убедимся, что внешний вид извлеченной даты тоже задается не вручную, а по формуле? Здесь нам поможет функция ТЕКСТ.

Самое простое представление выглядит так:

Как видите, фокус в том, чтобы правильно описать формат во втором аргументе. «ДД ММММ ГГГГ» обозначает день, месяц в именительном падеже и год из четырех цифр. Но « ДД ММММ ГГГГ» будет делать то же самое, но месяц будет в родительном падеже (7 ноября). Остальные варианты вы можете увидеть на фото выше. Вы можете выбрать тот, который лучше всего соответствует вашим потребностям.

Но что, если ваш день можно записать как однозначными, так и двузначными числами? А вот и универсальный вариант. Правда, посложнее.

Здесь мы применяем формулу массива.

Конечно, скобки вставлять не обязательно: они появятся автоматически после нажатия комбинации клавиш CRTR + SHIFT + ENTER, когда вы закончите ввод формулы.

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

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

И не забывайте формулу массива!

Функция Excel ДАТАЗНАЧ — изменить текст на дату

Функция DATEVALUE (DATEVALUE в английской версии) преобразует текстовую дату в порядковый номер дня, который Excel распознает как дату.

Синтаксис DATEVALUE очень прост:

= DATEVALUE (date_as_text)

Простейшее использование этой функции = DATEVALUE (A1), где A1 — это ячейка с датой, хранящейся в виде строки символов.

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

На снимке экрана показано несколько формул DATEVALUE в действии:

На первый взгляд результат работы может показаться вам довольно неожиданным. Вы можете увидеть это на скриншоте в столбце C. Но не запутайтесь — вам просто нужно применить нужное форматирование. Как видите, результатом формулы является порядковый номер дня с 1900 года (столбец C). И если мы применим правильный формат, то есть укажем программе, что на самом деле это не число, а день, мы сразу получим приемлемый результат (столбец D).

Следовательно, столбец D показывает результат C, только на этот раз как дату.

Как вы, наверное, знаете, Excel хранит дату и время как порядковые числа, и только форматирование ячеек позволяет отображать число как дату. В частности, 1 января 1900 года сохраняется как номер 1, а 2 января 1900 года сохраняется как 2. В результате 8 марта 2020 года сохраняется как 43898. Чтобы преобразовать такой серийный номер в дату, все, что вам нужно сделать сделать это изменить формат ячейки.

Для этого выберите ячейку или диапазон ячеек с числами, которые вы хотите представить в виде дат, и нажмите Ctrl + 1, чтобы открыть диалоговое окно «Формат ячеек». Во вкладке «Числовые форматы» в пункте «Дата» выберите подходящий вам аспект в разделе «Тип» и нажмите кнопку «ОК».

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

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

Функция ДАТАВАЛОРЕ — что запомнить?

При преобразовании текстовой строки в дату имейте в виду, что:

• Информация о времени в текстовых значениях игнорируется, как вы можете видеть в строках 2 и 6 на изображении выше (1). Чтобы преобразовать эти значения, содержащие дату и время, используйте функцию VALUE.
• Если год не указан в «текстовой дате», DATEVALUE заменит текущий год системным временем компьютера, как показано в строке 3 выше (2).
• Поскольку Microsoft Excel начинает отсчет времени только с 1 января 1900 г., использование VALOREDATA с более ранним возрастом приведет к ошибке #VALUE!.
• Все значения, связанные со временем, хранятся в Excel в виде числа. Но DATEVALUE не может преобразовывать числовое значение в меру времени и не может обрабатывать текст, который выглядит как число. Для этого вам нужно будет использовать функцию ЗНАЧЕНИЕ. И именно об этом мы поговорим позже.

Как вставить обновляемое время в Экселе

Следующий момент, требующий рассмотрения — как в Экселе вставить обновляемую дату / время. В этом случае пользователь может поставить динамический параметр в ячейке, который автоматически меняется. Если нужно добавить обновляемые сведения, используйте опции «ТДАТА» или «СЕГОДНЯ». Кратко рассмотрим особенности каждой:

1. «СЕГОДНЯ» — функция, которая позволяет поставить день в Экселе в числовом формате. Она полезна, когда необходимо указать текущие сведения вне зависимости от открытия документа. Она также применяется для расчета интервалов. Для функции «СЕГОДНЯ» не предусмотрено аргументов.
2. «ТДАТА» — помогает поставить данные в цифровом отображении. Если до ввода этой опции для ячейки задавался «Общий» формат, он будет меняться на формат даты и времени, который соответствует региональным данным. При этом формат можно менять с помощью команд на вкладке «Главная» и в разделе «число». Функция используется в ситуации, когда на листе нужно указать текущие дату / время или рассчитать параметры на базе текущей даты / времени. Аргументов не предусмотрено.

Чтобы сделать дату / время в Эксель, пройдите следующие шаги:

1. На пустом листе жмите на ячейку А1 один раз, а после CTRL+V. Если вы работаете в приложении через Интернет, повторите копирование.
2. Для переключения между просмотром результатов / формул (тех, что возвращают результаты) кликните на Ctrl+’ (символ удаления) в разделе «Формулы» в группе «Зависимости формул», а после кликните кнопку «Показывать формулы». После копирования примера на пустой лист настройте его с учетом своих потребностей.

Результаты, которые возвращаются опциями «СЕГОДНЯ» и «ТДАТА» в Экселе, меняются только при пересчете листа или выполнении макроса с этой функцией. Регулярного обновления ячеек с этими параметрами не выполняется. В качестве параметров для образца применяются показания часов ПК / ноутбука.

Формирование календаря в Excel

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

Способ 1: Календарь на год вручную

1. Запустите Эксель. Выберите «Пустую книгу».

Выделите ячейки, начиная с B2, заканчивая H8, зажав левую кнопку мыши.

Создайте сетку на выбранном диапазоне при помощи команды «Все границы».

Выделите ячейки с B2 по H2. Объедините их, воспользовавшись командой «Объединить и поместить в центре».

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

Отредактируйте ширину ячеек. Для этого выделите используемые столбцы. Кликните правой кнопкой мыши (далее ПКМ). Затем, в выпадающем списке, выберите «Ширина столбца».

В поле ввода в открывшемся окне задайте ширину ячеек. Затем нажмите «OK».

Теперь выделите выходные дни.

При помощи инструмента «Заливка» выберите любой понравившийся вам цвет для выделения выходных дней.

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

Выделите получившуюся заготовку для первого месяца. Скопируйте необходимое количество раз.

Осталось лишь заполнить даты и правильно обозначить месяцы. Календарь готов.

Способ 2: С применением формул

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

1. Повторите первые четыре пункта, описанные в первом способе.
2. В получившейся большой ячейке напишите название месяца. При стандартных настройках вместо «Январь 2017» в поле отобразится «янв.17». Это не совсем то, что нужно. Поэтому поменяйте формат ячейки. Для этого кликните по ней ПКМ, выберите соответствую строчку. В открывшемся окне выберите «Дата», нужный нам формат отображения и нажмите «OK».

Во второй строке в каждой из ячеек укажите краткое обозначение дней недели.

Выделите незаполненную часть таблицы. В строке формул напишите следующее: =ЕСЛИ(МЕСЯЦ(ДАТА(ГОД(B2);МЕСЯЦ(B2);1))МЕСЯЦ(ДАТА(ГОД(B2);МЕСЯЦ(B2);1)-(ДЕНЬНЕД(ДАТА(ГОД(B2);МЕСЯЦ(B2);1);2)-1) +*7+-1);» «;ДАТА(ГОД(B2);МЕСЯЦ(B2);1)-(ДЕНЬНЕД(ДАТА(ГОД(B2);МЕСЯЦ(B2);1);2)-1)+*7+-1) После этого нажмите сочетание клавиш «Ctrl», «Shift» и «Enter» на клавиатуре, чтобы изменения вступили в силу.

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

Способ 3: Использование специального шаблона

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

1. Запустите программу Эксель. В открывшемся окне вы увидите предлагаемые для выбора типы шаблонов. Среди них вы найдете категорию Календари.

После этого вам предоставится возможность выбрать подкатегорию (на панели справа) и конкретный шаблон.

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

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

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

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

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

Изучив эти методы, можно заметить, что создание календаря в Excel — не такое уж сложное занятие.