Auditportal.ru

Аудит Портал
1 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как изменить американский формат даты в Excel?

Excel VBA — конвертировать текст в дату?

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

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

Это изменило формат ячеек на дату, но не изменило формат значения, которое по-прежнему сохранялось как текст.

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

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

Мы будем признательны за предложения.

10 ответов

Команда V BA «Данные * Колонки в таблице» помогает быстро преобразовать столбец текста, который выглядит как дата, в реальную дату.

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

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

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

Приложение может напрямую генерировать значение даты Excel из строки формата yyyy-mm-dd.

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

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

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

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

Данный код работает для меня.

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

Читайте так же:
Как использовать ВПР для создания уникального списка из таблицы в Excel?

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

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

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

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

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

D ММ-ГГГГ можно контролировать с помощью простого обходного пути:

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

Вы можете использовать FORMAT (date, «dd- mmm -yyy») для ввода дат в строку из рабочего листа.

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

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

Как использовать функцию DATE (ДАТА) в Excel

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

Когда использовать функцию

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

Например, при регистрации данных о продажах и сделках, регистрации сотрудников и т.д.

Часто значение данных является результатом формулы или расчета.

Какие данные отображает формула

Значение формулы хранится в Excel в числовом формате. Например значение «42370» при использовании этой формулы вернёт значение «Январь 2016». Таким образом, формула преобразует числовое значение в привычный формат даты.

Синтаксис

=DATE(year, month, day) — английская версия

=ДАТА(год; месяц; день) — русская версия

Аргументы

  • Year (Год) — значение года, которое важно отобразить в дате;
  • Month (Месяц) — значение месяца, которое важно отобразить в дате;
  • Day (День) — значение дня, которое важно отобразить в дате.

