Как скопировать дату в excel без изменений
Предположим, что требуется легко указывать текущие дату и время при записи действий в журнал. Или нужно автоматически отображать текущие дату и время в ячейке при каждом пересчете формул. Вставить текущие дату и время в ячейку можно несколькими способами.
Вставка фиксированного значения даты или времени в ячейку Excel
Фиксированное значение на листе не изменяется при пересчете или открытии листа. При нажатии клавиш CTRL+; для вставки текущей даты в ячейку Excel «делает снимок» текущей даты, который вставляется в ячейку. Это значение ячейки не изменяется, поэтому называется статическим.
Выделите на листе ячейку, в которую требуется вставить текущую дату или время.
Выполните одно из действий, указанных ниже.
Чтобы вставить текущую дату, нажмите клавиши CTRL+; (точка с запятой).
Чтобы вставить текущее время, нажмите клавиши CTRL+SHIFT+; (точка с запятой).
Чтобы вставить текущие дату и время, нажмите клавиши CTRL+; (точка с запятой), затем клавишу ПРОБЕЛ, а после этого — CTRL+SHIFT+; (точка с запятой).
Изменение формата даты и времени
Чтобы изменить формат даты или времени, щелкните ячейку правой кнопкой мыши и выберите Формат ячеек. Затем в диалоговом окне Формат ячеек на вкладке Число в разделе Числовые форматы щелкните Дата или Время и в списке Тип выберите тип и нажмите ОК.
Вставка фиксированного значения даты или времени в ячейку Excel
Фиксированное значение на листе не изменяется при пересчете или открытии листа. При нажатии клавиш CTRL+; для вставки текущей даты в ячейку Excel «делает снимок» текущей даты, который вставляется в ячейку. Это значение ячейки не изменяется, поэтому называется статическим.
Выделите на листе ячейку, в которую требуется вставить текущую дату или время.
Выполните одно из действий, указанных ниже.
Чтобы вставить текущую дату, нажмите клавиши CTRL+; (точка с запятой).
Чтобы вставить текущее время, нажмите клавиши +; (точка с запятой).
Чтобы вставить текущие дату и время, нажмите клавиши CTRL+; (точка с запятой), затем клавишу ПРОБЕЛ, а после этого — +; (точка с запятой).
Изменение формата даты и времени
Чтобы изменить формат даты или времени, щелкните ячейку правой кнопкой мыши и выберите Формат ячеек. Затем в диалоговом окне Формат ячеек на вкладке Число в разделе Числовые форматы щелкните Дата или Время и в списке Тип выберите тип и нажмите ОК.
Вставка фиксированного значения даты или времени в ячейку Excel
Фиксированное значение на листе не изменяется при пересчете или открытии листа. При нажатии клавиш CTRL+; для вставки текущей даты в ячейку Excel «делает снимок» текущей даты, который вставляется в ячейку. Это значение ячейки не изменяется, поэтому называется статическим.
Выделите на листе ячейку, в которую требуется вставить текущую дату или время.
Выполните одно из действий, указанных ниже.
Чтобы вставить дату, введите ее (например, 02.02), а затем выберите Главная > раскрывающийся список Числовой формат (на вкладке Число) > Краткий формат даты или Длинный формат даты.
Чтобы вставить время, введите его и выберите Главная > раскрывающийся список Числовой формат (на вкладке Число) > Время.
Изменение формата даты и времени
Чтобы изменить формат даты или времени, щелкните ячейку правой кнопкой мыши и выберите Числовой формат. Затем в диалоговом окне Числовой формат в разделе Числовые форматы щелкните Дата или Время и в списке Тип выберите тип и нажмите ОК.
Вставка обновляемого значения даты или времени
Дата или время обновляемые при пересчете листа или открытии книги, считаются «динамическими», а не статическими. Наиболее распространенным способом получения динамической даты или времени в ячейке на листе является использование функции листа.
Для вставки обновляемой текущей даты или времени используются функции СЕГОДНЯ и ТДАТА, как показано в приведенном ниже примере. Дополнительные сведения об использовании этих функций см. в статьях Функция СЕГОДНЯ и Функция ТДАТА
Описание (результат)
Текущая дата (значения могут быть разными)
Текущие дата и время (значения могут быть разными)
Выделите текст в таблице, приведенной выше, и нажмите клавиши CTRL+C.
На пустом листе щелкните ячейку A1 и нажмите клавиши CTRL+V. При работе в Excel в Интернете повторите копирование и вставку для каждой ячейки из примера.
Важно: Чтобы пример правильно работал, его нужно вставить в ячейку A1.
Чтобы переключиться между просмотром результатов и просмотром формул, возвращающих эти результаты, нажмите клавиши Ctrl+` (знак ударения) или на вкладке Формулы в группе Зависимости формул нажмите кнопку Показывать формулы.
Скопировав пример на пустой лист, можно настроить его в соответствии со своими потребностями.
Примечание: Результаты, возвращаемые функциями СЕГОДНЯ и ТДАТА, изменяются только при пересчете листа или при выполнении макроса, содержащего эту функцию. Постоянного обновления ячеек, содержащих эти функции, не происходит. В качестве значений даты и времени используются показания системных часов компьютера.
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.
Как скопировать дату в excel без изменений
Смотрите также cell.Offset(, 0) = 0) = Format(cell,: «+» дает 1Dim rCell AsKuklP
Левсимв, Пстр, Текст. в ячейках стоит Нам нужно разделить выбираем функцию «Текст».
сделать, смотрите вОсталось преобразовать этот текст Нажимаем из списка «наследство» таблицу, в(внешний вид данных)стоит формат дат в вид данных вРассмотрим, как DateValue(Format(cell, «00/00/00»)) «00/00/00») пробел между данными,
Range: Помогло лишь одно не тот формат. точками число на Появившееся диалоговое окно статье «Сортировка по в дату. на функцию «ЗАМЕНИТЬ». которой даты написаны – «Общий». Если
этом же столбце, ячейке — шрифт,преобразовать дату в текст
текст получается смотритеполучается текст 16/02/01 нужно 2, а’application.Volatile ‘раскомментировать, если200?’200px’:»+(this.scrollHeight+5)+’px’);»>cell.value = format(cell,»h:mm:ss») — вручную перебить
Напоминаем, что в год, месяц и заполняем так. дате в Excel».Преобразовать текст в дату Заполняем диалоговое окно текстом, мы не нужно, то можно то выделяем ячейки, размер шрифта; число, Excel сами (вложенный файл) — нужна дата
лишние колонки тормозят нужен автопересчет функции.romik85 код Excel есть два день.В строке «Значение» указали
Преобразовать число в дату Excel. так. сможем отсортировать данные изменить формат ячеек устанавливаем нужный формат записанное с разрядами;. Нам нужно, чтобы а формулой неподредактируйте код пожалуйста работу( , затемFor Each rCell: спасибо что ответили,Мурад формата – форматКак преобразовать число в адрес ячейки, дату
в Excel.В новом столбцеВ строке «Старый текст» по дате. В «Общий» на формат
дат. в каком месте дата была написана
вариант, т.к у или напишите лучший файлы отдаются на
In dateRange но данный код,: Снимаю вопрос! ячейки и формат дату Excel. в которой нужно
Бывает, по разным устанавливаем формат ячеек указываем первую ячейку, ячейках есть формат «Текстовый».Если нужно перенести ячейки будут написаны
в текстовом формате. мне преобрзовать надо вариант
загрузкуIf rCell.Text <> преобразует ячейку сПопробовал значений. Подробнее об
У нас такая преобразовать в текст.
причинам, дата в «Дата», выделяем первую в которой нужно
ячейки, а естьКак скопировать дату эти данные в данные – слева,Сначала вспомним, как 300 тыс. строк.GuestMichael_S
«» Then текстовым форматов в200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ТЕКСТ(B4;»ГГ.ММ.ДД») этих форматах, чем таблица с датами,В строке «Формат»
ячейке написана одним ячейку. На закладке убрать букву «г». формат значений. в текстовом формате другой столбец, то справа, по центру, записаны и какlapink2000: =ДАТА(2000+ПСТР(D11;5;2);ПСТР(D11;3;2);ПСТР(D11;1;2)): . ставите между ГГГГDateTimeToText = DateTimeToText
ячейку с форматоми получилось. Извините, они отличаются, как написанными одним числом. указали формат, в числом (например, так
«Формулы» в разделеВ строке «Нач_поз»Бывает, стоит в в формат «Дата», выделяем ячейки, копируем выровнены, т.д. хранятся даты в: Это от того,lapink2000 и чч столько & rCell.Text & время.
что форум засорил. настроить нужный формат,Нам нужно сделать так, котором нужно записать — 20160112). нам «Библиотека функций» нажимаем указываем, какой по ячейке формат «Дата», смотрите в статье в другой столбецНапример, при числовом Excel. что у вас: cell.Offset(, 0) =
Преобразовать текст в дату Excel.
пробелов, сколько нужно » « необходимо преобразовать ячейкуUdik т.д., читайте в чтобы все даты число. нужно придать дате на кнопку «Время счету символ нужно а сортировка по «Преобразовать текст в и устанавливаем формат формате ячеек данныеВ Excel даты
ячейки отформатированы как DateValue(Format(cell, «00/00/00»))SkyProEnd If с форматом время,: Формат на текстовый статье «Преобразовать дату были написаны вПолучилось так. нормальный вид, отделив и дата». Нажимаем
убрать. У нас датам не работает,
дату Excel». ячеек «Дата». пишутся от правой хранятся под порядковыми ТЕКСТ ;-)Внимание! Это будет: эм.. а чтоNext в ячейку с вполне меняется штатными в текст Excel» таком формате –Формат даты пишем заглавными точками год, месяц, в появившемся списке буква «г» -
п.ч. даты написаныБывает, что вЗдесь в столбце B
границы ячейки, а номерами. Например, датаПопробуйте вот так: работать правильно на мешает переделать?DateTimeToText = Left(DateTimeToText, форматом текст. средствами (формат ячеек). здесь.
26.01.2016. буквами. Например, ММ день (например, так на
это 11 по текстом. Подробнее про ячейках дата написана дата стоит в при текстовом формате 01.01.1900 г. хранитсяSub CreateTime() компьютерах, где системныйromik85
Len(DateTimeToText) — 1)приходится сцеплять многоМурадКак работать сВ ячейке В1 (большими буквами) -это — 2016.01.12). Какфункцию «ДАТАЗНАЧ» счету символ (считаем формат ячеек и
одним числом (например, формате ячейки «Дата». – от левой под порядковым номеромDim cell As формат даты «DD/MM/YYYY».: ммм, не дает
End Function строк даты со: датами в Excel, пишем такую формулу. месяц, а мм это сделать, читайте. В появившемся диалоговом
и точки, и значений в Excel, так. 20160112). Нам
Excel посчитает даты, границы ячейки.
– 1. Дата Range, ra As
Если в системе больше 1 пробела,romik85 временем, и времяUdik менять формат, считать,=ДАТА(ЛЕВСИМВ(A1;4);ПСТР(A1;5;2);ПРАВСИМВ(A1;2)) (маленькими буквами) – в статье «Преобразовать окне указываем адрес пробелы). смотрите в статье нужно преобразовать это записанные и порядковымиФормат значения ячейки Excel 02.01.1900 г имеет Range: Application.ScreenUpdating = порядок дня и
результат такой же,
: спасибо работает, и создает некоторую проблемку(., действительно. Надо первым прибавлять дни кКопируем формулу по это минуты, формула число в дату
выделенной ячейки. НажимаемВ строке «Число «Преобразовать дату в число в дату, номерами, но нам– это формат, порядковый номер 2, False месяца противоположный (например как и =A2+B2 еще такой вопрос,RAN
шагом изменить формат дате, вычитать, т.д., столбцу. Получилось так. будет считать по-другому. Excel». «ОК». Копируем формулу
знаков» написали цифру текст Excel».
разделить точками год, удобнее видеть даты с помощью которого дата 27.01.2015 г.Set ra = US), то датаromik85 можно ли объединить: Ниче не понял. на текстовый, а смотрите в статьеПреобразовать дату в числоЕсли нужен другойПреобразовать дату в текст
по столбцу. Получилось 1, п.ч. стоит
У нас такая месяц, день. Как в формате «Дата». Excel понимает ЧТО имеет порядковый номер Range([C1], Range(«C» & не будет распознана: знаний не хватает( в макросе преобразованииЗачем что-то в вторым шагом изменить «Работа с датами Excel. формат, то пропишем
Excel. так, смотрите ячейку одна буква. Если таблица. это сделать, смотритеПреобразовать дату в текст написано – число,
– 42031, т.д. Rows.Count).End(xlUp)) или будет распознанапоэтому к вам даты и времени, текст превращать? Дата текстовый формат на
в Excel» иЧтобы преобразовать дату его в формуле.Чтобы
С27. бы было написаноВ ячейках таблицы стоит в статье «Преобразовать
в Excel.
дата, текст, т.д.,Чтобы преобразовать этотra.NumberFormat = «General» неверно. и обратился) в разных ячейках, со временем и
формат даты в конце этой в число, в Например, так.
преобразовать дату в текстОбратите внимание! слово «год», то формат «Дата», но число в датуДаты записаны так. и проводит различные порядковый номер вFor Each cellЕсли необходимо хранитьSkyPro колонках в текст, так сцепляется.. Спасибо!
Преобразовать число в дату Excel.
же статьи, смотрите соседней ячейке пишемВ Excel можно ExcelВ ячейке С27 в строке написали написаны даты в Excel». Формат ячеек – вычисления в зависимости привычный для нас In ra.Cells даты в текстовом
: а то дата
romik85romik85 перечень статей в
такую формулу. многое закрепить, чтобы(установить текстовый формат, стоит дата в бы цифру «3»,
текстовом формате. ВВ Excel можно
«Дата».
от формата числа, формат даты, нужно
cell.Offset(, 0) = формате, то лучше
200?’200px’:»+(this.scrollHeight+5)+’px’);»>Sub datetext() получается 0:00:00: 03.06.2013 0,375023148148148 нет: Добрый день, помогите
разделе «Другие статьи
=—(ТЕКСТ(A1;»ГГГГММДД»)) не смещалось при
а не записать привычном нам формате п.ч. нужно убрать конце даты стоит отследить изменения, произведенные
Нажимаем на ячейку В7. текста, т.д. Если установить в ячейке Format(cell, «00/00/00″) это делать вDim rCell Asromik85 времени, + тоже плз с макросом. по этой теме».
Копируем формулу по перемещении, после фильтра, прописью), воспользуемся функцией даты, п.ч. мы три символа (буквы).
буква «г» (12.02. ранее. Например, сотрудник На закладке «Формулы» формат чисел стоит формат даты. КакимиNext cell универсальном формате ISO, Range, t$: жаль но нужен выход, но хотелосьданный макрос преобразуетВ Excel можно
столбцу. Получилось так. при копировании, т.д. «ТЕКСТ» в Excel. перед вводом формулыВ строке «Новый 2000г). Как убрать без вашего ведома в разделе «Библиотека не верный, то способами вызвать диалоговоеEnd Sub т.е. «YYYYMMDD».For Each rCell
макрос( бы макрос только дату в несколькими способами посчитатьЕсли нужно убрать из — заголовок, строку,Функция «Текст» в
Преобразовать даты в текст (Формулы/Formulas)
настроили формат ячеек текст» ничего не букву «г» и изменил некоторые цифры. функций» в функциях формула считать не окно и сменитьА для 300Например: In SelectionMichael_SMichael_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 два Falselapink2000: макрос, можно формулой.
«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 = a200?’200px’:»+(this.scrollHeight+5)+’px’);»>Sub Текст() — коду специальности. и формулами удалить.как преобразовать число в
в формате «Дата». достаточно установить обычным
по формату «Дата», «г» в конце
Здесь рассмотрим, как так. У нас такая
и формат Rows.Count).End(xlUp))
строка) спасибо) ++++
(между
Next cell
For Each cell
Обнаружилось, что коды
Подробнее о способах
дату Нам нужно эти способом формат даты в который будем дат.
преобразовать в
Преобразовать текст в дату
Даты в столбце таблица с датами,значенияra.NumberFormat = «General»
Если же вамтему прошу закрыть)ГГГГ
MsgBox «готов» In Selection
воспринимается программой как копирования, смотрите вExcel
даты перевести в в ячейке. (Формат
преобразовывать это значение.В ячейке В1Excel текст в дату
B написаны как написанными порядковыми номерами., написанного в ячейке.ra.Value = Evaluate(«TEXT(« надо формулой, яРавильиEnd Scell.Formula = cell.Text даты, а не статье «Копирование в
и текст (формат текст). ячейки -> Дата). Нажимаем «ОК». вставляем формулу. Наи
текст (текстовый формат
Преобразуем порядковые номера дат
Формат ячейки & ra.Address(, ,
бы сделал так:: Здравствуйте, нужно преобразоватьчч
ubNext 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
Excel сохраняет даты в виде порядковых номеров. Человеку невозможно их читать и понимать, но, таким образом, можно использовать их в функциях расчета, к примеру добавить к дате или времени какое-то количество времени (день, год и так далее).
Но, довольно часто, вам может быть нужно использовать даты в одних ячейках с текстом, так сказать, объединять.
На картинке пример объединения даты с текстом в одной ячейке. Видите как выглядит наша дата? Это формат порядкового номера Excel.

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

Она принимает два аргумента:
- Значение — в нашем случае это дата, которую вы хотите преобразовать в текст нужного формата. Это может быть просто дата, ссылка на ячейку с датой, или результат формулы.
- Формат_текста — формат, в котором нужно показать нашу дату. Формат должен быть указан в двойных кавычках.
Какие есть форматы и разделители?
Формат дат состоит из 4 составляющих:
- день
- месяц
- год
- разделитель
Ниже приведены форматы, которые можно использовать для каждой части:
- Д — день будет отображаться без нуля, т.е. 1 будет отображаться как 1, а 12 как 12.
- ДД — день будет отображаться с нулем, т.е. 1 будет 01, а 12 как 12.
- ДДД — будет отображаться день недели(сокращенно), т.е. «Пятница» будет «Пят».
- ДДДД — будет отображаться день недели(полностью), т.е. «Пятница» будет «Пятница».
- М — месяц будет отображаться без нуля, т.е. 1 будет отображаться как 1, а 12 как 12.
- ММ — месяц будет отображаться с нулем, т.е. 1 будет 01, а 12 как 12.
- МММ — месяц будет отображаться с сокращенным названием, т.е. «Сентябрь» будет «Сен».
М — месяц будет отображаться без нуля, т.е. 1 будет отображаться как 1, а 12 как 12.
ММ — месяц будет отображаться с нулем, т.е. 1 будет 01, а 12 как 12.
МММ — месяц будет отображаться с сокращенным названием, т.е. «Сентябрь» будет «Сен».
ММММ — месяц будет отображаться с полным названием, т.е. «Сентябрь» будет «Сентябрь».
- ГГ — год будет отображаться как 2 цифры, т.е. 2019 будет 19.
- ГГГГ — год будет отображаться полностью, т.е. 2019 будет 2019.
- «/» : это просто разделитель между цифрами в дате, т.е. отображаться будет как 01/01/2019.
- «-»: такой же разделитель между цифрами, т.е. отображаться будет как 01-01-2019.
- Пробелы и запятые: тут немного посложней, вы можете комбинировать и создавать свои форматы отображения, например, 01 января, 2019 года.
Итак, разберем примеры.
Как привести указанную дату в текстовый формат

Итак, мы получили дату вместе с текстом, но дата сейчас в формате порядкового номера Excel.

Итак, мы вызвали функцию ТЕКСТ, указали ссылку на ячейку и определили формат, результат вы видите на картинке выше.
Какие еще есть форматы и как они выглядят на «выходе»:

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

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

- Выделите ячейки, которые необходимо преобразовать в текст;
- Перейдите в раздел «Данные» -> «Текст по столбцам»;
- В открывшемся окне:
- Выберите опцию «с разделителями»;
- Снимите все галочки и нажмите «Далее»;
- Выберите формат «текстовый» укажите куда поместить наши, уже текстовые, значения и нажмите «Готово».
Функция сразу же переводит даты в текстовый формат.

Важная информация: эта функция устанавливает формат для дат по умолчанию для настроек вашей системы. То есть если вы хотите записать даты в определенном формате вам нужно использовать способы рассмотренные ранее, но для начала дочитайте нашу статью, возможно следующий вариант подойдет вам.
Переводим дату в текстовый формат с помощью копирования и вставки
- Выделите ячейки с датами, которые нужно преобразовать, и скопируйте их;
- Откройте обычный блокнот и вставьте наши данные туда, как только вы это сделаете, данные сразу же потеряют формат, т.е. станут обычным текстом (потому что в блокноте нет никаких форматов данных, кроме текста);
- Выделите ячейки, куда вы будете вставлять даты и поменяйте их формат на «Текстовый»;
- А теперь, заново скопируйте даты из блокнота, а затем вставьте их в Excel.
Excel сохраняет даты в виде порядковых номеров. Человеку невозможно их читать и понимать, но, таким образом, можно использовать их в функциях расчета, к примеру добавить к дате или времени какое-то количество времени (день, год и так далее).
Но, довольно часто, вам может быть нужно использовать даты в одних ячейках с текстом, так сказать, объединять.
На картинке пример объединения даты с текстом в одной ячейке. Видите как выглядит наша дата? Это формат порядкового номера Excel.
5 способ копирования таблицы в Excel. Пошаговая инструкция с фото
Табличный редактор Excel предназначен для обработки массивов информации, представленных в виде таблиц всевозможных значений. Одним из наиболее частых видов такой обработки является копирование. Например, если имеется какой-то исходный массив данных, и необходимо сделать какие-то расчеты, требующие дополнительных столбцов или строк, не всегда удобно добавлять их прямо в исходную таблицу. Она может потребоваться и для других целей. Поэтому разумным решением будет скопировать все данные или их часть в новый лист или документ, и делать все преобразования с копией. Таким образом, исходный документ останется нетронутым. Какими способами можно это сделать?
Простое копирование без изменений
Этот способ наиболее прост в использовании, он удобен, если в исходной таблице представлены простые данные без формул и ссылок.
Обратите внимание! Простое копирование ничего не меняет в исходной информации.
Если исходная информация содержит формулы, они будут скопированы вместе с остальными данными, и здесь следует быть аккуратным – относительные ссылки при копировании начинают ссылаться на совсем другие ячейки, где могут быть расположены некорректные данные. Поэтому простое копирование данных с формулами предпочтительно только лишь в том случае, когда все источники ссылок для формул копируются одновременно. Такой метод включает следующие шаги.
- Выделение ячеек. Как правило, используется либо указание диапазона ячеек левой кнопкой мыши, либо комбинация клавиатуры «Shift+стрелка». В результате часть ячеек листа обводится черной рамкой, и они дополнительно подсвечиваются темным оттенком.
- Копирование в буфер обмена. Буфер обмена – это специальная область в памяти компьютера, предназначенная для перенесения данных внутри приложения или между приложениями. Копирование в нее воспроизводится либо нажатием клавиш «Ctrl+C», либо «Ctrl+Insert» (эти комбинации эквивалентны). Также его возможно осуществить через соответствующий пункт контекстного меню или с помощью ленты программы.
- Указание места для вставки. Переходим туда, куда мы хотим скопировать данные, и указываем курсором ячейку, которая будет верхней левой ячейкой вставляемых данных. Следует быть осторожным, если место для вставки уже содержит какие-то данные. Они могут быть стерты.
- Вставка содержимого буфера обмена в указанную область. Это делается клавишами «Ctrl+V» либо «Shift+Insert» либо соответствующим пунктом контекстного меню или ленты программы.
Если нужны только значения
Очень часто информация в ячейках является результатом вычислений, при которых используются ссылки на соседние ячейки. При простом копировании таких ячеек оно будет производиться вместе с формулами, и это изменит нужные значения.
В этом случае следует копировать только значения ячеек. Как и в прошлом варианте, сперва выбирается необходимый диапазон, но для копирования в буфер обмена используем пункт контекстного меню «параметры вставки», подпункт «только значения». Также можно использовать соответствующую группу в ленте программы. Остальные шаги по вставке скопированных данных остаются прежними. А в результате в новом месте появятся только значения нужных ячеек.
Важно! Формулы и форматы при таком способе не сохраняются.
Это может быть как удобством, так и помехой, в зависимости от ситуации. Чаще всего форматирование (особенно сложное) требуется оставить. В этом случае можно воспользоваться следующим способом.

Копирование только значений
Когда нужны и значения, и форматы
Выбор ячеек для такого способа копирования остается прежним, но оно осуществляется либо с помощью контекстного меню (пункт «Специальная вставка»), либо с помощью ленты программы. Нажатием на сам значок «Специальная вставка» можно открыть целую диалоговую панель, где предоставляется больше вариантов копирования, и появляется возможность даже комбинировать данные с помощью операций. Например, можно переносимые данные не просто вставить в указанные ячейки, а прибавить к тем, что уже имеются на листе. Иногда это очень удобно.
Также бывает, что таблица имеет большое количество столбцов различной ширины, и после копирования значений требуется много довольно кропотливой работы по установке нужных ширин. В этом случае в диалоге «Специальная вставка» есть специальный пункт «Ширина столбцов». Вставка при этом осуществляется в два этапа. Сперва вставляем только «ширину столбцов», чтобы «подготовить место», а затем копируем значения. Таблица получается точно такой же, как исходная, но вместо формул в ней остаются значения. Иногда удобно скопировать только ширину столбцов, чтобы таблица выглядела, как исходная, а значения вписывать в ячейки вручную. Кроме того, можно выбрать пункт «копирование с сохранением ширины столбцов» в контекстном меню. В результате вставка будет выполнена в один этап.

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

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

Полное копирование листа
Заключение
Копирование – это одно из самых востребованных действий в Excel. Для простых таблиц без формул наиболее удобен первый способ, а для таблиц, содержащих много формул и ссылок, как правило, предпочтительнее пользоваться вторым способом – копирование только значений. Прочие методы применяются реже.
Как скопировать дату в excel без изменений
Как скопировать числа или значения без формул в Excel?
Когда вы копируете числа, которые являются результатами вычислений формул, в другие ячейки, эти числа будут изменены после вставки, потому что обычно вы копируете эти формулы вместо их вычисленных результатов. Эта статья поможет вам скопировать рассчитанные результаты формул без изменения значений в Excel.
With Kutools for Excel‘s Exact Copy utility, you can quickly copy and paste the formulas into any cells without changing the calculated results.

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!
Копирование чисел или значений без копирования формул в ячейки с помощью специальной команды «Вставить»
Если вы просто хотите скопировать вычисленные результаты, не копируя их формулы, вы можете использовать команду «Специальная вставка», как показано ниже, для копирования чисел без копирования формул, но она очистит все формулы и форматирование.
1. Выделите и скопируйте числа, которые вы хотите вставить без формул.
2. Выберите ячейку и щелкните ее правой кнопкой мыши. Затем нажмите кнопку Ценности вариант под Вставить вариант в контекстном меню, см. следующий снимок экрана:

3. После этого вы увидите, что все числа вставлены в ячейки назначения без формул.

Примечание: Специальная вставка команда очищает не только формулы, но и форматирование, например полужирный, цвет и т. д.
Копируйте числа или значения без копирования формул в ячейки с помощью Kutools for Excel
С помощью вышеуказанного метода вы можете потерять форматирование ячейки, если вы хотите вставить без формул, но сохранить форматирование ячейки, Kutools for ExcelАвтора К фактическому утилита может оказать вам услугу.
После установки Kutools for Excel, пожалуйста, сделайте следующее:
1. Скопируйте и вставьте значения ячеек и формулы в другое нужное место.
2. Затем выберите вставленные данные, из которых вы хотите удалить формулы, и нажмите Кутулс > К фактическому, см. снимок экрана:

3. И все значения и формулы в диапазоне будут изменены на фактические значения без формул, форматирование ячеек также сохраняется, см. Снимок экрана:

Нажмите здесь, чтобы узнать больше о К фактическому.
Копируйте числа или формулы без изменения ссылок на ячейки с помощью Kutools for Excel
Если вы хотите скопировать как вычисленные результаты, так и формулы в другие ячейки, не меняя их значений, Kutools for Excel‘s Точная копия Инструмент может помочь вам быстро скопировать как числа, так и их формулы, не изменяя их значения в других ячейках Excel.
После установки Kutools for Excel, пожалуйста, сделайте следующее:
1. Нажмите Кутулс > Точная копия, см. снимок экрана:

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

3, Затем нажмите Ok , и появится другое окно подсказки, напоминающее вам о выборе ячейки для вставки результата. Смотрите скриншот:

4. Затем нажмите OK , вы увидите, что числа и формулы из выбранных ячеек копируются в целевую ячейку без изменения их значений.
Kutools for ExcelИнструмент Exact Copy позволяет легко копировать все формулы без изменения относительных ссылок на ячейки. Нажмите, чтобы узнать больше…
Как в Excel копировать значение из каждой n-й строки

В этом учебном материале по Excel мы рассмотрим примеры того, как скопировать значение из каждой n-й строки.
Основная формула
Описание
Для копирования значений или создания ссылок с шаблоном, например, каждая 3-я строка, каждая 7-я строка и т.д., Вы можете использовать формулу, основанную на функциях СМЕЩ и СТРОКА. В показанном примере формула в D5:
Что можно скопировать в столбец D, чтобы получить n-е значения из столбца B.
Пояснение
В Excel вы не можете легко скопировать формулы, которые должны пропускать строки или пропускать столбцы по определенному шаблону, потому что ссылки в формуле автоматически изменятся в соответствии с отношениями между исходной ячейкой и новой целевой ячейкой. Однако, приложив немного усилий, можно создать ссылки на формулы, которые следуют определенным шаблонам.
В этом случае мы используем функцию СМЕЩ, которая предназначена для создания ссылок на другие ячейки или диапазоны ячеек на основе заданной начальной точки или источника.
В показанном примере начальная ячейка — B5, предоставленная СМЕЩ в качестве аргумента ссылки и фиксированная как абсолютная ссылка, поэтому она не изменится при копировании формулы:
Чтобы вычислить правильное значение для аргумента строк, мы используем подформулу, основанную на функции СТРОКА:
Как преобразовать текст в дату в Excel или извлечь ее из сложной фразы?
Сейчас вы научитесь использовать функции Excel для преобразования текста в дату. Вы также узнаете, как это сделать без использования формул.
Поскольку Excel — не единственное приложение, которое вы используете, иногда вы работаете с данными, импортированными из файла .csv или другого внешнего источника (1С, например). Когда это произойдет, скорее всего, даты будут вставлены в вашу таблицу в виде текстовых записей. Хотя они внешне выглядят как даты, Excel не распознает их как таковые.
И вот о чём мы поговорим:
Существует много способов, как в Excel преобразовать текст в дату, и это небольшое руководство должно вам помочь выбрать метод, наиболее подходящий для вашего формата данных и предпочтений работы — с формулами или без них.
Как отличить обычные даты Excel от «текстовых дат»
Импортированные данные (или данные, введенные неправильно) могут выглядеть как обычные даты Excel, но они не ведут себя так, как выглядят. Microsoft Excel обрабатывает такие записи как текст. Поэтому вы не сможете правильно отсортировать таблицу в хронологическом порядке, а также использовать эти «неправильные даты» в формулах, сводных таблицах, диаграммах или любом другом инструменте Excel, который работает с временем.
Сначала давайте изучим несколько признаков, которые могут помочь определить, записана в ячейке датировка либо текст.
• По умолчанию выравнивание по правому краю.
• Можно выбрать различные форматы даты и изменить вид.
• Если выбрано несколько дат, в строке состояния отображается «Среднее», «Количество» и «Сумма».
· • По умолчанию выравнивание по левому краю.
• По умолчанию установлен формат «Общий». При изменении формата вид не меняется.
• Если выбраны несколько ячеек, строка состояния показывает только «Количество».
• В строке формул может быть виден начальный апостроф.
Их можно легко распознать, немного расширив столбцы, выделив один из них, выбрав команду Формат ► Ячейки ► Выравнивание (Format ► Cells ► Alignment) и для параметра По горизонтали (Horizontal) выбрав значение Общий (General) (это вид ячеек по умолчанию). Щелкните кнопку ОК и внимательно просмотрите на таблицу.

Поскольку все функции, которые изменяют текст на дату, в результате возвращают число, давайте сначала более подробно рассмотрим преобразование чисел в даты.
Как вы, наверное, знаете, Excel хранит дату и время как порядковые номера дней, и только форматирование ячейки заставляет число отображаться в виде день-месяц-год. Например, 1 января 1900 года сохраняется как номер 1, 2 января 1900 года сохраняется как 2, а 1 января 2015 года сохраняется как 42005. Дополнительные сведения о том, как Excel сохраняет дату и время, см. в статье Форматирование дат.
При вычислении дат в Excel результатом, возвращаемым различными функциями, часто является число, обозначающее дату. Например, если СЕГОДНЯ()+10 возвращает вместо дня, который наступает через 10 после сегодняшнего, число вроде 44294, это не означает, что формула неверна. Просто формат ячейки установлен на Общий или Текст, тогда как это должно быть Дата.
Чтобы преобразовать такой порядковый номер в день-месяц-год, все, что вам нужно сделать, это изменить формат представления. Для этого просто выберите «Дата» в поле «Числовой формат» на вкладке «Главная».
Чтобы применить нестандартный формат, отличный от формата по умолчанию, выберите ячейки с такими числами и нажмите комбинацию клавиш Ctrl + 1, чтобы открыть диалоговое окно «Формат ячеек». На вкладке «Число» выберите «Дата», укажите нужный формат даты в разделе «Тип» и нажмите «ОК».

Если вам нужно что-то более сложное, чем стандартные форматы даты, ознакомьтесь с информацией о том, как создать собственный формат даты в Excel.
Если какое-то упрямое число отказывается заменяться датой, проверьте, почему не работает формат даты — советы по устранению неполадок .
Когда возникает подобная проблема, скорее всего, вы захотите перевести эти текстовые значения в обычные даты Excel, чтобы вы могли ссылаться на них в формулах для выполнения различных вычислений. И, как это часто бывает в Экселе, есть несколько способов решения этой задачи.
Функция Excel ДАТАЗНАЧ — изменить текст на дату
На скриншоте представлено несколько формул ДАТАЗНАЧ в действии:

На первый взгляд, результат работы может выглядеть для вас довольно неожиданным. Вы его видите на скриншоте в колонке С. Но пусть это вас не смущает: нужно просто применить нужное форматирование. Как видите, результат работы формулы – это порядковый номер дня начиная с 1900 года (столбец С). И если мы к нему применим правильный формат, то есть укажем программе, что это на самом деле не число, а день, то сразу же получим приемлемый результат (колонка D).
Итак, в столбце D показан результат из С, только уже как дата.
Как вы, наверное, знаете, Excel хранит датировки и время как порядковые номера, и только форматирование ячейки заставляет число отображаться в виде даты. В частности, 1 января 1900 года хранится как число 1, а 2 января 1900 года хранится как 2. Соответственно, 8 марта 2020 года хранится как 43898. Чтобы превратить такой серийный номер в дату, все, что вам нужно сделать — это изменить формат ячейки.
Для этого выберите клетку или диапазон ячеек с числами, которые вы хотите представить как даты, и нажмите комбинацию клавиш Ctrl + 1, чтобы открыть диалоговое окно «Формат ячеек». На вкладке «Числовые форматы» в пункте «Дата», выберите подходящий для вас внешний вид в разделе «Тип» и нажмите кнопку «ОК».
Обратите также внимание, что не с любым текстом можно провести подобную операцию. Если Эксель не может с ним справиться, то возвращает ошибку #ЗНАЧ! (см. метку 3 на рисунке выше)
Кроме того, если по какой-то причине у вас вместо точек использованы запятые, то при помощи функции ПОДСТАВИТЬ сразу замените их на точки – и всё будет в порядке.
Функция ДАТАЗНАЧ — что нужно запомнить?
При конвертации текстовой строки в дату имейте в виду, что:
- Информация о времени в текстовых значениях игнорируется, как вы можете видеть в строках 2 и 6 на рисунке выше (1). Чтобы превратить такие значения, содержащие даты и время, используйте функцию ЗНАЧЕН.
- Если в «текстовой дате» пропущен год, ДАТАЗНАЧ подставит текущий год из системного времени вашего компьютера, как показано в строке 3 выше (2).
- Поскольку Microsoft Excel начинает свой отсчет времени только с 1 января 1900 года, использование ДАТАЗНАЧ с более ранним возрастом приведет к ошибке #ЗНАЧ!.
- Любое значение, связанное со временем, хранится в Excel как число. Но ДАТАЗНАЧ не может превратить числовое значение в показатель времени и не может обработать текст, который выглядит как число. Для этого вам нужно будет использовать функцию ЗНАЧЕН. И это именно то, что мы собираемся обсудить далее.
Функция ЗНАЧЕН – как конвертировать текст в дату.
По сравнению с ДАТАЗНАЧ, функция ЗНАЧЕН более универсальна. Она может конвертировать любую строку, которая выглядит как дата или число, в настоящее число, которое затем вы можете легко вывести как дату.
Синтаксис здесь следующий:
Где текст – это строка или ссылка на ячейку, содержащую символы, которые вы хотите превратить в число.
Функция ЗНАЧЕН() может обрабатывать как дату, так и время. Последнее преобразуется в десятичную часть числа, как вы можете видеть в строке 11 на следующем скриншоте:

Как видите, результаты те же, что и в примере выше. Только ещё и время определено и обработано правильно.
Однако, к сожалению, такие данные, пригодные для обработки функцией ЗНАЧЕН(), встречаются на практике не слишком часто. Разработчики различного программного обеспечения стремятся проявить фантазию, записывая в свои отчеты дату и время операции. И когда эти отчеты мы хотим обработать в Excel, возникает множество проблем.
Остановимся на наиболее типичных из них и попробуем показать пути решения.
Математические операции для преобразования текста в дату
Помимо использования функций Excel, о которых мы говорили чуть выше, вы можете выполнить простую математическую операцию, чтобы заставить программу выполнить реорганизацию строки в дату. Обязательное условие: операция не должна изменять ее значение (порядковый номер дня). Звучит немного сложно? Следующие примеры помогут разобраться!
Предполагая, что ваши данные находятся в ячейке A1, вы можете использовать любую из следующих формул, а затем применить формат даты к ячейке:
- Сложение: =A1 + 0
- Умножение: =A1 * 1
- Деление: =A1 / 1
- Двойное отрицание: =—A1
Как вы можете убедиться, математические операции могут помочь с датами (строки 3,4.,5,7), временем (строки 2 и 6), а также числами, отформатированными как текст (строка 8).
Иногда результат даже отображается в виде даты автоматически, и вам не нужно беспокоиться об изменении формата ячейки.
Как обойти нестандартные разделители
Если запись содержит какой-либо разделитель, отличный от косой черты (/) или тире (-), функции Excel не смогут распознать их как даты и вернут ошибку #ЗНАЧ!. Чаще всего такие «неправильные» разделители – это пробел и запятая.
Чтобы это исправить, вы можете запустить инструмент поиска и замены, чтобы заменить этот неподходящий разделитель, к примеру, косой чертой (/):
- Выберите все ячейки, которые вы хотите превратить в даты.
- Нажмите Ctrl + H, чтобы открыть диалоговое окно «Найти и заменить».
- Введите свой пользовательский разделитель (запятую, к примеру) в поле Найти и косую черту в Заменить.
- Нажмите Заменить все.
Теперь у ДАТАЗНАЧ или ЗНАЧЕН должно быть проблем с конвертацией текстовых строк в даты. Таким же образом вы можете исправить записи, содержащие любой другой разделитель, например, пробел или обратную косую черту.
Если вы предпочитаете решение на основе формул, вы можете использовать функцию ПОДСТАВИТЬ (SUBSTITUTE в английской версии), как это мы делали на одном из скриншотов ранее:
И текстовые строки преобразуются в даты, все при помощи одной формулы.

Как видите, функции ДАТАЗНАЧ или ЗНАЧЕН довольно мощные, но они, к сожалению, имеют свои ограничения. Например, если вы пытаетесь работать со сложными конструкциями, такими как четверг, 01 января 2021 г., ни одна из них не сможет помочь.
К счастью, есть решение без формул, которое может справиться с этой задачей, и следующий раздел даст нам пошаговое руководство.
Мастер преобразования текста в столбцы — не требующий формул способ преобразования текста в дату.
Вам пригодится достаточно древний инструмент Excel под названием «Текст в столбцы». Он умеет работать как с простыми текстовыми датами, так и с текстовыми строками из нескольких частей.
Пример 1. Преобразование простых текстовых строк в даты.
В этом примере мы будем превращать текстовые строки, например 01 01 2015 (день, месяц и год разделены пробелами), в даты.
- На рабочем листе выберите столбец, содержимое которого вы хотите конвертировать.
- Перейдите на вкладку «Данные», и нажмите «Текст по столбцам».
- На шаге 1 мастера преобразования текста в столбцы выберите «Сразделителями» и нажмите «Далее».
- На шаге 2 мастера снимите флажки со всех разделителей и нажмите Далее.
- На последнем шаге выберите «Дата» в разделе «Формат данных столбца», укажите в выпадающем списке вид, соответствующий вашим данным. Нажмите «Готово».
Теперь Excel распознает ваши текстовые значения как нужно, автоматически преобразует их в формат даты по умолчанию и отобразит в ячейках с выравниванием по правому краю.
Вы можете изменить представление результата обычным способом через диалог Формат ячеек.
Заметка. Для правильной работы мастера «Текст по столбцам» все записи должны быть единообразными. Например, если некоторые из них выглядят как день / месяц / год , а другие — месяц / день / год , вы получите неверные результаты.
Пример 2. Преобразование сложных текстовых строк в даты.
Если ваши записи представлены более сложной фразой из нескольких частей, к примеру:
- 8 март 2020 г., воскресенье
- 8 март 2020 г. 15:00
Вам нужно будет приложить немного больше усилий и использовать рассматриваемый нами инструмент в комбинации с функцией ДАТА.
Важно! Название месяца при использовании этого метода должно быть в именительном падеже!
- Выберите нужную область данных.
- Вызовите мастер преобразования.
- На шаге 1 укажите «Сразделителями» и нажмите « Далее» .
- На шаге 2 мастера выберите разделители, которые у вас используются.
Например, если вы преобразуете строки, разделенные запятыми и пробелами, например «8 март 2020г., воскресенье», то вам следует выбрать оба разделителя — запятую и пробел.
Также имеет смысл выбрать опцию «Считать последовательные разделители одним», чтобы игнорировать лишние пробелы или пробел после запятой, если они есть в ваших данных.
Я немного усложнил наш пример, добавив после порядкового номера года еще букву «г» с точкой. Поэтому эта буква и добавлена в список разделителей.
И, наконец, взгляните на окно Образец разбора данных и проверьте, правильно ли поделены ваши слова на столбцы. Затем нажмите «Далее».
- На шаге 3 мастера убедитесь, что все колонки в разделе предварительного просмотра данных имеют формат «Общий». Если это не так, щелкните столбец и выберите Общий в разделе Формат данных.
Заметка. Не выбирайте формат «Дата», поскольку каждый столбец содержит только ее часть. Поэтому Excel не сможет понять, что вы на самом деле хотите сделать.
Если вам не нужен какой-то из столбцов, щелкните по нему и выберите «Пропустить».
Как видите, я буду использовать только три первых столбца, а два последних не нужны.
Если вы не хотите перезаписывать исходные данные, укажите, где именно должны быть вставлены результаты — введите адрес верхней левой ячейки в поле «Поместить в…». Выбираем ячейку B2.
Когда закончите, нажмите кнопку Готово.
Как вы видите на скриншоте выше, мы пропускаем четвертую и пятую колонки с точкой и днями недели, разделяем остальные данные на 3 столбца (в общем формате) и вставляем их, начиная с ячейки В2.
На следующем рисунке показан результат с исходными данными в A и разделенными на части данными в В, C и D.
- Наконец, вы должны объединить эти кусочки вместе, используя функцию ДАТА (DATE). Синтаксис её не требует особых пояснений:
В нашем случае год записан в колонке D и день — в В, с этим проблем нет.
Но не так просто с месяцем, потому что это слово, а нужен его номер. К счастью, Microsoft Excel предоставляет специальную функцию МЕСЯЦ (MONTH), которая может изменить название месяца на его порядковый номер:
Чтобы функция МЕСЯЦ понимала, что она имеет дело с датой, мы выражаем это так:
Здесь C2 содержит название месяца, март в нашем случае. «1 &» добавляется для получения даты (1 март), чтобы формула могла превратить ее в соответствующий номер месяца. Повторюсь еще раз, что месяц должен быть здесь в именительном падеже, что не всегда удобно.
А теперь давайте встроим это выражение в качестве второго аргумента:
И вуаля, наши сложные комбинации слов и цифр успешно становятся датами:
Как преобразовать текст в дату при помощи формул.
Разберем еще один способ как превратить сложную формулировку в дату. В колонке А записаны датировки как текстовые выражения вида «08 марта 2020г.». Сразу отметим, что записи должны быть единообразны, то есть все числа сохранены в двузначном виде: 01, 07, 08, 10, 12 и т.д.
Используем формулы работы с символьными значениями — ЛЕВСИМВ, ПРАВСИМВ, ПСТР. Они позволяют извлечь определенное количество символов с начала текстовой строки, с конца или с середины.
Наша задача – разобрать текст на части, из которых затем «склеить» дату.
Давайте разбираться, как это работает.
ЛЕВСИМВ(A2;2) — извлекаем 2 первых символа, то есть число.
ПСТР(ЛЕВСИМВ(A2;6);4;3) — получаем 3 первых буквы названия месяца.
ПСТР(ПРАВСИМВ(A2;6);1;4) — выводим 4 цифры года.
И объединяем все это при помощи оператора &, вставляя между этими частями тире.
Можно записать и чуть иначе:
В английской версии:
Как извлечь дату из сложного текста при помощи формул?
При переносе в Excel различных бухгалтерских данных мы постоянно убеждаемся в том, что фантазия наших бухгалтеров поистине безгранична. В том числе это касается и написания датировок в пояснениях к бухгалтерским проводкам.
Вот несколько готовых решений, которые вам, возможно, пригодятся.
Давайте попробуем вытащить дату из пояснения к бухгалтерским проводкам. Как видите, варианты написания могут быть самые разнообразные. Единственное условие – день должен быть записан в виде двузначного числа: 07 в нашем случае.
Формула, при помощи которой можно извлечь дату из текста, выглядит так:
- если у вас двумя цифрами обозначены день и месяц, и четырьмя – год:
- а вот если год – либо последние две, либо все четыре цифры, тогда есть более универсальный вариант:
Как должен выглядеть результат в ячейке — определите самостоятельно. Как именно – мы уже несколько раз разбирали. Смотрите чуть выше.
А если сделать так, чтобы и внешний вид извлеченной даты также устанавливался не вручную, а при помощи формулы? Здесь нам поможет функция ТЕКСТ.
Самый базовый вариант представления выглядит так:
Как видите, вся хитрость заключается в правильном описании формата во втором аргументе. «DD MMMM YYYY» означает число, месяц в именительном падеже и год из четырёх цифр. А вот «[$-FC19]DD MMMM YYYY» сделает то же самое, но месяц будет в родительном падеже (7 ноября). Остальные варианты вы видите на рисунке выше. Можете сами выбрать тот, который больше подходит для ваших задач.
Ну а если день у вас может быть записан и одной цифрой, и двузначным числом? И здесь есть универсальный вариант. Правда, более сложный.
Здесь применяем формулу массива.
Естественно, скобки вводить не нужно: они появятся автоматически после того, как вы в конце ввода формулы нажмете комбинацию клавиш CRTR+SHIFT+ENTER.
Как видите, здесь мы постарались охватить практически все возможные варианты написания. Внешний вид ячеек с результатом вам нужно вновь установить вручную. Ну а если необходимо это сделать автоматически, то вновь используйте функцию ТЕКСТ, как это сделано чуть выше.
К примеру, чтобы сразу получить «07 ноября 2019 г.», можно изменить наше выражение следующим образом:
И не забывайте про формулу массива!
Быстрая конвертация при помощи Специальной вставки.
Чтобы быстро переделать диапазон простых текстовых строк в даты, вы можете использовать следующую хитрость.
- Скопируйте любую пустую ячейку (выделите ее и нажмите Ctrl + C).
- Выберите диапазон с текстовыми значениями, которые вы хотите превратить в даты.
- Щелкните правой кнопкой мыши выделенную область, выберите «Специальная вставка» и выберите «Сложить» в диалоговом окне:
- Нажмите кнопку ОК, чтобы завершить работу и закрыть диалоговое окно.
Что вы только что сделали, это попросили Эксель добавить ноль (пустую ячейку) к вашим текстовым датам. Чтобы выполнить это, программа конвертирует текст в число, и, поскольку добавление нуля не приводит к изменению значения, вы получаете именно то, что хотели — порядковый номер дня.
Как и обычно, далее вы устанавливаете для этих ячеек нужный вид с помощью диалогового окна «Формат ячеек».
Исправление записей с двузначными годами.
Современные версии Microsoft Excel достаточно умны, чтобы обнаружить некоторые очевидные ошибки в ваших данных, или, точнее, сказать, что Эксель считает ошибкой. Когда это произойдет, вы увидите индикатор ошибки (маленький зеленый треугольник) в верхнем левом углу клетки, и, когда вы выделите её, появится восклицательный знак.
При нажатии на восклицательный знак отобразятся несколько параметров, относящихся к вашим данным. В случае двухзначного года программа спросит, хотите ли вы преобразовать его в 19XX или 20XX.
Если у вас имеется несколько записей этого типа, вы можете исправить их все одним махом — выделите все ячейки с ошибками, затем нажмите на восклицательный знак и выберите соответствующую опцию.
Как преобразовать 8-значное число в дату в Excel
Это очень распространенная ситуация, когда дата вводится как 8-значное число, например 10032021. Вам необходимо преобразовать ее в такой вид, который может распознать Excel (то есть, 20.03.2021). В этом случае простое изменение формата ячейки на «Дата» не пройдёт – в результате вы получите ##########.
Чтобы преобразовать это в дату, вам нужно будет использовать функцию ДАТА() в сочетании с функциями ПРАВСИМВ(), ЛЕВСИМВ() и ПСТР(). К сожалению, невозможно составить универсальную формулу, которая будет работать во всех сценариях, потому что исходное значение может быть введено самыми разными способами. Например:
| Число | Формат | Дата |
| 20032021 | ддммгггг | 20-мар-2021 |
| 20210320 | ггггммдд | |
| 20212003 | ггггддмм |
Я постараюсь объяснить общий подход к преобразованию таких чисел в даты и приведу несколько примеров формул.
Для начала запомните порядок аргументов функции ДАТА():
=ДАТА(год; месяц; день)
Итак, что вам нужно сделать, так это извлечь год, месяц и день из исходного числа и передать их в качестве соответствующих аргументов функции ДАТА().
Например, давайте посмотрим, как преобразовать число 20032021 (хранящееся в ячейке A2) в 20.03.2021.
- Извлекаем год. Это последние 4 цифры, поэтому мы используем функцию ПРАВСИМВ, чтобы выбрать последние 4 символа: ПРАВСИМВ(A2; 4).
- Месяц. Это третья и четвёртая цифры, поэтому мы используем функцию ПСТР, чтобы получить их: ПСТР(A2;3;2). Здесь цифра 3 (второй аргумент) – это начальная позиция, а 2 – это количество символов, которые нужно извлечь.
- День. Это первые две цифры, поэтому у нас есть функция ЛЕВСИМВ, которая возвращает их: ЛЕВСИМВ(A2;2).
Наконец, вставьте указанные выше элементы в функцию ДАТА(), и вы получите формулу для преобразования числа в дату в Excel:
Следующий скриншот демонстрирует это и еще пару выражений в действии:
Обратите внимание на строку 5 на скриншоте выше:
Исходное число (212003) содержит только два символа, обозначающих год (21). Итак, чтобы получить 2021 год, мы объединяем 20 и 21, используя следующую формулу:
Если вы не сделаете этого, функция ДАТА() возвратит 1921 год, что немного странно, как будто Microsoft все еще живет в 20-м веке 🙂
В том случае, если число представляет собой последовательность ДДММГГГ или ГГГММДД, то для конвертации в дату можно использовать функцию ТЕКСТ().
К примеру, число 20210320 можно преобразовать при помощи формулы
Сначала в нужных местах мы вставляем разделители — наклонную черту. Получаем текст 2021/03/20. А затем производим с ним математическую операцию: дважды умножаем на минус 1. Видя операцию умножения, Excel пытается превратить содержимое ячейки в число. И ему это успешно удается: получаем 44275. Осталось применить подходящий формат даты.
Примечание. Формулы, показанные в этом примере, работают правильно, если все числа, которые вы хотите преобразовать в даты, подчиняются одному и тому же определённому шаблону.
Простой способ преобразования текста в дату без формул.
Как видите, преобразование текста в дату в Excel — далеко не тривиальная операция, выполняемая одним щелчком мыши. Если вас смущают достаточно сложные формулы, позвольте мне показать вам быстрый и простой способ.
Установите надстройку Ultimate Suite (бесплатную пробную версию можно скачать здесь), переключитесь на вкладку AblebitsTools (в ваш Excel будут добавлены 2 новые вкладки, содержащие более 60 очень полезных инструментов) и активируйте инструмент Text to Datе.
Чтобы преобразовать «дату-как-текст» в обычную дату, выполните следующие действия:
- Выделите ячейки с текстовыми данными и нажмите TexttoDate .
- Укажите порядок дат (дни, месяцы и годы) в выбранных ячейках.
- Выберите, нужно ли включать время в преобразованные даты.
- Щелкните Преобразовать(Convert).
Вот и все! Результаты конвертации появятся в соседнем столбце, ваши исходные данные сохранятся. Если что-то пойдет не так, вы можете просто удалить их и повторить попытку с другим порядком дат.
А вот пример конвертации текста в дату и время:
Порядок действий тот же, но нужно не забыть снять галочку, чтобы не исключать время из расчётов.
Подсказка. Если вы выбрали преобразование даты и времени, но единицы времени отсутствуют в результатах, обязательно примените в ячейке формат, в котором отображаются как значения даты, так и времени. Дополнительные сведения см. в статье «Как создавать собственные форматы даты и времени» .
Если вам интересно узнать больше об этом замечательном инструменте, посетите его домашнюю страницу.
Итак, мы с вами рассмотрели как в Excel можно различными способами преобразовать текст в дату. Надеюсь, вам удалось найти метод по своему вкусу.
Я благодарю вас за чтение и надеюсь вновь увидеть вас на нашем сайте.
Как перевести время в число — В статье рассмотрены различные способы преобразования времени в десятичное число в Excel. Вы найдете множество формул для преобразования времени в часы, минуты или секунды. Поскольку Microsoft Excel использует числовую систему для работы с временем, вы можете…
Как посчитать время – разницу, сумму, добавить или вычесть — В статье рассматривается, как в Excel посчитать время. Вы найдете несколько полезных формул чтобы посчитать сумму времени, разницу во времени или сколько времени прошло и многое другое. Ранее мы внимательно рассмотрели…
Формат времени в Excel — Вы узнаете об особенностях формата времени Excel, как записать его в часах, минутах или секундах, как перевести в число или текст, а также о том, как добавить время с помощью…
СЦЕПИТЬ в Excel: как объединять текстовые строки, ячейки и столбцы — В этом руководстве вы узнаете о различных способах объединения текстовых значений, ячеек, диапазонов, столбцов и строк в Excel с помощью функций СЦЕПИТЬ, СЦЕП и оператора «&» . В ваших файлах Excel…
Как быстро посчитать количество слов в Excel — В статье объясняется, как подсчитывать слова в Excel с помощью функции ДЛСТР в сочетании с другими функциями Excel, а также приводятся формулы для подсчета общего количества или конкретных слов в…
Как быстро извлечь число из текста в Excel — В этом кратком руководстве показано, как можно быстро извлекать число из различных текстовых выражений в Excel с помощью формул или специального инструмента «Извлечь». Проблема выделения числа из текста возникает достаточно…
Как вывести месяц из даты — На примерах мы покажем, как получить месяц из даты в таблицах Excel, преобразовать число в его название и наоборот, а также многое другое. Думаю, вы уже знаете, что дата в…
Функция ПРАВСИМВ в Excel — примеры и советы. — В последних нескольких статьях мы обсуждали различные текстовые функции. Сегодня наше внимание сосредоточено на ПРАВСИМВ (RIGHT в английской версии), которая предназначена для возврата указанного количества символов из крайней правой части…
Функция ЛЕВСИМВ в Excel. Примеры использования и советы. — В руководстве показано, как использовать функцию ЛЕВСИМВ (LEFT) в Excel, чтобы получить подстроку из начала текстовой строки, извлечь текст перед определенным символом, заставить формулу возвращать число и многое другое. Среди…
Как извлечь текст из ячейки при помощи функции ПСТР и специальных инструментов — ПСТР — одна из текстовых функций, которые Microsoft Excel предоставляет для управления текстовыми строками. На самом базовом уровне она используется для извлечения подстроки из середины текста. В этом руководстве мы обсудим…