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

Преобразовать текст в дату

​Даты в столбце​​ таблица с датами,​значения​ra.NumberFormat = «General»​

​Если же вам​тему прошу закрыть)​ГГГГ​​MsgBox «готов»​ In Selection​

​ воспринимается программой как​ копирования, смотрите в​Excel​

​ даты перевести в​​ в ячейке. (Формат​

​ преобразовывать это значение.​​В ячейке В1​Excel текст в дату​

​ B написаны как​ написанными порядковыми номерами.​, написанного в ячейке.​ra.Value = Evaluate(«TEXT(«​ надо формулой, я​Равиль​и​End S​cell.Formula = cell.Text​ даты, а не​ статье «Копирование в​

​и​ текст (формат текст).​ ячейки -> Дата).​ Нажимаем «ОК».​ вставляем формулу. На​и​

​ текст (текстовый формат​

​Преобразуем порядковые номера дат​

​Формат ячейки​ & ra.Address(, ,​

​ бы сделал так:​​: Здравствуйте, нужно преобразовать​чч​

​ub​​Next cell​ как текст. Собственно,​ Excel».​

​дату в число.​Выделяем ячейку D26. На​Теперь этот столбец​

​Формула получится такая.​

​ закладке «Формулы» в​​дату в текст Excel​значений​ в привычные для​​в​ , True) &​=—ТЕКСТ(A1;»00-00-00″)​ текст 160201 в​столько пробелов, сколько​SkyPro​

​MsgBox «готов»​​ вопрос. Как изменить​Если по какой-то​Например, в ячейке​ закладке «Главная» в​

​ можно сортировать по​

​ =ЗАМЕНИТЬ(A26;11;1;»»)Копируем формулу по​​ разделе «Библиотека функций»​. Если мы скопировали​). Формат​ нас даты.​

​Excel​ «,»»00-00-00″»)»)​Равиль​​ дату 16.02.2001​​ нужно)​: Странная задача..​​End Sub​ формат кодов специальностей​​ причине не считаются​​ Excel дата написана​

​ разделе «Библиотека функций»​ дате. Как это​ столбцу. Получилось так.​

Как в excel преобразовать дату в текст?

При переводе даты в текст необходимо использовать фунцию ТЕКСТ(Дата;Формат)­

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

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

Для определения дня используются следующие значения:

• Д — будет выведен день (число) без указания начального нуля
• ДД — будет выведен день (число) с указанием начального нуля
• ДДД — будет показано название дня недели в сокращенном виде: Пн, Вт,Ср.
• ДДДД — будет показано полное название дня недели: понедельник, вторник,Среда.

Для определения месяца используются следующие значения:

• М — будет выведен месяц (цифрами) без указания начального нуля
• ММ — будет выведен месяц (цифрами) с указанием начального нуля
• МММ — будет показано название месяца в сокращенном виде: янв, фев,мар.
• ММММ — будет показано полное название месяца: январь, февраль,март.
• МММММ — будет показано сокращенное до первой буквы название месяца: Я, Ф, М.

Для определения месяца используются следующие значения:

• ГГ — будет выведен год в виде двух последних цифр года
• ГГГГ — будет выведен год в виде четырех цифр

другие символы выводятся без изменений

Пример

Если в ячейке A1 записано =ДАТА(2018;11;6), то

• =ТЕКСТ(A1 ; «ДД — МММ — ГГГГ» ) выведет строку: 06 — ноя — 2018
• =ТЕКСТ(A1 ; «ДД.ММ.ГГГГ» ) выведет строку: 06.11.2018
• =ТЕКСТ(A1 ; «Д/ММ/ГГГГ» ) выведет строку: 6/11/2018

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

Переводим дату в текстовый формат

Функция РАЗНДАТ – разность двух дат в днях, месяцах и годах

Функция ВРЕМЗНАЧ

Дата и месяц прописью в Excel

Узнаем как написать дату и месяц прописью в Excel (в том числе в именительном и родительном падежах).

Приветствую всех, дорогие читатели блога TutorExcel.Ru.

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

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

Рассмотрим пример кода с данной функцией (VAL) и без нее:

Существует форма для внесения данных, в которой в два поля «TextBox1» и «TextBox1» вносятся числа, которые сравниваются нажатием кнопки «Сравнить»(CommandButton1) и суммируются нажатием кнопки «Сумма» (CommandButton2).

Если на нажатие кнопки сравнения «Сравнить»(CommandButton1) использовать код:

1. Private Sub CommandButton1_Click()
2. Dim i, b As String
3. i = TextBox1.Value
4. b = TextBox2.Value
5. If i b = True Then MsgBox («Первое число больше «)
6. End Sub

А на нажатие кнопки суммы «Сумма» (CommandButton2) применить код:

1. Private Sub CommandButton2_Click()
2. Dim i, b, d As String
3. i = UserForm1.TextBox1.Value
4. b = UserForm1.TextBox2.Value
5. d = i + b
6. TextBox3.Value = d End Sub

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

Формат даты и времени в Excel

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

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

А в категории Время – встроенные форматы времени:

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

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

1. Выделите ячейку, к которой необходимо применить требуемый числовой формат.
2. Откройте диалоговое окно Формат ячеек и перейдите на вкладку Число.
3. В категории Все форматы в поле Тип введите необходимый код числового формата. В нашем случае мы введем: ДДДД ДД.ММ.СС чч:мм:cc и нажмем ОК.
4. Пользовательский числовой формат будет применен к ячейке.

Как видите все достаточно просто!

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

Преобразование даты в текст на практических примерах наглядно.

Преобразование строки даты в дату

= ЛЕВСИМВ (дата; 10) + ПСТР (дата; 12;8)

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

Чтобы преобразовать строку даты в дату-время (дату со временем), вы можете разобрать текст на отдельные компоненты, а затем построить правильное время и дату.

В показанном примере мы используем приведенные ниже формулы.

Для извлечения даты формула в C5:

Чтобы извлечь дату, формула в D5:

= ВРЕМЗНАЧ (ПСТР (B5;12;8))

Чтобы собрать дату-время, формула в E5:

Чтобы получить дату, мы извлекаем первые 10 символов значения с помощью ЛЕВСИМВ:

ЛЕВСИМВ(B5;10) // возвращает «2015-03-01»

Результатом является текст, поэтому, чтобы заставить Excel интерпретироваться как дата, мы помещаем ЛЕВСИМВ в ДАТАЗНАЧ, который преобразует текст в правильное значение даты Excel.

Чтобы получить время, мы извлекаем 8 символов из середины значения с ПСТР:

ПСТР (B5;12;8) // возвращает «12:28:45»

Опять же, результатом является текст. Чтобы заставить Excel интерпретироваться как время, мы помещаем ПСТР в ВРЕМЗНАЧ, который преобразует текст в правильное значение времени Excel.

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

Хотя этот пример извлекает дату и время отдельно для ясности, вы можете комбинировать формулы, если хотите. Следующая формула извлекает дату и время и объединяет их в один шаг:

= ЛЕВСИМВ(дата; 10) + ПСТР(дата; 12;8)

Обратите внимание, что в этом случае значения ДАТАЗНАЧ и ВРЕМЯЗНАЧ не нужны, поскольку математическая операция (+) заставляет Excel автоматически принудительно передавать текстовые значения в числа

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

Особенности синтаксиса функции ДАТАЗНАЧ в Excel

Функция ДАТАЗНАЧ имеет следующую синтаксическую запись:

Единственным аргументом (обязателен для заполнения) является дата_как_текст – текстовое представление даты, которое может быть преобразовано к данным формата Дата. В Excel есть несколько допустимых вариантов записи дат: 13-июн-2019, 13.06.2019. Любой из этих вариантов записи может быть использован в качестве аргумента функции ДАТАЗНАЧ.

1. Если текстовые строки, характеризующие даты, хранятся в ячейках Excel, большинство функций выполняют преобразования данных к требуемому типу автоматически. Однако, во избежание возможных ошибок, рекомендуется использовать функцию ДАТАЗНАЧ.
2. Рассматриваемая функция ориентируется на показания часов, встроенных в ПК, на котором используется редактор Excel. Если в качестве текстового представления даты указана неполная дата, например «13.06», данные о годе будут взяты из текущего времени. Например, функция =ДАТАЗНАЧ(“13.06”) вернет значение 43629, которое после установления формата Дата для ячейки будет преобразовано в 13.06.2019.
3. Если в качестве аргумента функции ДАТАЗНАЧ было передано значение, не преобразуемое к формату Дата (например, =ДАТАЗНАЧ(23), =ДАТАЗНАЧ(ИСТИНА), =ДАТАЗНАЧ(“333”)), будет возвращен код ошибки #ЗНАЧ!
4. Для склеивания значений, содержащихся в отдельных ячейках, чтобы «собрать» их в одну строку, характеризующую значение даты, следует использовать символ “&”. Например, в ячейках A1, B1, C1 хранятся значения 10, 3 и 2019 соответственно. Чтобы получить данные формата Дата и записать их в отдельную ячейку, можно использовать следующую функцию — =ДАТАЗНАЧ(A1&».»&B1&».»&C1).

Макрос преобразования даты в текст, и время в текст (Макросы Sub)

​ в формате «Дата».​​ отчет. Нам нужно​ «Текст». Диалоговое окно​​ этом читайте в​ статье «Формат Excel»​ вот так​будет распознано правильно​rCell.NumberFormat = «@»​ если преобразовывать из​200?’200px’:»+(this.scrollHeight+5)+’px’);»>Sub Текст()​ время в текстовое​ статью «Как быстро​ можно скрыть этот​ формуле, т.д. Смотрите​ ​ ячейки в текстовый​​ тоже стоит дата,​ п.ч. ничего не​​ Просто, установив в​​ посмотреть предыдущую цифру,​​ функции «Текст» заполнили​​ статье «Преобразовать текст​ ​ тут.​

​Sub CreateTime2()​​ в любой локали.​ ​rCell.Formula = t​

​ двух ячеек в​​Dim cell, a$​ значение, но без​ посчитать стаж в​ столбец.​ об этих способах​ формат (число будет​​ но указан порядковый​ должно стоять после​ ячейке формат «Дата»,​ которая стояла в​​ так.​ в число Excel».​Уточним еще один момент​Dim cell As​

​Равиль​​Next​​ одну — то​For Each cell​ добавления 0 в​ Excel».​

​Или, скопировать столбец​​ в статье «Как​ написано цифрами, не​ номер даты. В​ цифр.​

​ ничего не получится.​​ ячейке, т.д. Подробнее​ ​Нажимаем «ОК». Получилась​​Теперь рассмотрим,​​-​ Range, ra As​​: мне надо «DD/MM/YYYY»​End Sub​​ куда? Если в​​ In Selection​​ начало значения 9:00:01,​​Мурад​​ В и вставить​ ​ закрепить в Excel​

​ прописью).​​ таком формате Excel​ ​В этом диалоговом​​ Нам поможет​ о работе с​​ такая формула. =ТЕКСТ(А7;»ДД.ММ.ГГГГ»)​как преобразовать порядковый номер​​формат даты в Excel.​ Range: Application.ScreenUpdating =​​ смотрите файл​romik85​​ третью -то зачем​a = Format(cell,​ т.е не должно​​: Всем привет! Пытался​​ в новый столбец​​ заголовок, строку, ячейку,​У нас такая​​ хранит даты. Чтобы​

​ окне мы написали​​функция в​ этой функцией смотрите​ Копируем формулу из​ дат в формат​В Excel два​ False​lapink2000​:​ макрос, можно формулой. ​

​ «h:mm:ss»)​​ быть после преобразования​ связать несколько файлов​

​ значения ячеек. А​​ ссылку, т.д.».​ таблица. В столбце​ преобразовать дату в​ условия, как должно​Excel «ЗАМЕНИТЬ».​ в статье «Журнал​ ячейки В7 вниз​ «Дата»​ ​ формата – формат​ ​Set ra =​​: Смотрите мой предыдущий​​SkyPro​​ типа такой​​cell.NumberFormat = «@»​ 09:00:01​

