Как сделать из текста дату excel?

В этом уроке расскажу как сцепить дату и текст в Excel. Допустим, у вас есть несколько ячеек, одна из которой дата. Необходимо получиться ячейку, в которой будет храниться запись «Договор №150 от 28.12.2015» при условии, что вид документа, его номер и дата будут храниться в разных ячейках.

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

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

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

• Первый — ячейка со значением, которое необходимо преобразовать;
• Второй — формат, в котором преобразованные данные необходимо вывести.

Если с первым все понятно, то второй можно использовать по разному. В нашем примере формат будет «ДД.ММ.ГГГГ»- это говорит о том, что при выводе информации день и месяц надо вывести в виде двух чисел, а год в виде четырехзначного числа. Пример, 01.02.2015. Конечно в вашем случае это может быть другой формат.

Вся двойная функция будет выглядеть следующим образом:=СЦЕПИТЬ(A2;» №»;B2;» от «;ТЕКСТ(C2;»ДД.ММ.ГГГГ»))

Только в этом случае получиться нужным нам результат.

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

Здесь, в блоке «Рекомендации по форматам даты и времени», вы найдете все возможные выводы даты и времени.

Не забудьте поделиться ссылкой на статью ⇒

В этом уроке расскажу как поставить в начале ячейки знак плюс или ноль перед числом в Excel. Давайте представим ситацию, что вам необходимо ввести в ячейку номер телефона в формате «+7 987…». В обычном состоянии программа Эксель просто удалит этот знак плюс.

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

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

В английской версии эта функция называется VLOOKUP — расшифровывается как вертикальный просмотр. Существует также функция ГПР, которая ориентирована на горизонтальный просмотр. В основном функция ВПР используют для того, чтобы подтянуть данные из одной таблицы в другую, также ее можно использовать для сравнения столбцов в двух разных таблицах.

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

Сейчас приведу пример. Допустим, у вас есть ячейка «A», в которой находится имя, фамилия и отчество.

Вам необходимо сделать так, чтобы в первой ячейке «A» была только фамилия, в ячейке «B» — имя, ну и в ячейке «C» отчество.

Источник: https://4upc.ru/materials/show/kak-scepit-datu-i-tekst-v-excel

Преобразование даты — Формулы — ExcelPedia.ru

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

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

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

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

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

= ДАТАЗНАЧ (ЛЕВСИМВ(B5;10))

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

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

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

= C5 + D5

Чтобы получить дату, мы извлекаем первые 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 года вы можете увидеть:

1721 // ГГД

201721 // ГГГГ

2017021 // ГГГГДДД

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

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

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

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

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

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

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

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

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

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

ГОД (B5)

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

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

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

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

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

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

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

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

= ДАТА (2016;1; A1)

Где 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. Функция ДАТА требует действительных значений года, месяца и дня, поэтому они анализируются из исходной текстовой строки следующим образом:
• Значение года извлекается с помощью функции ПРАВСИМВ:

ПРАВСИМВ(B5;2) +2000

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

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

ПСТР(B5;4;2)

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

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

ЛЕВСИМВ(B5;2)

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

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

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

1. Хотя месяц и день предоставляются в виде текста, функция ДАТА автоматически преобразуется в числа и возвращает действительную дату.
2. Примечание: значение 2016 года автоматически было преобразовано в число при добавлении 2000.
3. Если исходное текстовое значение содержит дополнительные начальные или конечные символы пробела, вы можете добавить функцию СЖПРОБЕЛЫ для удаления:

