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

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

Как превратить 150204 в 15.02.2004?

Используйте функцию ДАТА (год, месяц, день)
Извлеките каждую часть из 160208 с помощью этих строковых функций: RIGHT, LEFT и MID, как показано здесь: = DATE (RIGHT (A1,2); MID (A1,3,2); LEFT (A1,2))

Примечание: Используйте функции, если и когда изменится порядок. Например: год-месяц-день, месяц-год-день и т. Д.

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

Чтение файлов csv или txt без возможности записи значений в файл

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

Использование скрипта Excel-CSVD

Суть скрипта Excel-CSVD в том, что он открывает файл полностью, как текст. Поэтому он всегда отобразит все данные, как есть. Причем действий со стороны пользователя будет минимально.

К плюсам данного способа можно смело отнести отсутствие правки самого файла csv. При сохранении значения запишутся правильно обратно в файл.

Минус данного способа состоит в том, что при открытии файла все данные (числа или даты) будут текстовые. Это даст неправильную сортировку чисел и дат. При вставке в ячейку формулы, она станет текстом. Для того, чтобы формула заработала в такой ячейке, надо изменить формат на Общий или Формула в свойствах ячейки.

Использование импорта данных на вкладке Данные

Плюс способа в том, что при импорте можно выбрать только необходимые колонки (а не все подряд) как Текст. При этом в остальных колонках сортировка останется нормальной.

Минус только в том, что для пользователя операции импорта с последующим выбором колонок — это куча щелчков мышью. Если операцию нужно проводить постоянно, то этот способ очень муторный.

Решение проблемы отображения числа как даты

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

Способ 1: контекстное меню

Большинство пользователей для решения данной задачи используют контекстное меню.

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

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

Способ 2: изменение форматирования на ленте

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

Выделяем ячейку или диапазон с форматом даты.
Находясь во вкладке «Главная» в блоке инструментов «Число» открываем специальное поле форматирования. В нём представлены самые популярные форматы. Выбираем тот, который наиболее подходит для конкретных данных.
Если среди представленного перечня нужный вариант не был найден, то жмите на пункт «Другие числовые форматы…» в этом же списке.

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

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

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

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

В приложении 1, в описании типа datetime сказано, что SQL сервер использует формат даты, при котором вначале идет месяц (мм/дд/гггг). Если все оставить так, как есть, то пользователям будет неудобно вводить данные в таблицы, ведь мы привыкли начинать ввод даты с числа, а потом уже указывать месяц.

Для преобразования дат лучше всего использовать функцию CONVERT. Самый простейший пример преобразования с помощью этой функции:

SELECT convert(datetime, '31.1.2005' ,103)

Как мы уже знаем, после указания функции convert, в скобках указывается новый тип и переменная. Но у нас здесь три параметра. Что это за третий параметр. Это стиль, в котором представлена дата. Если посмотреть на дату ‘31.1.2005’, то видно, что вначале явно идет число, ведь 31-го месяца не существует. Если бы команда выглядела так: SELECT convert(datetime, ‘31.1.2005’), то сервер вернул бы нам ошибку, потому что не смог бы преобразовать дату.

Последний параметр как раз указывает номер стиля, в котором мы представили дату и благодаря ему сервер правильно прочитает параметр ‘31.1.2005’. Стили вы можете увидеть в таблице 2.1. Это наиболее часто используемые стили. Более полный вариант таблицы можно увидеть в файле помощи. В первой колонке показан номер стиля, если год показан в сокращенном виде (то есть без указания века, например 05). Во второй колонке номер стиля, если год указан полностью (например, 2005).

Таблица 2.1. Стили преобразования типа данных даты

Сокращенный год	Полный год	Формат даты
—	0 или 100	Месяц дд гггг чч:ммAM (или PM)
1	101	мм/дд/гггг
2	102	гг.мм.дд
3	103	дд/мм/гггг
4	104	дд.мм.гггг
5	105	дд-мм-гггг
6	106	дд месяц гггг
7	107	месяц дд гггг
8	108	чч:мм:сс
10	110	мм-дд-гггг
11	111	гг/мм/дд
12	112	Ггммдд