​ по уникальному полю​​ столбцs с датами​Рассмотрим,​ С написаны даты​ нужном нам формате,​ быть написано число​Итак, убираем букву​ изменений в Excel».​

​ по столбцу. Получилось​​.​ячейки​ Range(, Range(«C» &​

​ ответ (подсказка: первая​​, то что надо,​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ТЕКСТ(RC1+RC2;»ДД.ММ.ГГГГч:мм:сс»)​

​cell.Value = a​​200?’200px’:»+(this.scrollHeight+5)+’px’);»>Sub Текст()​ — коду специальности.​ и формулами удалить.​как преобразовать число в​

​ в формате «Дата».​​ достаточно установить обычным​​ по формату «Дата»,​ «г» в конце​

​Здесь рассмотрим, как​​ так.​ ​У нас такая​​и формат​ Rows.Count).End(xlUp))​​ строка)​ спасибо) ++++​​(между​​Next cell​​For Each cell​​ Обнаружилось, что коды​​ Подробнее о способах​

​ дату​​ Нам нужно эти​​ способом формат даты​​ в который будем​ дат.​​преобразовать в​

Как отключить автоматический ввод даты в Excel?

Как убрать автоматический ввод даты в Excel?

Автозамена числа на дату в Excel 2003

1. Нажмите на круглую кнопку меню Excel 2007 (в левом верхнем углу программы).
2. Нажмите на «Параметры Excel». …
3. Выберите пункт меню Правописание и, справа, нажмите на кнопку Параметры автозамены:
4. В появившемся окне снимите отметку с чекбокса «Заменять при вводе» и нажмите на ОК:

Почему в Excel пишет дату?

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

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

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

1. Выделите ячейки, которые нужно отформатировать.
2. Нажмите сочетание клавиш CTRL+1. …
3. В диалоговом окне Формат ячеек откройте вкладку Число .
4. В списке Категория выберите пункт Дата и выберите нужный формат даты в поле Тип .

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

Одновременное настройка всех параметров автоматического форматирования

1. Выберите Файл > Параметры.
2. В окне «Параметры Excel»щелкните «Параметры >».
3. На вкладке «Автоформат при типе» отведите флажки для форматирования, которые вы хотите использовать.

Как сделать чтобы в Экселе число не менялось на дату?

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

Как сделать чтобы дата менялась в Excel?

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

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

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

Для отображения даты в нужном нам формате используем функцию ТЕКСТ() : =»Cегодня «&ТЕКСТ(A1;»дд. ММ. гг») . Получим желаемое — «Сегодня 02.10.10».

Как в Excel преобразовать дату в год?

1. Преобразование строки даты в дату = ЛЕВСИМВ (дата; 10) + ПСТР (дата; 12;8) …
2. Преобразовать дату в Юлианский формат = ГОД (дата) и ТЕКСТ (дата-ДАТА (ГОД (дата); 1;0); «000») …
3. Преобразование даты в месяц и год …
4. Преобразование даты в текст …
5. Преобразование даты текста дд/мм/гг в мм/дд/гг …
6. Преобразование текста в дату

Как в Экселе убрать дату в ячейке?

Выберите ячейки, в которые необходимо ввести числа. Щелкните Главная > Числовой формат > Текст.…Отмена автоматической замены чисел датами

1. Пробел перед вводом числа. …
2. Апостроф (‘) перед вводом числа, например ’11-53 или ‘1/47.

Как в Excel изменить дату на месяц?

Чтобы показать его как дату, необходимо выбрать ячейку (или ячейки) и нажать Ctrl+1 . Откроется диалоговое окно Формат ячеек (Format Cells). На вкладке Число (Number) в списке числовых форматов выберите Дата (Date) и затем укажите нужный вам формат.

Как отключить быстрый анализ Excel?

1. Нажмите Отправьте > Опции , чтобы перейти к Параметры Excel диалоговое окно. 2. В диалоговом окне нажмите Общие на левой панели, а затем снимите флажок Показывать параметры быстрого анализа при выборе вариант под Параметры пользовательского интерфейса раздел, см.

Как отключить автоматическое создание гиперссылок в Excel?

Выберите Файл ► Параметры, чтобы открыть диалоговое окно Параметры Excel. Перейдите в раздел Правописание. Нажмите кнопку Параметры автозамены для отображения диалогового окна Автозамена. Перейдите на вкладку Автоформат при вводе и снимите флажок адреса Интернета и сетевые пути гиперссылками (рис.

Как убрать в Экселе?

Как вы знаете, удалить данные/информацию в ячейках Excel можно:

1. нажатием кнопки Delete на клавиатуре, после выделения нужной области
2. нажатием кнопки Удалить содержимое Главная — раздел Редактирование — в нем жмите кнопку Очистить содержимое (см. описание выше)
3. можно удалить не только данные, но и сами ячейки.

Обработка значений даты в текстовом формате в Excel

Пример 2. В таблице Excel указаны даты неверного формата (вместо записи вид «13.06.2019» используется 13_06_2019). Такие данные указаны в двух столбцах. В соседнем необходимо вычислить разницу дней между указанными датами.

Вид таблицы данных:

Для расчетов используем следующую формулу:

Для получения текстовой строки, которая может быть преобразована в данные формата Дата с помощью функции ДАТАЗНАЧ, используем функцию ПОДСТАВИТЬ, которая выполняет замену символов «_» на «.». Результат вычитания двух полученных дат – искомое значение.

Растянем формулу вниз по столбцу чтобы рассчитать все значения:

Создание пользовательского формата времени.

Отображение в MS EXCEL формата ДАТЫ в ТЕКСТовой строке

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

По аналогии с примером из статьи Отображение ЧИСЛОвого формата в ТЕКСТовой строке, рассмотрим, как преобразовать дату в текстовый формат. Начнем сразу с примера.

Введем в ячейку А1 дату 02.10.10. В ячейке В1 попробуем отразить фразу: «Сегодня 02.10.10». Для этого в B1 пишем формулу: =»Cегодня «&A1 . В результате получим: «Сегодня 40453». EXCEL подставил вместо даты «02.10.10» соответствующее ему число 40453.

Для отображения даты в нужном нам формате используем функцию ТЕКСТ() : =»Cегодня «&ТЕКСТ(A1;»дд.ММ.гг») . Получим желаемое — «Сегодня 02.10.10»

Обратите внимание, что ММ (Месяцы) записаны ПРОПИСНЫМИ буквами, чтобы отличить их от минут, которые записываются строчными (см. файл примера )

Приведем другие форматы:

«дддд» (день недели); «ддд» (сокращенный день недели); «д.М.гг»; «дд.ММ.гг Ч:мм»; «ч:мм:сс AM/PM»; «ДД МММ ГГГГ»;

СОВЕТ: Подробнее о пользовательских форматах дат и времени читайте в одноименной статье Пользовательский формат даты и времени.

Строка Excel VBA на дату

Преобразование даты текста дд/мм/гг в мм/дд/гг

= ДАТА(ПРАВСИМВ(A1;2) + 2000; ПСТР(A1;4;2); ЛЕВСИМВ(A1;2))

Чтобы преобразовать даты в текстовом формате дд/мм /гг в истинную дату в формате мм/дд/гг, вы можете использовать формулу, основанную на функции ДАТА. В показанном примере формула в C5:

= ДАТА(ПРАВСИМВ(B5;2) + 2000; ПСТР(B5;4;2); ЛЕВСИМВ (B5;2))

Который преобразует текстовое значение в B5 «29/02/16» в правильную дату Excel.

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

Значение года извлекается с помощью функции ПРАВСИМВ:

ПРАВСИМВ получает по крайней мере 2 символа от исходного значения. Число 2000 добавлено к результату, чтобы создать действительный год. Это число переходит в ДАТА в качестве аргумента год.

Значение месяца извлекается с помощью:

ПСТР извлекает символы 4-5. Результат переходит в ДАТА в качестве аргумента месяц.

Значение дня извлекается с помощью:

ЛЕВСИМВ захватывает последние 2 символа исходного текстового значения, которое переходит в ДАТА в качестве аргумента дня.

Три значения, извлеченные выше, входят в ДАТУ следующим образом:

= ДАТА (2016; «02»; «29»)

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

Примечание: значение 2016 года автоматически было преобразовано в число при добавлении 2000.

Если исходное текстовое значение содержит дополнительные начальные или конечные символы пробела, вы можете добавить функцию СЖПРОБЕЛЫ для удаления:

= ДАТА(ПРАВСИМВ (СЖПРОБЕЛЫ (A1); 2) + 2000; ПСТР(СЖПРОБЕЛЫ (A1); 4;2); ЛЕВСИМВ(СЖПРОБЕЛЫ (A1); 2))

Как записать месяц прописью?

Как и в примере выше воспользуемся функцией ТЕКСТ. Формат «ММММ» даст нам полную запись месяца (в именительном падеже):

Ещё одним способом является совместное применение функций МЕСЯЦ (в английской версии MONTH) и ВЫБОР (английский вариант CHOOSE).Месяц вернёт нам порядковый номер месяца указанной даты (от 1 до 12), а ВЫБОР сопоставит числовому значению текстовое (где 1 — январь, 2 — февраль, …, 12 — декабрь):

Если же мы пишем число с месяцем и годом, то зачастую месяц нужно указать в родительном падеже (а не в именительном как в примере выше), например, 2 февраля 2015 года или 14 августа 2012 года и т.п.

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

Есть и альтернативный вариант. На помощь опять придут уже знакомые функции МЕСЯЦ и ВЫБОР, только тут вместо именительного падежа для месяцев прописываем родительный (меняем окончания):

На этом все. Выбирайте наиболее понравившийся и удобный для вас способ.

Как преобразовать текст в число в Эксель?

Преобразовать даты в текст (Формулы/Formulas)

​ настроили формат ячеек​​ текст» ничего не​ букву «г» и​ изменил некоторые цифры.​ функций» в функциях​ формула считать не​ окно и сменить​А для 300​Например:​ In Selection​Michael_S​Michael_S​ текстовое значение, но​ стаж, возраст. Про​ таблицы столбец А​ картинку в ячейке,​

​Excel​​ «Дата».​​ пишем, нажимаем один​ ​ сделать эти даты​ ​ Или, например, составляем​ «Текстовые» выбираем функцию​

​ будет. Подробнее об​​ формат, смотрите в​ тыс. лучше -​DateValue(Format(«20010116», «0000-00-00»))​

​t = rCell.Text​​: вопрос не понятен. ​​:​​ необходимо преобразовать и​ эти способы читайте​ с датами, то​ ссылку, ячейку в​преобразует формат значения​В ячейке С26​ ​ раз на пробел,​

Преобразовать текст в дату

​Даты в столбце​​ таблица с датами,​значения​ra.NumberFormat = «General»​

​Если же вам​тему прошу закрыть)​ГГГГ​​MsgBox «готов»​ In Selection​

​ воспринимается программой как​ копирования, смотрите в​Excel​

​ даты перевести в​​ в ячейке. (Формат​

​ преобразовывать это значение.​​В ячейке В1​Excel текст в дату​

​ B написаны как​ написанными порядковыми номерами.​, написанного в ячейке.​ra.Value = Evaluate(«TEXT(«​ надо формулой, я​Равиль​и​End S​cell.Formula = cell.Text​ даты, а не​ статье «Копирование в​

​и​ текст (формат текст).​ ячейки -> Дата).​ Нажимаем «ОК».​ вставляем формулу. На​и​

​ текст (текстовый формат​

​Преобразуем порядковые номера дат​

​Формат ячейки​ & ra.Address(, ,​

​ бы сделал так:​​: Здравствуйте, нужно преобразовать​чч​

​ub​​Next cell​ как текст. Собственно,​ Excel».​

​дату в число.​Выделяем ячейку D26. На​Теперь этот столбец​

​Формула получится такая.​

​ закладке «Формулы» в​​дату в текст Excel​значений​ в привычные для​​в​ , True) &​=—ТЕКСТ(A1;»00-00-00″)​ текст 160201 в​столько пробелов, сколько​SkyPro​

​MsgBox «готов»​​ вопрос. Как изменить​Если по какой-то​Например, в ячейке​ закладке «Главная» в​

​ можно сортировать по​

​ =ЗАМЕНИТЬ(A26;11;1;»»)Копируем формулу по​​ разделе «Библиотека функций»​. Если мы скопировали​). Формат​ нас даты.​

​Excel​ «,»»00-00-00″»)»)​Равиль​​ дату 16.02.2001​​ нужно)​: Странная задача..​​End Sub​ формат кодов специальностей​​ причине не считаются​​ Excel дата написана​

​ разделе «Библиотека функций»​ дате. Как это​ столбцу. Получилось так.​

Преобразовать даты в текст (Формулы/Formulas)

​ настроили формат ячеек​​ текст» ничего не​ букву «г» и​ изменил некоторые цифры.​ функций» в функциях​ формула считать не​ окно и сменить​А для 300​Например:​ In Selection​Michael_S​Michael_S​ текстовое значение, но​ стаж, возраст. Про​ таблицы столбец А​ картинку в ячейке,​

​Excel​​ «Дата».​​ пишем, нажимаем один​ ​ сделать эти даты​ ​ Или, например, составляем​ «Текстовые» выбираем функцию​

​ будет. Подробнее об​​ формат, смотрите в​ тыс. лучше -​DateValue(Format(«20010116», «0000-00-00»))​

​t = rCell.Text​​: вопрос не понятен. ​​:​​ необходимо преобразовать и​ эти способы читайте​ с датами, то​ ссылку, ячейку в​преобразует формат значения​В ячейке С26​

Excel works!

Еще раз привет. Как обычно начну статью с того, что скажу — часто встречаюсь с каким-нибудь незнанием со стороны пользователей и сейчас расскажу об этом. Банально, но действительно так и приходят идеи о статье. Сегодня хочется написать отдельный текст про работу со временем в Excel. Как можно быстро обработать временные данные? Как перевести время в число в Excel? Как не запутаться в этом — читаем в тексте!

Время и число в Excel. =СЕГОДНЯ()

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

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

Приведем пример. Вам нужно задать сегодняшнюю дату? Пожалуйста! Записываем функцию =СЕГОДНЯ() — без аргумента в скобках. Она возвращает сегодняшнее число. Как правило, сразу в этой ячейке будет дата в правильном формате.

Но что будет, если данные перевести в формат Числовой? Появиться число 42544,00.

Тоже самое будет и со временем в формате Время (см. на первой картинке), значение будет равно, например 21:00. Если сравнить значения чисел, то они будут идентичны.

Соответственно, как складывать и вычитать такие данные? Да как обычные числа! Если вы хотите, чтобы у вас в ячейке отображалось дата_время, просто сложите числа и задайте нужный формат. Как на примере:

Так же быть и с вычитанием данных друг из друга.

Теперь можно перейти к хитростям обработки дата_время.

Отделить дату от времени и наоборот в Excel

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

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

Воспользуйтесь формулой для определения времени из дата_время

Формула находит целое значение числа, что будет для нас датой и вычитает из общего значения — разница будет для нас временем, не забудьте перевести в полученные данные в формат времени.

Как перевести время в число в Excel? Функции ДЕНЬ, ЧАС, МИНУТЫ

Это тоже просто, если знаете хитрость. Очень и очень часто, нам удобнее использоваться формат 3,5 часа, чем 3:30. Т.е. перевод числа во временном формате в число, но с нужным значением. Для этого есть прекрасные функции Excel =ЧАС(), =МИНУТЫ().

Чтобы перевести время 3:30 в просто три с половиной часа, нам нужно воспользоваться формулой.

Как работает: мы находим количество полных часов функцией ЧАС, затем находим количество полных минут и делим их на 60, чтобы получить минут в часе. Готово!

Если необходимо подсчитать количество дней во времени (например 33:30:00) нужно воспользоваться функцией ДЕНЬ.

Отмена автоматической замены чисел датами

Microsoft Excel предварительно программируется, чтобы упростить ввод дат. Например, 12/2 изменяется на 2-дек. Это очень неприятно, если вы вводите что-то, что не хотите менять на дату. К сожалению, отключить эту возможность не получится. Однако существует несколько способов обойти это.

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

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

Пробел перед вводом числа. После нажатия ввод пробел останется в ячейке. (См. заметки)

Апостроф (‘) перед вводом числа, например ’11-53 или ‘1/47. Апостроф не отображается в ячейке после нажатия ввода.

Нуль и пробел перед вводом дроби, например 1/2 или 3/4, чтобы они не менялись, например, на 2-янв или 4-мар. Введите 0 0 1/2 или 0 3/4. Ноль не остается в ячейке после нажатия ввод, и ячейка становится типом дробного числа.

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

Нажмите клавиши CTRL+1 (1 в строке чисел над клавишами QWERTY), чтобы открыть формат ячеек.

