Elettracompany.com

Компьютерный справочник
7 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Vba excel дата в текст

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

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

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

Преобразовать число в дату Excel.

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

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

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

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

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

Макрос преобразования даты в текст, и время в текст (Макросы 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([C1], 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​
​ Обнаружилось, что коды​
​ Подробнее о способах​

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

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

​Даты в столбце​​ таблица с датами,​значения​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 VBA-преобразование текста в дату?

У меня есть столбец дат (столбец а), сохраненный в виде текста в формате yyyy-mm-dd, который я пытаюсь преобразовать в даты, в конечном счете, чтобы я мог выполнять поиск по ним.

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

Это изменило формат ячеек на сегодняшний день, но фактически не изменило формат значения, которое все еще хранилось в виде текста.

Я понимаю, что мне нужно использовать CDate(), чтобы изменить формат значения с текста на дату. Я уже пробовал нечто подобное:

Что дает мне ошибку несовпадения типов. Я задался вопросом, не было ли это потому, что я пытался сохранить значения даты обратно в ячейку без даты, поэтому я попытался объединить ее с .NumberFormat = «date» выше, что тоже не сработало.

Любые предложения будут приняты с благодарностью.

10 Ответов

Вы можете быстро преобразовать столбец текста, который напоминает даты, в фактические даты с помощью команды VBA, эквивалентной команде Data ► Text-to-Columns листа.

Массовые операции, как правило, гораздо быстрее, чем циклирование по ячейкам, и метод VBA Range.TextToColumns очень быстр. Он также позволяет вам свободно устанавливать маску преобразования MDY против DMY или YMD, которая мешает многим текстовым импортам, где формат даты не соответствует региональным настройкам системы. Полный список доступных форматов дат см. в свойстве TextFileColumnDataTypes.

Предостережение: будьте осторожны при импорте текста, чтобы некоторые даты еще не были преобразованы. Текстовая дата с неоднозначными целыми числами месяца и дня уже может быть преобразована неверно; например, 07/11/2015 может быть интерпретировано как 07-Nov-2015 или 11-Jul-2015 в зависимости от региональных настроек системы. В таких случаях следует отказаться от импорта и вернуть текст вместе с данными ► получить внешние данные ► из текста и указать правильную маску преобразования даты в Мастере импорта текста. В VBA используйте метод Workbooks.OpenText и укажите xlColumnDataType.

Читать еще:  Разбиение текста на колонки в word

Вы можете использовать DateValue для преобразования строки в дату в этом экземпляре

Он может конвертировать строку формата yyyy-mm-dd непосредственно в собственное значение даты Excel.

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

работает (только что протестирован с описанием вашего случая, с Excel 2010). Что касается причин, по которым вы получаете ошибку несоответствия типов, вы можете проверить (например) это .

Это может быть проблема locale.

Это предполагает, что столбец A содержит текстовые значения типа 2013-12-25 без ячейки заголовка.

Этот код работает на меня

Попробуйте изменить столбцы в соответствии с вашим листом

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

Используя VBA.DateSerial(year,month,day) , вы можете преодолеть внутреннее смещение Excel в формат даты US. Это также означает, что у вас есть полный контроль над данными, который я лично предпочитаю:

На OP. Я также получил ошибку несоответствия типов при первой попытке запустить вашу подпрограмму. В моем случае это было вызвано данными non-date-like в первой ячейке (т. е. заголовком). Когда я изменил содержимое ячейки заголовка на txt в стиле даты для тестирования, он работал просто отлично.

Надеюсь, это тоже поможет.

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

Я избавился от несоответствия типов следующим кодом:

Но в моем конкретном случае у меня есть данные в формате » dd.mm.yy»

Для DD-MM -YYYY вот простой обходной путь для управления строками и датами:

вставьте дату в строку через DD — MMM -YYYY например 01-11-2017 — > 01-Nov-2017

U может использовать FORMAT (date, «dd-mmm-yyyy») для ввода дат в строку из спред-листа.

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

Похожие вопросы:

(Excel 2010 VBA) У меня есть ячейка (A1), содержащая дату в формате mmm-yy (категория Custom). Например, если я ввожу 1/6/13, то ячейка показывает июнь-13. Вот и хорошо. В моем макро VB мне нужно.

Я сделал все возможное в excel, чтобы извлечь дату из этого текста Mon Nov 03 10:07:06 2014 Может ли кто-нибудь помочь мне в этом? Я хочу получить дату в этой форме, например, G 11/3/2014, и как.

Итак, я пытаюсь получить дату из html, используя VBA в Excel, и у меня возникают проблемы с поиском способа извлечения текста, который я хочу, чтобы он выглядел как:
Excel преобразование формулы даты excel в VBA

У меня есть эта формула Excel ниже; =DATE(YEAR(G8),MONTH(G8),8)-WEEKDAY(DATE(YEAR(G8),MONTH(G8),4))+14 Я хотел бы преобразовать эту формулу Excel в VBA Возможно ли это сделать в Excel VBA.

Я импортировал некоторые даты в документ excel, и мне нужно отформатировать их как даты, а не текстовые строки. Формат такой: 12.05.79 но когда я устанавливаю формат на дату excel ничего не делает.

Короткий вопрос: Я хочу вставить строку типа 10/3 в такую ячейку, как эта: Worksheets(Sheet1).Range(A1).Value = 10/3 Но Excel автоматически форматирует ячейку как дату или число. Я хочу иметь его.

В Excel VBA я хочу получить текст ячейки вместе с форматом каждого слова. Например, ячейка A1 имеет значение образец текста . Диапазон (A1).Свойство Value возвращает только обычный текст (т. е.

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

Мы генерируем HTML из текста в электронной таблице Excel. Текст содержит unicode представления международных символов. Когда мы используем VBA для извлечения текста и вывода его в файл, он.

Я делаю преобразование с помощью текста в столбцы в Excel на таком тексте, как: NAME|PERIOD_NAME|AMOUNT|CREATION_DATE ANDY|OCT-18|6385.46|17-OCT-2018 Я использую следующий код.

Функция VAL в VBA Excel или как преобразовать TextBox в число (цифру).

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

Для решения денной проблемы в VBA можно использовать функцию VAL(), которая переводит в численный формат данных. VAL — это функция конвертирующая текстовые значения аргумента в целые числовые или числовые значения с плавающей запятой. Следует отметить что функция Val при преобразовании десятичных дробей в число может распознать только точку, то есть, если вы напишете десятичную дробь через запятую, то функция распознает только первую (целую)часть дроби, остальной текст после запятой функция не воспримет. Например: VAL(«10,8»)= 10

Рассмотрим пример кода с данной функцией (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

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

Применим функцию VAL () в этих кодах:

1-й код (Сравнение двух чисел)

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

2-й код (Сумма двух чисел)

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

С таким кодом кнопки формы UserForm1 работают корректно, как показано на скриншоте далее.

Vba excel дата в текст

235723 просмотра

11 ответа

118 Репутация автора

У меня есть столбец дат (столбец A), сохраненный в виде текста в формате yyyy-mm-dd, который я пытаюсь преобразовать в даты, в конечном итоге, чтобы я мог выполнить поиск по ним.

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

Это изменило формат ячеек до даты, но фактически не изменило формат значения, которое все еще сохранялось как текст.

Читать еще:  Как сделать сноску в документе word

Насколько я понимаю, мне нужно использовать CDate (), чтобы изменить формат значения с текста на дату. Я пробовал что-то вроде этого:

Что дает мне ошибку несоответствия типов. Я задавался вопросом, было ли это потому, что я пытался сохранить значения даты обратно в ячейку без даты, поэтому я попытался объединить ее с .NumberFormat = «date» выше, что тоже не сработало.

Мы ценим любые предложения.

Ответы (11)

6 плюса

77280 Репутация автора

Это предполагает, что столбец A содержит текстовые значения, такие как 2013-12-25 без ячейки заголовка.

21 плюса

6400 Репутация автора

Вы можете использовать DateValue для преобразования вашей строки в дату в этом случае

Он может конвертировать yyyy-mm-dd строку формата непосредственно в собственное значение даты Excel.

Автор: Sam Размещён: 04.12.2013 12:48

12 плюса

6695 Репутация автора

Помимо других опций, я подтверждаю, что используя

работает (только что протестировано с описанием вашего случая, с Excel 2010). Что касается причин, по которым вы получаете ошибку несоответствия типов, вы можете проверить (например) это .

Это может быть языковой вопрос.

1 плюс

11 Репутация автора

Для OP . Я также получил ошибку несоответствия типов при первой попытке запустить вашу подпрограмму. В моем случае это было вызвано данными, не похожими на даты, в первой ячейке (т. Е. В заголовке). Когда я изменил содержимое ячейки заголовка на txt в стиле даты для тестирования, он работал нормально .

Надеюсь, это поможет.

Автор: Eric Размещён: 20.02.2015 06:10

27 плюса

Репутация автора

Вы можете быстро преобразовать столбец текста, который напоминает даты, в фактические даты с помощью VBA-эквивалента команды Данные ► Текст в столбцы на рабочем листе.

Массовые операции, как правило, выполняются намного быстрее, чем циклическая обработка ячеек, а метод Range.TextToColumns в VBA очень быстрый. Это также позволяет вам свободно устанавливать маску преобразования MDY в DMY или YMD, которая сопровождает многие операции импорта текста, если формат даты не соответствует региональным настройкам системы. См. Свойство TextFileColumnDataTypes для получения полного списка доступных доступных форматов даты.

Предостережение: будьте осторожны при импорте текста, чтобы некоторые даты еще не были конвертированы. Текстовая дата с неоднозначными числами месяца и дня уже может быть неправильно преобразована; например, 07/11/2015 может быть интерпретировано как 07-ноя-2015 или 11-июл-2015 в зависимости от региональных настроек системы. В подобных случаях откажитесь от импорта и верните текст обратно с помощью Данные ► Получить внешние данные ► Из текста и укажите правильную маску преобразования даты в мастере импорта текста. В VBA используйте метод Workbooks.OpenText и укажите xlColumnDataType.

3 плюса

33 Репутация автора

Этот код работает для меня

Попробуйте изменить столбцы в соответствии с вашим листом

плюса

1 Репутация автора

Я избавился от несоответствия типов с помощью следующего кода:

Но в моем конкретном случае у меня есть данные в формате «dd.mm.yy»

плюса

1053 Репутация автора

Для DD- MM -YYYY есть простой обходной путь для управления строкой и датами:

вставьте дату в строку с помощью ДД- МММ- ГГГГ, например, 01-11-2017 -> 01-Nov-2017

Вы можете использовать ФОРМАТ (date, «dd- mmm -yyyy») для ввода дат в строку из электронной таблицы.

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

Автор: Gilco Размещён: 05.09.2017 12:37

1 плюс

40 Репутация автора

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

2 плюса

1028 Репутация автора

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

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

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

= ЛЕВСИМВ (дата; 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 автоматически принудительно передавать текстовые значения в числа.

Преобразовать дату в Юлианский формат

= ГОД (дата) и ТЕКСТ (дата-ДАТА (ГОД (дата); 1;0); «000»)

Если вам нужно преобразовать дату в формат даты в Юлиане в Excel, вы можете сделать это, построив формулу, в которой используются функции ТЕКСТ, ГОД и ДАТА.

«Формат даты в Юлиане» относится к формату, в котором значение года для даты комбинируется с «порядковым днем для этого года» (т. Е. 14-й день, 100-й день и т. д.) для формирования штампа даты.

Есть несколько вариантов. Дата в этом формате может включать в себя 4-значный год (гггг) или год с двумя цифрами (гг), а номер дня может быть заполнен нулями или может быть не дополнен тремя цифрами. Например, на дату 21 января 2017 года вы можете увидеть:

Для двухзначного года + число дня без дополнения используйте:

Для двузначного года + число дня, дополненное нулями до 3-х мест:

Для четырехзначного года + число дня, дополненное нулями до 3-х мест:

Эта формула строит окончательный результат в 2 частях, объединенных конъюнкцией с оператором амперсанда (&).

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

Чтобы извлечь полный год, используйте функцию ГОД:

С правой стороны амперсанда нам нужно определить день года. Мы делаем это, вычитая последний день предыдущего года с того дня, с которым мы работаем. Поскольку даты — это просто серийные номера, это даст нам «n» день года.

Читать еще:  Электронная подпись в word 2020

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

B5-ДАТА(ГОД (B5); 1;0)

Дает нам последний день предыдущего года, который на примере 31 декабря 2015 года.

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

ТЕКСТ (B5-ДАТА (ГОД (B5); 1;0); «000»)

Если вам нужно преобразовать юлианскую дату назад к обычной дате, вы можете использовать формулу, которая анализирует юлианскую дату и пробегает ее через функцию даты с месяцем 1 и днем, равным «n-му» дню. Например, это создаст дату с Юлианской датой ггггддд, например, 1999143.

= ДАТА(ЛЕВСИМВ(A1;4); 1; ПРАВСИМВ(A1;3)) // для ггггддд

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

Где A1 содержит номер дня. Это работает, потому что функция ДАТА умеет настраивать значения вне диапазона.

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

Чтобы преобразовать нормальную дату Excel в формат ггггмм (например, 9/1/2017> 201709), вы можете использовать функцию ТЕКСТ.

В показанном примере формула в C5:

= ТЕКСТ (B5; «ггггмм»)

Функция TEКСT применяет заданный числовой формат к числовому значению и возвращает результат в виде текста.

В этом случае предоставляется формат числа «ггггмм», который присоединяется к 4-значному году с 2-значным значением месяца.

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

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

= ТЕКСТ (дата; формат)

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

Даты и время в Excel хранятся в виде серийных номеров и преобразуются в удобочитаемые значения «на лету» с использованием числовых форматов. Когда вы вводите дату в Excel, вы можете применить числовой формат, чтобы отобразить эту дату по своему усмотрению. Аналогичным образом, функция ТЕКСТ позволяет преобразовать дату или время в текст в предпочтительном формате. Например, если дата 9 января 2000 года введена в ячейку A1, вы можете использовать TEКСТ, чтобы преобразовать эту дату в следующие текстовые строки следующим образом:

= ТЕКСТ(A1; «ммм») // «Янв»

= TEКСТ(A1; «дд/мм/гггг») // «09/01/2012»

= ТЕКСТ(A1; «дд-ммм-гг») // «09-Янв-12»

Вы можете использовать TEКСТ для преобразования дат или любого числового значения в фиксированном формате. Вы можете просмотреть доступные форматы, перейдя в меню «Формат ячеек» (Win: Ctrl + 1, Mac: Cmd + 1) и выбрав различные категории в списке слева.

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

= ДАТА(ПРАВСИМВ(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))

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

=ДАТА (ЛЕВСИМВ(текст; 4); ПСРТ(текст; 5;2); ПРАВСИМВ(текст; 2))

Чтобы преобразовать текст в непринятом формате даты в правильную дату Excel, вы можете проанализировать текст и собрать правильную дату с формулой, основанной на нескольких функциях: ДАТА, ЛЕВСИМВ, ПСРТ и ПРАВСИМВ.

В показанном примере формула в C6:

= ДАТА(ЛЕВСИМВ(B6;4); ПСРТ(B6;5;2); ПРАВСИМВ(B6;2))

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

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

текст (19610412) Дата представления (Апрель 12, 1961)

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

Функция ДАТА принимает три аргумента: год, месяц и день. ЛЕВСИМВ извлекает самые левые 4 символа и поставляет это в ДАТА в качестве года. Функция ПСРТ извлекает символы 5-6 и поставляет это в ДАТА в качестве месяца, а функция ПРАВСИМВ извлекает самые правые 2 символа и поставляет их в ДАТА в качестве дня. Конечным результатом является правильная дата Excel, которая может быть отформатирована любым способом.

В строке 8 (непризнанный) формат даты дд.мм.гггг и формула в C8:

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

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

Чтобы преобразовать даты, добавив нуль, попробуйте Специальную вставку:

  • Добавить ноль в неиспользуемую ячейку и скопировать в буфер обмена
  • Выберите проблемные даты
  • Специальная вставка> Значения> Добавить

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

Где A1 содержит непризнанную дату.

Другой способ заставить Excel распознавать даты — использовать текст в столбцах:

Выберите столбец дат, затем попробуйте Дата> Текст в столбах>Исправлено> Конец

Ссылка на основную публикацию
Adblock
detector