Исходя из этой таблицы, стиль 103 соответствует принятому в России формату дд/мм/гггг и именно его мы использовали.

В следующем запросе мы используем преобразование даты в операторе INSERT для вставки данных в таблицу:

INSERT INTO tbPeoples
 (vcFamil, vcName, vcSurname, idPosition, dDateBirthDay) 
VALUES('СОДОРОЧКИН', 'ИВАН', 'СЕРГЕЕВИЧ', 11, 
  convert(datetime, '31.1.2005' ,103))

Есть еще один способ указания формата даты – с помощью оператора SET. Этот оператор мы будем достаточно подробно рассматривать в главе 4.1, но то, что касается преобразования даты мы рассмотрим сейчас. Посмотрите на следующий пример:

SET DATEFORMAT dmy
GO
DECLARE @vdate datetime
SET @vdate = '29/11/00'
SELECT @vdate

В первой строке указывается формат необходимой даты с помощью оператора SET DATEFORMAT. После этого указывается формат в виде букв d, m и y, которые отражают необходимую нам последовательность в указании даты. В данном случае мы говорим, что мы будем указывать в дате сначала число, потом месяц и в конце будет идти год.

Теперь объявляется переменная vdate, которой присваивается дата в заданном формате и эта дата выводиться на экран.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Как изменить формат даты в Google Таблицах

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

Формат даты Google Sheets по умолчанию

Выберите все ячейки, которые вы хотите отформатировать.
Перейти к Формат > Число в меню электронной таблицы и выберите Свидание чтобы увидеть только дату или Дата время чтобы получить дату и время в ячейке:

Целые числа успешно преобразуются в формат, который вы узнаете с первого взгляда. Это форматы даты Google Таблиц по умолчанию:

Кончик. Вы можете найти те же форматы, если вы нажмете на 123 значок на панели инструментов электронной таблицы:

Пользовательские форматы даты

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

Вы можете получить к ним доступ из того же меню Google Sheets: Формат > Число > Дополнительные форматы > Дополнительные форматы даты и времени:

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

Если вас все еще не устраивает внешний вид ваших дат, вы можете настроить свой собственный формат даты:

Выберите ячейки, которые вы хотите отформатировать.
Перейти к Формат > Число > Дополнительные форматы > Дополнительные форматы даты и времени.
Поместите курсор в поле вверху, содержащее единицы даты, и удалите все с помощью клавиш Backspace или Delete:
Нажмите на стрелку справа от поля и выберите юнит, который вы хотите иметь в первую очередь. Не забудьте потом ввести разделитель

Повторяйте, пока не будут добавлены все необходимые единицы (не беспокойтесь, вы сможете добавить или удалить их позже):

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

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

Когда будете готовы, нажмите Применить.

Вот какой формат я создал и как теперь выглядят мои даты:

Функция QUERY для Google Sheets для форматирования дат

Есть еще один способ изменить формат даты в Google Sheets — конечно, с помощью формулы. Поскольку QUERY я показываю вам не в первый раз, я начинаю думать о нем как о настоящей панацее для электронных таблиц.

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

Я хочу изменить формат даты в столбце B. Вот моя формула ЗАПРОСА:

=ЗАПРОС(A1:C7,»выберите * формат B ‘д-ммм-гг (ддд)’»)

сначала я указываю диапазон всей моей таблицы — А1:С7
затем я прошу формулу вернуть все столбцы – Выбрать *
и в то же время переформатировать столбец B так, как я вставил в формулу — формат B ‘д-ммм-гг (ддд)’

Формула работает как шарм. Он возвращает всю мою таблицу и меняет формат даты в столбце B:

Как вы могли заметить, для изменения формата даты с помощью формулы я использовал специальные коды, представляющие разный вид дней, месяцев и лет. Если вы не знакомы с ними, вот список этих кодов для дат:

Код Описание Пример d День без начального нуля для 1-9 7 dd День с начальным нулем для 1-9 07 ddd День как аббревиатура Wed dddd День как полное название Среда m(если не предшествует или не следует зачасы или секунды) Месяц без ведущего нуля 8 мм(если не предшествует или не следует зачасы или секунды) Месяц с начальным нулем 08 ммм Аббревиатура месяца авг мммм Полное название месяца август ммммм Первая буква месяца А уили жеyy Год из двух цифр 19 yyyили жегггг Полный числовой год 2019

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

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

Получите только год, месяц или день:
=ЗАПРОС(A1:C7,»выберите * формат B ‘гггг’»)
Возвращает день, месяц и день недели:
=ЗАПРОС(A1:C7,»выберите * формат B ‘дд мммм, дддд’»)

Кстати, к какому формату даты вы привыкли?

Использование функций при работе с датами и временем

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

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

ГОД()

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

В ячейке 1 видно дату, представленную в формате ДДДД ДД.ММ.ГГГГ чч:мм:cc. Это тот формат, который мы создали ранее. Давайте приведем в качестве примера формулу, которая определяет, сколько лет прошло между двумя датами.

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

МЕСЯЦ()

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

ДЕНЬ()

Аналогично предыдущим функциям, эта выдает номер дня, в определенной дате. Результат вычислений может колебаться от 1 до 31.

МИНУТЫ()

Функция, возвращающая количество минут в определенной ячейке. Возможные значения, которые возвращаются – от 0 до 59.

ДЕНЬНЕД()

С помощью этой функции можно узнать номер дня недели, который используется в этой дате. Возможные значения от 1 до 7, но стоит учитывать, что отсчет начинается с воскресенья, а не понедельника, как у нас принято.

При этом с помощью второго аргумента эта функция позволяет настроить формат. Например, если передать значение 2 в качестве второго параметра, то можно настроить формат, при котором число 1 означает понедельник, а не воскресенье. Это значительно удобнее для отечественного пользователя.

Если во втором аргументе написать 2, то в нашем случае функция вернет значение 6, что соответствует субботе.

СЕГОДНЯ()

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

ТДАТА()

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

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

Например, такая формула может определить нынешнее время.

=ТДАТА()-СЕГОДНЯ()

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

ДАТА()

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

Аргумент «День» или «Месяц» можно делать как положительным, так и отрицательным. В первом случае дата увеличивается, а во втором – уменьшается.

Также можно использовать математические операции в аргументах функции ДАТА. Например, эта формула позволяет добавить 1 год 5 месяцев и 17 дней к дате, указанной в ячейке A1.

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

ВРЕМЯ()

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

По своему принципу работы у функции ВРЕМЯ() и ДАТА() очень много чего схожего. Поэтому особого внимания на ней акцентировать нет смысла.

Важно учесть, что эта функция не способна вернуть время, которое больше 23:59:59. Если получится больший, чем этот, результат, функция автоматически обнуляется

Функции ДАТА() и ВРЕМЯ() могут применяться вместе.

На этом скриншоте ячейка D1, в которой использовались обе эти функции, имеет формат даты и времени.

Типы данных в Access

Для того, чтобы обеспечить возможность хранения в базе данных разнообразной информации, Access предлагает большой набор типов данных, перечисленных в табл. 16.1.

ТАБЛИЦА 16.1 . Типы данных

Чтобы сделать работу со списком контактов более продуктивной, следует изменить типы данных некоторых полей.

1.В окне базы данных выделите таблицу Список и щелкните на кнопке Конструктор, расположенной в верхней части окна. Выбранная таблица откроется в режиме конструктора.

2. Описание контакта может быть достаточно длинным, а максимальная длина текстовых полей ограничена 256 символами. Поэтому измените тип поля Описание на поле MEMO.

3. Щелкните на поле Дата.

Рис. 16.14. Выбор формата данных в Access

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

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

5. Введите в поле Значение по умолчанию функцию Date, которая возвращает текущую дату. В некоторых случаях требуется не только задать тип данных поля, но и ограничить его возможные значения некоторым интервалом или более сложным правилом отбора. Такое правило вводится в поле Условие на значение. Предположим, вы точно знаете, что все контакты, которые будут заноситься в список, состоятся после 1 января 1999 г. Чтобы избежать ошибок, можно заставить Access сообщать вам о вводе неверной даты. Для этого выполните следующие шаги.

6. В поле Условие на значение щелкните на кнопке построителя выражений. Откроется окно диалога. Для задания условий или значений определенных параметров Access предлагает большой набор математических операций и функций. Построитель выражений помогает ориентироваться во всем многообразии функций Access. Верхнее прокручивающееся поле построителя содержит конструируемое выражение. Его можно изменять как путем непосредственного редактирования текста, так и с помощью других кнопок и списков построителя.

7. Необходимо, чтобы дата записи была позднее, то есть больше, даты 1 января 1999 г. Поэтому щелкните на кнопке >, чтобы добавить нужное условие.

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

8. Дважды щелкните на папке Функции в левом списке построителя выражений.

9. Щелкните на папке Встроенные функции, чтобы вывести список стандартных функций Access.

10. В среднем списке выберите категорию Дата/время.

11. Двойным щелчком на имени функции DateValue добавьте ее в поле выражения. Вместо местозаполнителя аргумента «stringexpr» нужно ввести конкретную дату в строковом формате.

12. В поле выражения щелчком выделите текст «stringexpr», а затем напечатайте «1.1.99».

13. Щелкните на кнопке ОК.

Рис. 16.15. Параметры поля Дата

14. В поле Сообщение об ошибке введите текст Неверная дата, который будет появляться при вводе даты, более ранней, чем 1 января 1999 г. Окончательный набор параметров поля Дата показан на рис. 16.15.

15. Щелкните на кнопке закрытия окна конструктора. В окне запроса на обновление структуры таблицы щелкните на кнопке Да. По причине добавления условий на значения поля Дата Access запросит о необходимости проверки содержимого этого поля в уже имеющихся записях таблицы. Щелкните на кнопке Нет, чтобы не выполнять проверку.

Давайте изучим влияние настроенных параметров на поведение базы данных.

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

17. Щелкните в ячейке Дата второй строки, измените ее содержимое на 19.11.95 и нажмите клавишу Tab, чтобы перейти к следующему полю. Так как модифицированная дата предшествует 1 января 1999 г., что противоречит настроенному условию, Access не позволит убрать курсор из текущей ячейки и откроет окно сообщения с текстом, введенным вами в поле Сообщение об ошибке конструктора таблицы на шаге 14. Такое сообщение будет появляться до тех пор, пока вы не скорректируете дату.

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

Если ваша ячейка содержит дату, но Excel считает ее текстовой строкой, вы можете (в большинстве случаев) использовать ЗНАЧЕНИЕ функция для его преобразования. Это берет текстовое значение и преобразует его в пятизначную временную метку Unix, которая затем может быть преобразована в типичное значение даты, которое вы визуально распознаете.

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

Чтобы преобразовать текстовую строку в значение даты, введите = ЗНАЧЕНИЕ (А2)где клетка A2 содержит текстовую строку, которую вы конвертируете. В приведенном ниже примере мы используем формат даты день-месяц-год, но это будет работать и для других форматов даты.

Это работает только для определенных типов вероятных значений даты. Как вы увидите на изображении выше, он не поддерживает даты с десятичными знаками (например, 12.05.1990). Это вернет #ЗНАЧЕНИЕ ошибка для ячеек, которые он не может конвертировать.

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

Использование проверки ошибок для изменения формата даты

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

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

Выберите одну из ячеек, содержащих индикатор. Вы должны заметить восклицательный знак с выпадающим меню рядом с ним.
Выберите раскрывающееся меню и выберите « Преобразовать ХХ в 19ХХ» или « Преобразовать ХХ в 20ХХ» , в зависимости от года.
Вы должны увидеть, что дата немедленно изменится на четырехзначное число.

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

Есть несколько способов узнать, отформатирован ли номер или набор чисел в столбце или строке как текст в Excel.

Самый простой способ – выбрать ячейку, выбрать главное меню и в группе «Число» на ленте указать формат чисел, отображаемый в раскрывающемся списке.

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

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

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

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

Вставка текущей даты и времени

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

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

Если вам нужно установить статическое время, вы можете использовать специальные инструменты Excel, которые вызываются с помощью горячих клавиш:

Ctrl +; o Ctrl + Shift + 4 — эти горячие клавиши автоматически вставляют соответствующую дату в ячейку, когда человек нажимает эти кнопки.
Ctrl + Shift +; или Ctrl + Shift + 6 — можно использовать для записи текущего времени.
Если вам нужно ввести и время, и дату, актуальные для данного момента, вы должны сначала нажать первую комбинацию клавиш, затем нажать пробел и вызвать вторую комбинацию.

Какие именно ключи мне следует использовать? Все зависит от активированной в данный момент раскладки. Если сейчас английская раскладка, то используется первая комбинация, если русская — вторая (то есть та, которая сразу следует за словом «или»).

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

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

Функции вычисления даты и времени

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

ДАТАМЕС()

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

КОНМЕСЯЦА()

Эта функция дает возможность определить порядковый номер последнего дня месяца, отстающего или опережающего заданную дату. Имеет такие же аргументы, как и предыдущая.

РАБДЕНЬ()

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

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

Google Sheets: преобразовать дату в число

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

Преобразовать дату в число, изменив формат

Выберите те ячейки с датами, которые вы хотите преобразовать в числа.
Перейти к Формат > Число и на этот раз выбрать Число среди прочих вариантов.
Вуаля — все выбранные даты превратились в цифры, которые их обозначают:

Функция DATEVALUE для Google Sheets

Другой способ для Google Sheets преобразовать дату в число — использовать функцию DATEVALUE:

=ДАТАЗНАЧ(дата_строка)

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

Например, я хочу преобразовать 17 августа 2019 г. к номеру. Все приведенные ниже формулы вернут один и тот же результат: 43694.

=ДАТАЗНАЧ(«17 августа 2019 г.»)=ДАТАЗНАЧ(«2019-8-17»)=ДАТАЗНАЧ(«17.08.2019»)

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

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

=ДАТАЗНАЧ(A2)

Функция ВРЕМЯ

Возвращает значение времени, составленное из отдельных частей, заданных числами.Синтаксис: =ВРЕМЯ(часы; минуты; секунды), где

Часы – обязательный аргумент. Число либо ссылка на ячейку, содержащую числовое значение от 1 до 24.
Минуты – обязательный аргумент. Число либо ссылка на ячейку, содержащую числовое значение от 1 до 60.
Секунды – аналогично минутам.

Функция ВРЕМЯ не может возвращать значение от 24 и выше, т.е. если для аргумента «Часы» задать число 25, то результатом будет 1 час.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Применение встроенного формата

В Access есть несколько предопределенных форматов для данных даты и времени.

Откройте таблицу в режиме Конструктор.

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

В разделе Свойства поля щелкните стрелку в поле свойства Формат и выберите формат из раскрывающегося списка.

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

Чтобы применить изменения ко всей базе данных, в диалоговом окне » Обновление свойств » отобразите формы и другие объекты, которые будут наследовать новый формат. Нажмите Да.

Дополнительные сведения можно найти в разделе распространение свойства поля.

Сохраните изменения и переключитесь в режим таблицы, чтобы узнать, соответствует ли формат вашим требованиям.

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

В форме или отчете

Откройте форму или режим макета отчета или конструктор.

Поместите указатель мыши в текстовом поле и выберите дату и время.

Нажмите клавишу F4, чтобы открыть окно свойств.

Задайте для свойства Format один из предопределенных форматов даты.

Откройте запрос в режиме конструктора.

Щелкните поле даты правой кнопкой мыши и выберите пункт Свойства.

На странице свойств в списке свойств Формат выберите нужный формат.

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

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