Выберите тексти нажмите кнопку ОК.

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

Щелкните Главная > Числовой формат > Текст.

Рекомендуется использовать апостроф вместо пробела для ввода данных, если вы планируете использовать функции подменю для данных. Такие функции, как ПОИСКПОЗ и ВПР, не учитывают апострофы при вычислении результатов.

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

При введении в ячейку числа с буквой «е», например 1e9, оно автоматически преобразуется в научное число: 1,00E+09. Чтобы избежать этого, введите перед числом апостроф: ‘1e9

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

Не обращайте внимание на треугольник или щелкните его. Слева появится поле

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

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Как преобразовать дату в текст в Excel

В Excel каждой дате соответствует определенное число дней, прошедших с принятой точки отсчета – 1 января 1900 года. Функция ДАТАЗНАЧ возвращает число, соответствующее числовому представлению даты, которая указана в виде текста, с учетом указанной выше особенности хранения дат в Excel. Формат возвращаемого значения зависит от настроек формата ячейки, в которой будет выведен результат вычислений.

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

Пример 1. В таблице Excel находится столбец, в котором хранятся даты как текстовые строки, при этом записи имеют вид: «28 сентября 2018 года». Преобразовать эти значения в данные формата Дата.

Вид таблицы данных:

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

Единственный аргумент состоит из подстрок, склеенных амперсандами (&):

Функция ЛЕВСИМВ возвращает номер дня (первые два символа строки, содержащейся в ячейке A2)

Очень важно, чтобы однозначные номера дней (например, 8 апреля) записывались как 08 апреля (имели нуль в начале), иначе будет возникать ошибка.
Комбинация функций ПСТР и ЛЕВСИМВ выделяет из строки три первых символа названия месяца и возвращает их.
Комбинация функций ПСТР и ПРАВСИМВ выделяет 4 символа, соответствующие числовому представлению года.. Растянем формулу вниз по столбцу, чтобы рассчитать остальные значения:

Растянем формулу вниз по столбцу, чтобы рассчитать остальные значения:

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

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

Является ли текст данных ячейки или числа?

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

Пример №1. Преобразование даты в текстовом формате в число

В приведенном выше примере, формула возвращает числовое значение даты введенной в кавычках. Введенная дата должна быть в текстовом формате, иначе Excel возвращает “#VALUE!” ошибку.

Обратите внимание, что значение, возвращаемое функцией зависит от настройки часов вашей системы. Если дата указана в формате ДД-ММ-ГГГГ значит «01-06-2016» будет отображать как 1 июня 2016 года, но для формата даты ММ-ДД-ГГГГ дата будет отображаться как 6 января 2016

Больше лайфхаков в нашем Telegram Подписаться

Пример №2. Преобразование даты в текстовом формате с помощью формулы

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

Обратите внимание, что дата должна быть в текстовом формате

В приведенном выше примере, формула преобразует “1-6-16” и “1-июня-16” — это значение указанное в формате даты.

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

Пример №3. Использование функции без указания года

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

Пример №4. Использование функции без указания дня

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

В приведенных выше примерах, когда мы вводим число «6-16», система возвращает число 42522, которое является значением даты на 1 июня 2016 года.

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

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