= ДАТА(ПРАВСИМВ (СЖПРОБЕЛЫ (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 + 0

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

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

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

Это иногда может исправить все сразу.

Источник: https://excelpedia.ru/formuli-v-excel/preobrazovanie-daty

Функции времени ГОД, МЕСЯЦ, ДЕНЬ в Excel

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

     В этой статье мы поговорим о трёх основных функций дат, это функция ГОД, функция МЕСЯЦ и функция ДЕНЬ в Excel. Это три составляющие любой даты и теперь рассмотрим, как с ними работать и для чего это нам надо.

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

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

     Теперь давайте рассмотрим каждую из функций времени по отдельности:

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

     Основная обязанность, которую выполняет функция ГОД в Excel, является возврат года, который будет соответствовать указанной дате. Сам год будет определять функция ГОД как целое число в диапазоне от 1900 года до 9999 года.

     Синтаксис, который использует функция ГОД в Excel очень простой:      =ГОД(указаная_дата_в_числовом_формате), где:

• Дата_в_числовом_формате, является обязательным аргументом и представляет собой дату, год которой следует вычислить. Даты нужно вводить при помощи функции ДАТА, указывать ссылкой на ячейку, которая содержит дату в соответствующем формате или это будет результат отдельных расчётов иных функций и формул. Важно! В случае, когда дата будет введена как текст, это приведёт к ошибкам и проблемам.

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

     Все даты MS Excel хранит как последовательные числа и именно это позволяет нам работать с ними и использовать в своих вычислениях. По умолчанию первая дата под номером 1, это 1 января 1900 года, а вот, к примеру, 1 января 2018 года будет предоставлен числом 43101, так разница между этими двумя датами будет составлять 43101 день.

Функция МЕСЯЦ в Excel

     Главная особенность, как используется функция МЕСЯЦ в Excel, это возврат значения месяца из даты, который заданный как числовой формат. Месяц будет возвращен функцией как целое число в диапазоне чисел от 1 до 12, что соответствует месяцам от января до декабря.

     Синтаксис, который использует функция МЕСЯЦ в Excel, простой и незатейливый:     =МЕСЯЦ(ваша_дата_в_числовом_формате), где:

• Дата_в_числовом_формате, является обязательным аргументом и представляет собой дату, месяц которой следует вычислить. Даты нужно вводить при помощи функции ДАТА, указывать ссылкой на ячейку, которая содержит дату в соответствующем формате или это будет результат отдельных расчётов иных функций и формул. Важно! В случае, когда дата будет введена как текст, это приведёт к ошибкам и проблемам.

     Особенности, как используется функция МЕСЯЦ в Excel, соответствуют аналогично предыдущей рассматриваемой функции, так как формат дат распространяется на все функции времени.

Функция ДЕНЬ в Excel

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

     Синтаксис, который использует функция ДЕНЬ в Excel, так же не отличаеться обилием аргументов:      =ДЕНЬ(ваша_дата_в_числовом_формате), где:

• Дата_в_числовом_формате, является обязательным аргументом и представляет собой дату, день которой следует вычислить. Даты нужно вводить при помощи функции ДАТА, указывать ссылкой на ячейку, которая содержит дату в соответствующем формате или это будет результат отдельных расчётов иных функций и формул. Важно! В случае, когда дата будет введена как текст, это приведёт к ошибкам и проблемам.

•      Как и предыдущие функции, функция ДЕНЬ в Excel хранит даты как последовательные числа и значения которые она возвращает, соответствуют датам григорианского календаря.
•      Отдельно для каждой функции я решил не создавать примеры их использования, поскольку они очень схожи в характере работы, рознятся только полученные результаты, разницу вы можете изучить на скриншоте:      Также, для примера, можете изучить варианты работы этих функций в моей статье «Как посчитать количество дней в Excel».
•      Для работы с датами также вам будет полезно, ознакомится со статьями: «Как определяется разница между двумя датами с помощью функции РАЗНДАТ» и «Как определить в Excel день недели по дате».

     Ну, вот и всё что я, пока, хотел вам рассказать о таких функциях работы с датами как функция ГОД, функция МЕСЯЦ и функция ДЕНЬ. Очень надеюсь, что моя статья вам помогла в решении вашей проблемы. Жду ваши лайки и комментарии, делитесь статьей с друзьями и коллегами, пусть нас будет много!

     До встречи на страницах TopExcel.ru!

«Я хочу жить как бедный человек с деньгами.»

Пабло Пикассо

Статья помогла? Поделись ссылкой с друзьями, твитни или лайкни!

Источник: http://topexcel.ru/funkcii-vremeni-god-mesyac-den-v-excel/

Текстовая дата в Excel

Вы когда-нибудь сталкивались с колонкой дат, с которой вы ничего не могли сделать? Ну то есть ни определить день недели, ни номер месяца, ни прибавить сколько-нибудь дней?

Не так давно мне задали вопрос – что делать с такими данными, как их исправить. Выглядели данные нормально, то есть «1 января 2010», «10 октября 2008», «17 ноября 2009» и т.д.. Но если в соседнем столбце попытаться определить день недели с помощью функции НОМНЕДЕЛИ (WEEKNUM), Excel выдаст ошибку #ЗНАЧ! (#VALUE!)

Скорее всего это происходит из-за того, что изначально дата была введена в текстовом формате (или импортировалась в текстовом формате), и Excel не «распознал» в ней дату, с которой он был бы готов работать.

Представим, что в файле Excel, начиная с ячейки A2 и ниже, в первом столбце расположены такие даты. Первым делом нам необходимо вычленить из этого текста номер дня и номер года в соседних столбцах. Для этого используем функции ЛЕВСИМВ(LEFT), ПРАВСИМВ(RIGHT), ПОИСК(SEARCH), ПСТР(MID)

Заполняем столбец B – номер дня

С помощью формулы для определения дня мы отрежем такое количество символов, сколько расположено до первого пробела. Чтобы не захватить первый пробел, используем -1. Итого формула для определения дня (для ячейки B2):

• =ЛЕВСИМВ(A2;ПОИСК(» «;A2)-1)
  =LEFT(A2;SEARCH(» «;A2)-1)
• Заполняем столбец C – год
• С годом проще – мы знаем, что это всегда 4 символа, поэтому можно просто указать
• =ПРАВСИМВ(A2;4)
  =RIGHT(A2;4)
• Заполняем столбец D – три буквы месяца

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

=ПСТР(A2;ПОИСК(» «;A2)+1;3)
=MID(A2;SEARCH(» «;A2)+1;3)

Заполняем столбцы I и J вспомогательной таблицей

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

В ячейке I2 – «Янв», J2 – 1, I3 – «Фев», J3 – 2, I4 – «Мар», J4 – 3 и т.д..

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

1. Заполняем столбец E – номер месяца
2. После этого мы можем осуществить подстановку номера месяца по ее трехбуквенной аббревиатуре при помощи функции ВПР(VLOOKUP)
3. =ВПР(D2;$I$2:$J$13;2;ЛОЖЬ)
   =VLOOKUP(D2;$I$2:$J$13;2;FALSE)
4. Заполняем столбец F – дата
5. Осталось только скрепить воедино дату при наличии дня, месяца и года. Для этого используем функцию ДАТА(DATE):
6. =ДАТА(C2;E2;B2)
   =DATE(C2;E2;B2)
7. И в столбце F мы получаем дату, которую понимает Excel, с которой можно делать много чего интересного — начиная от простого формата чисел, и заканчивая функция ЧИСТРАБДНИ(NETWORKDAYS) и так далее.
8. Ссылка на скачивание примера: Text_data_sample

Источник: https://ytikhonov.wordpress.com/2015/08/16/text-data-correction-excel/

Дата прописью в Excel

Пользовательская функция «ДатаПрописью» предназначена для преобразования даты из числового формата Excel в полную текстовую форму. Интервал преобразуемых дат составляет с 2001 по 2099 год.

Используется в шаблонах доверенностей, договоров, соглашений, решений, уставов и других документов, заполняющихся в программе Excel и предусматривающих наличие строки с датой прописью. Формат преобразования: 01.01.

2001 — «Первого января две тысячи первого года».

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

Импорт модуля с функцией в проект

Импорт готового модуля в книгу Excel — самый простой способ добавления функции «ДатаПрописью».

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

Вставка кода в стандартный модуль

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

'Преобразование даты из числового формата в текст с 2001 по 2099 год
Public Function ДатаПрописью(md As Date) As String
If (md < 36892) Or (md > 73050) Then
ДатаПрописью = «Преобразуемая дата должна быть с 2001 по 2099 год!»
Else
Dim den As Byte, dg(1 To 4) As Byte, mes As Byte, god As Byte, _
mespr As String, dmgpr As String
den = Day(md)
mes = Month(md)
god = (Year(md) Mod 100)
dg(1) = god Mod 10
dg(2) = Fix(god / 10)
dg(3) = den Mod 10
dg(4) = Fix(den / 10)
Dim dgpr(1 To 4) As String, i1 As Byte
For i1 = 1 To 4
If (i1 = 1) Or (i1 = 3) Then
If dg(i1 + 1) = 1 Then
dgpr(i1) = Choose(dg(i1) + 1, «десятого «, «одиннадцатого «, «двенадцатого «, _
«тринадцатого «, «четырнадцатого «, «пятнадцатого «, «шестнадцатого «, _
«семнадцатого «, «восемнадцатого «, «девятнадцатого «)
Else
dgpr(i1) = Choose(dg(i1) + 1, «», «первого «, «второго «, _
«третьего «, «четвертого «, «пятого «, «шестого «, _
«седьмого «, «восьмого «, «девятого «)
End If
ElseIf (i1 = 2) Or (i1 = 4) Then
If dg(i1 — 1) = 0 Then
dgpr(i1) = Choose(dg(i1) + 1, «», «», «двадцатого «, _
«тридцатого «, «сорокового «, «пятидесятого «, «шестидесятого «, _
«семидесятого «, «восьмидесятого «, «девяностого «)
Else
dgpr(i1) = Choose(dg(i1) + 1, «», «», «двадцать «, _
«тридцать «, «сорок «, «пятьдесят «, «шестьдесят «, _
«семьдесят «, «восемьдесят «, «девяносто «)
End If
End If
Next
mespr = Choose(mes, «января «, «февраля «, «марта «, «апреля «, «мая «, _
«июня «, «июля «, «августа «, «сентября «, «октября «, «ноября «, «декабря «)
dmgpr = dgpr(4) & dgpr(3) & mespr & «две тысячи » & dgpr(2) & dgpr(1) & «года»
ДатаПрописью = Replace(dmgpr, Left(dmgpr, 1), UCase(Left(dmgpr, 1)), 1, 1)
End If
End Function

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

Работа с функцией в Excel

Итак, если вы импортировали модуль или создали новый и вставили в него код, можете вызывать Мастер функций и работать с функцией «ДатаПрописью», как с любой другой, встроенной в Excel. Найти ее вы сможете в разделе «Определенные пользователем». В настройках вашей программы Excel должно быть разрешено выполнение макросов.

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

• в текущей рабочей книге — =ДатаПрописью()
• в Личной книге макросов — =PERSONAL.XLSB!ДатаПрописью()

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

Источник: https://vremya-ne-zhdet.ru/ofisnaya-rabota/data-propisyu-v-excel/

Остановить Excel от автоматического преобразования определенных текстовых значений в даты — import | Qaru

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

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

• ZIP/почтовые индексы
• телефонные номера
• правительственные идентификационные номера

который иногда может начинаться с одного или нескольких нулей (0), которые отбрасываются при преобразовании в числовое. Или значение содержит символы, которые можно спутать с математическими операторами (как в датах:/, -).

Два случая, о которых я могу думать, что решение «prepending =», как упоминалось ранее, может быть не идеальным

• где файл может быть импортирован в программу, отличную от MS Excel (функция MS Mail Mail Merge приходит на ум),
• где человеческая читаемость может быть важной.

Мой взлом, чтобы обойти это

Если один из pre/добавляет в числозначительный и/или не-даты символ, значение будет распознаваться как текст и не преобразовываться. Непечатаемый символ будет хорош, так как он не изменит отображаемое значение.

Однако простой старый символ пространства ( s, ASCII 32) не работает для этого, поскольку он отрубается Excel, а затем значение все еще преобразуется. Но есть и другие печатные и непечатные пробелы, которые будут хорошо работать.

Однако проще всего добавить (добавить после) простой символ табуляции ( t, ASCII 9).

Преимущества такого подхода:

• Доступный с клавиатуры или с легко запоминающимся кодом ASCII (9),
• Он не беспокоит импорт,
• Обычно не беспокоит результаты слияния почты (в зависимости от шаблона макета, но обычно он просто добавляет широкое пространство в конце строки). (Если это проблема, посмотрите на другие символы, например, пространство нулевой ширины (ZWSP, Unicode U + 200B)
• не является большим препятствием при просмотре CSV в «Блокноте» (и т.д.),
• и может быть удален путем поиска/замены в Excel (или Notepad и т.д.).
• Вам не нужно импортировать CSV, но можно просто дважды щелкнуть, чтобы открыть CSV в Excel.

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

Другой вариант

может быть сгенерировать XML файлы, для которых определенный формат также принимается для импорта более новыми версиями MS Excel и который позволяет намного больше параметров, похожих на формат.XLS, но у меня нет опыта с этим.

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

прибавление

Нужно сказать, что более новые версии (2013+) MS Excel больше не открывают CSV в формате электронных таблиц — еще один speedbump в одном рабочем процессе делает Excel менее полезным… По крайней мере, существуют инструкции по обходу, См. Например, этот Stackoverflow: как правильно отображать CSV файлы в Excel 2013? ,

Источник: http://qaru.site/questions/18789/stop-excel-from-automatically-converting-certain-text-values-to-dates