Дополнительная информация

  • то, как отображаются значения формулы в ячейке зависит от того, в каком формате ячейка. Например:
    • Если ячейка отформатирована как Общая, значение даты отображается в известном формате даты (день-месяц-год). Из приведенного выше текста видно, что значение данных, обрабатываемых в Excel, хранится в числовом формате. Если вы хотите, чтобы значение данных было в числовом формате, измените формат ячейки данных на Числа.

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

    Пример 1: Получение числового значения даты, с использованием данных Года, Месяца, Дня

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

    Как использовать функцию DATE (ДАТА) в Excel

    В приведенном выше примере, функция DATE (ДАТА) использует три аргумента: значение «2016» в качестве значения года, значение «4» в качестве месяца и «1» в качестве значения дня.

    Функция возвращает «42461», когда ячейка отформатирована как «General» (Общие), и возвращает «4/1/16», когда ячейка отформатирована как ‘Date’.

    В Excel дата или время хранится в виде числового значения.

    Формат даты может отличаться в зависимости от региональных настроек. Например, формат даты в России — DD-MM-YYYY, а в Великобритании — MM-DD-YYYY.

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

    Как использовать функцию DATE (ДАТА) в Excel


    Пример 2: Использование отрицательных значений в функции DATE (ДАТА)

    Вы можете использовать отрицательные значения при указании Месяца и Дня в ячейке. При использовании отрицательных значений при указании месяца, скажем, «-1», Excel отнимет 1 месяц от Года.

    Вы можете использовать формулу =DATE(2017,-1,1) или =DATE(2017;-1,1), чтобы узнать дату 1 ноября 2016 года. Вы также можете использовать любое отрицательное число в значении Month.

    Как использовать функцию DATE (ДАТА) в Excel

    Та же логика работает, когда вы используете значение месяца более чем «12». В этом случае, Excel прибавляет к значению Года столько месяцев, на сколько исходное значение Месяца больше 12.

    Если использовать формулу =DATE(2016,14,1) или =DATE(2016;14;1), она возвращает дату 1 февраля 2017 года.

    Как использовать функцию DATE (ДАТА) в Excel

    Примечание: Формат даты, показанный в примерах выше, основан на конфигурации моей системы. Он может отличаться в зависимости от конфигурации вашей системы.

    Кроме того, вы можете также использовать отрицательные значения Дня, или те что больше, чем «31» — функция DATE (ДАТА) будет корректировать дату по логике описанной выше.

    Еще больше полезных приемов в работе со списками данных и функциями в Excel вы узнаете в практическом курсе «От новичка до мастера Excel«. Успей зарегистрироваться по ссылке!

    Использование функции DATE в Google Sheets

    Используя функцию DATE, Google Sheets комбинирует дни, месяцы и годы, введенные в аргументы функции, чтобы вернуть дату или порядковый номер.

    Например, если в ячейку листа введена следующая функция DATE – = DATE (2016,01,16) , возвращается серийный номер 42385 , который ссылается на дата 16 января 2016 г.

    Ввод дат как дат

    В сочетании с другими функциями Google Sheets DATE можно использовать для создания самых разнообразных формул дат. Одним из важных применений этой функции – как показано в строках с 5 по 10 на изображении выше – является обеспечение правильного ввода и интерпретации дат некоторыми другими функциями дат в Листах Google. Это особенно верно, если введенные данные отформатированы как текст.

    Функция DATE в основном используется для отображения даты, которая объединяет элементы даты – например, год, месяц или день из разных мест на листе, и для обеспечения того, чтобы даты, используемые в расчетах, интерпретировались как даты (числовые данные) вместо текстовых данных. ,

    Синтаксис и аргументы функции DATE

    Синтаксис функции описывает схему функции и относится к имени функции, круглым скобкам и аргументам.

    Синтаксис функции DATE: = DATE (год, месяц, день)

    • Год – введите год в виде четырехзначного числа (гггг) или ссылку на ячейку в соответствии с его местоположением на листе.
    • Месяц – введите месяц в виде двузначного числа (мм) или ссылку на ячейку в соответствии с его местоположением на листе.
    • День – введите день в виде двузначного числа (dd) или ссылку на ячейку в соответствии с его местоположением на листе.

    Ошибки и корректировки

    Значение! Функция возвращает значение ошибки, если вместо числа вводятся текстовые данные. Это включает в себя ввод ссылки на ячейку, содержащую текст. #NUM! Функция возвращает значение ошибки, если для аргумента год введено пятизначное число, например 20016. Если для аргументов месяца или дня есть недопустимое значение, функция автоматически корректирует выходные данные функции на следующую действительную дату. Например, = DATE (2016,13,1) – в котором для аргумента month указано 13 – корректирует аргумент year и возвращает 01.01.2017. Другой пример: = DATE (2016,01,32), который имеет 32 дня для января, корректирует аргумент month и возвращает 2/01/2016. Если для аргумента введены десятичные значения, значение усекается до целочисленного значения. Например, значение «10,25» будет интерпретировано функцией как «10».

    Пример функции DATE

    На рисунке выше функция DATE используется вместе с другими функциями в серии формул даты.

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

    • Строка 5 вводит первый день текущего месяца
    • Строка 6 преобразует текстовую строку (ячейка A5) в дату
    • Строка 7 отображает день недели для этой даты
    • Строка 8 считает дни между текущей и предыдущей датой
    • Строка 9 преобразует номер юлианского дня (ячейка A9) в текущую дату
    • Строка 10 преобразует текущую дату (ячейка A10) в номер юлианского дня

    Ниже описаны шаги для ввода функции DATE, которая находится в ячейке B4. Она создает составную дату, объединяя отдельные элементы даты в ячейках A2 — C2.

    Ввод функции ДАТА

    Есть несколько вариантов ввода функции и ее аргументов в таблице:

    • Введите вручную полную функцию – просто помните, что порядок должен быть гггг, мм, дд , например = ДАТА (2016,01,16) или = ДАТА (A2, B2, C2) , если используются ссылки на ячейки.
    • С помощью окна автоматического предложения введите функцию и ее аргументы.

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

    Разделитель запятых

    При использовании любого метода для ввода функции обратите внимание, что запятые (, ) используются для разделения аргументов функции внутри скобок.

    Ниже описаны шаги, как ввести функцию DATE, расположенную в ячейке B4 на рисунке выше, используя автоматическое окно предложений .

    Нажмите на ячейку D4, чтобы сделать ее активной: здесь отображаются результаты функции ДАННЫЕ.

    Введите знак равенства = , за которым следует название функции – дата.

    Окно автоматического предложения с именами и синтаксисом функций, начинающихся на букву D, появляется при вводе .

    Щелкните поле DATE, чтобы ввести имя функции и открывающую скобку (в ячейке D4).

    Нажмите на ячейку A2 в рабочем листе, чтобы ввести ссылку на эту ячейку в качестве аргумента года.

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

    Щелкните на ячейке B2, чтобы ввести ссылку на эту ячейку как аргумент месяца.

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

    Нажмите ячейку C2, чтобы ввести ссылку на эту ячейку в качестве аргумента дня .

    Нажмите Enter на клавиатуре, чтобы ввести закрывающую круглую скобку) и завершите функцию.

    Дата должна быть в ячейке B1 с форматом 15.11.2015.

    Когда вы щелкаете по ячейке B1, полная функция = DATE (A2, B2, C2) появляется в строке формул над рабочим листом.

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

    Изменение формата даты

    Отметьте ячейки, которые содержат или будут содержать даты на листе.

    Нажмите на кнопку Формат >Число >Дата в меню, чтобы изменить форматирование ячейки на формат даты, используемый текущими региональными настройками.

    Изменение региональных настроек

    Как и многие онлайн-приложения, Google Sheets по умолчанию использует формат даты США, также известный как MM/DD/YYY.

    Если в вашем местоположении используется другой формат даты – например, в Google Листах с прямым порядком байтов (ГГГГ/ММ/ДД) или с прямым порядком байтов (ДД/ММ/ГГГГ) Google Sheets может быть настроен для отображения даты в правильном формате, регулируя региональные настройки.

    Чтобы изменить региональные параметры:

    Чтобы открыть меню Файл, нажмите Файл.

    Нажмите на Настройки электронной таблицы, чтобы открыть диалоговое окно настроек.

    В разделе Языковой стандарт в диалоговом окне нажмите на поле – значение по умолчанию США – для просмотра списка доступных настроек страны.

    Теперь вашей страной будет выбранная вами страна.

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

    Новые даты, введенные в таблицу, должны соответствовать формату выбранной страны – для вступления изменений в силу, возможно, потребуется снова отформатировать существующие даты.

    Отрицательные серийные номера и даты Excel

    По умолчанию в Microsoft Excel для Windows используется система дат, начинающаяся с 1900 года. Ввод серийного номера 0 дает дату: 0 января 1900 года. Кроме того, функция DATE в Excel не отображает даты до 1900 года.

    Google Sheets использует дату 30 декабря 1899 года для порядкового номера ноль, но в отличие от Excel, Google Sheets отображает даты до этого, используя отрицательные числа для порядкового номера.

    Например, дата 1 января 1800 года приводит к порядковому номеру -36522 в Google Sheets и позволяет использовать его в формулах, таких как вычитание 1 января 1850 года – 1 января 1800 года, что приводит к значению 18 262 – количество дней между двумя датами.

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

    Юлианские номера дня

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

    Например, на изображении выше юлианский номер дня в ячейке A9 – 2016007 – состоит из семи цифр, причем первые четыре цифры номера представляют год, а последние три дня года. Как показано в ячейке B9, это число представляет седьмой день 2016 года или 7 января 2016 года.

    Число 2010345 относится к 345-му дню 2010 года, то есть 11 декабря 2010 года.

    голоса
    Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector