Как время перевести в число в эксель?
В программе эксель можно перевести дату в числовой формат, при этом в качестве числа может выступать: часы, минуты или секунды. При этом данная операция довольно часто вызывает вопросы, поэтому давайте рассмотрим подробную инструкцию, как время перевести в число в программе эксель.
Рассмотрим данную операцию на конкретном примере, перед нами таблица, в которой мы последовательно время 11:30 переведем в часы, минуты и секунды.
Чтобы любое время перевести в число равное количеству часов, нужно умножить это время на число 24, при этом формат ячейке, в которой производится расчет, должен быть «Общий» или «Числовой». Таким образом, в ячейке «В3» пишем формулу: =A3*24.
Чтобы перевести время в число равное количеству минут, необходимо его умножить на 24 и 60, поэтому в ячейке «В5» пишем формулу: =A5*24*60.
Чтобы сделать перевод времени в секунды, вы должны его умножить один раз на 24 и два раза на 60, т.е. в ячейке «В7» пишем формулу: =A7*24*60*60. Незабываем, что формат ячейки с расчетом должен быть «Общий» или «Числовой».
Экономия 5 минут в час за счет более продуктивной работы дает за год экономию в 4 рабочие недели
Обратное преобразование даты в число
Когда вы связываете несколько систем друг с другом возможны всякие…нюансы. Вроде таблицы с числами вида
Само собой при выгрузке не имелся ввиду июнь 2034 года или 4 августа 2016 года. А конкретные числа 6.34 или 4.08, которые Excel по ошибке попытался преобразовать в даты. Тот же самый механизм, что и если вы введете в любой ячейке любого листа, например, текстом «12-15» и нажмете на Enter. Вы думаете, что вводите числовой диапазон, а Excel думает, что это незавершенная дата вида 12-15-2016 и «помогает» вам дописать его.
Исправить при вводе это автоматическое преобразование можно добавив в начало формулы одинарный апостроф — ‘
В этом случае неважно что вы введете в значение ячейки (в том числе формулу) — Excel будет считать, что дальше текст
Само преобразование может пойти по трем путям (в т.ч. в зависимости от региональных разделителей): 1. Если второе число меньше или равно 12. Excel будет пытаться определить в первом числе день, во втором числе месяц 2. Если второе число больше 12, но меньше или равно 29. Excel будет считать первое число месяцем, второе — годом. Год будет считаться от 1900 3. Если второе число больше или равно 30. Excel будет считать первое число месяцем, второе — годом. Год будет считаться от 2000
Итак, причины и механизмы возникновения ошибки ясны. Как поправить? Простая махинация вроде —A2 не поможет ведь после «дописки» значения до даты прошло преобразование и то же число 6.34 это теперь 12 571.
Исправление немного сложнее. Во-первых посмотрим по какому пути пошло преобразование. Если год от этой даты совпадает с текущим годом — значит мы имеем дело с первой ситуацией, в ином случае — ситуация 2 или 3.
Запишем формулу с вложенным ЕСЛИ():
Для Ситуации 1 мы должны склеить дни (целое число) и номер месяца (дробная часть числа):
Для Ситуации 2 и 3 мы должны взять номер месяца (целое число) и две последние цифры года (дробная часть числа).
Осталось только подставить эти значения в наш ЕСЛИ() в соответствующие группы формулы:
И вуаля. Можем преобразовать хоть 10 000 строк за раз.
PS К сожалению, в ситуации 1 мы не можем точно знать какое число было преобразовано в дату — с нулем, или без него как первый знак после запятой. То есть 4,08 и 4,8 в данном случае будут преобразованы одинаково.
PPS Если на компьютере стоит в качестве разделителя точки — нужно поправить формулу в двойных кавычках должна быть точка.
Дата из текста (ДАТАИЗСТРОКИ)
Часто необходимо извлечь дату из ячейки, которая содержит длинный текст. Задача может усложнится тем, что дата может быть как в начале, в конце или середине текста. Еще сложнее дело будет обстоять, что дата в тексте имеет разные форматы.
Извлечь в данном случае дату из текста стандартными методами крайне сложно.
С помощью функции на VBA =ДАТАИЗСТРОКИ это сделать очень просто. Функция найдет дату в тексте вне зависимости от формата ее записи и расположения ее в строке. Также распознаются даты на русском и английском языке.
Пример 1
Извлечение даты из текста, когда она расположена в начале строки. Это простой случай, здесь можно воспользоваться и сочетанием стандартных текстовых функций.
Аналогичный пример, но дата расположена в конце строки.
Пример 3
Извлечение даты из любой части текста и особенности функции. Обойтись стандартными функциями тут практически невозможно. Алгоритм функции из надстройки справится с эти очень просто.
Дата и месяц прописью в Excel
Узнаем как написать дату и месяц прописью в Excel (в том числе в именительном и родительном падежах).
Приветствую всех, дорогие читатели блога TutorExcel.Ru.
Подписывая какие-либо документы мы помимо собственного автографа очень часто вписываем туда и текущую дату в виде числа, месяца и года. При этом мы почти никогда там не встречаем месяц записанный в численном формате. Ведь, например, дата записанная как 12.11.2016 куда сложнее воспринимается и читается чем 12 ноября 2016 г.
Вот и в Excel иногда требуется указать дату в таком виде, поэтому давайте подробно разберемся какие у нас есть варианты реализации.
Примеры использования функции DATEVALUE (ДАТАЗНАЧ)
Пример №1. Преобразование даты в текстовом формате в число
В приведенном выше примере, формула возвращает числовое значение даты введенной в кавычках. Введенная дата должна быть в текстовом формате, иначе Excel возвращает “#VALUE!” ошибку.
Обратите внимание, что значение, возвращаемое функцией зависит от настройки часов вашей системы. Если дата указана в формате ДД-ММ-ГГГГ значит «01-06-2016» будет отображать как 1 июня 2016 года, но для формата даты ММ-ДД-ГГГГ дата будет отображаться как 6 января 2016
Больше лайфхаков в нашем Telegram Подписаться
Пример №2. Преобразование даты в текстовом формате с помощью формулы
Когда дата введена как текст в ячейку, вы можете использовать функцию DATAVALUE (ДАТАЗНАЧ), чтобы получить числовое значение даты
Обратите внимание, что дата должна быть в текстовом формате
В приведенном выше примере, формула преобразует “1-6-16” и “1-июня-16” — это значение указанное в формате даты.
Эта функция полезна, когда у вас есть дата в текстовом формате, по которой вы хотите фильтровать, сортировать данные в таблице. В текстовом формате, вы не сможете сортировать даты и проводить вычисления в Excel, так как система будет рассматривать его как текст, а не число.
Пример №3. Использование функции без указания года
Если значение года не указано, то система использует текущий год при отображении значения даты (текущий год 2016 в указанном примере).
Пример №4. Использование функции без указания дня
Если в дате не указан день, то формула использует первый день месяца.
В приведенных выше примерах, когда мы вводим число «6-16», система возвращает число 42522, которое является значением даты на 1 июня 2016 года.
Больше лайфхаков в нашем ВК
Возвращаем отображение чисел вместо дат
На самом деле все достаточно просто – вместо числа в ячейке появляется дата по причине того, что для нее задан такой формат (“Дата”). Следовательно, чтобы мы видели именно числовое значение, требуется всего лишь сменить формат на тот, который нам нужен. Справиться с этим можно по-разному.
Метод 1: используем контекстное меню
Данное меню пользуется наибольшей популярностью среди пользователей Excel для решения множества задач. В нашем случае нам оно тоже может помочь.
Правой кнопкой мыши кликаем по отдельной ячейке или заранее выделенному диапазону ячеек (например, с помощью зажатой левой кнопки мыши). В появившемся меню выбираем команду “Формат ячеек”.
Мы должны оказаться в окне форматирования во вкладке “Число”. В поле “Числовые форматы” меняем формат “Дата” на “Числовой” (в некоторых случаях, если требуется, можно выбрать “Общий”, “Денежный” или “Текстовый”)
Обращаем вниманием на область по надписью “Образец” – именно так будет отображаться содержимое ячейки после применения настроек. По готовности нажимаем OK.
Примечание: некоторые выбранные форматы можно настраивать, например, для числового можно задать количество десятичных знаков, а также включить/отключить разделитель групп разрядов
Таким образом, нам удалось получить в выбранной ячейке (диапазоне ячеек) конкретное число вместо даты.
Метод 2: пользуемся инструментами на ленте
Этот способ используется реже, чем первый, несмотря на то, что является более простым и реализуется буквально в один клик.
- Встаем в нужную ячейку или выделяем требуемый диапазон элементов.
- Переключаемся во вкладку “Главная”, если находимся в другой, после чего в группе инструментов “Число” щелкаем по стрелке вниз рядом с текущим форматом (в нашем случае – это “Дата”).
- В раскрывшемся списке выбираем формат, который нам нужен (например, “Числовой”).
- Получаем числовые значения вместо дат. Но здесь есть важный нюанс – в отличие от первого метода, возможности детально настроить выбранные форматы у нас нет.
Примечание: Если щелкнуть по пункту “Другие числовые форматы” в конце списка, то откроется окно форматирования, с которым мы уже познакомились ранее.
Метод 3: жмем горячие клавиши
Этот способ используется редко, так как для его реализации нужно запомнить специальную комбинацию клавиш, и по сути, он является альтернативой использованию контекстного меню, позволяя обойтись без него.
Все, что нам нужно сделать – выбрать ячейку (или выделить диапазон), после чего нажать на клавиатуре сочетание Ctrl+1, после чего откроется уже знакомое окно форматирования.
Как отключить автоматический ввод даты в Excel?
Как убрать автоматический ввод даты в Excel?
Автозамена числа на дату в Excel 2003
- Нажмите на круглую кнопку меню Excel 2007 (в левом верхнем углу программы).
- Нажмите на «Параметры Excel». …
- Выберите пункт меню Правописание и, справа, нажмите на кнопку Параметры автозамены:
- В появившемся окне снимите отметку с чекбокса «Заменять при вводе» и нажмите на ОК:
Почему в Excel пишет дату?
Единственной причиной, почему данные в ячейке могут отображаться как дата, является то, что в ней установлен соответствующий формат. Таким образом, чтобы наладить отображение данных, как ему нужно, пользователь должен его поменять.
Как поменять в Экселе дату на цифры?
Для этого проще всего взять за основу формат, который ближе всего к нужному формату.
- Выделите ячейки, которые нужно отформатировать.
- Нажмите сочетание клавиш CTRL+1. …
- В диалоговом окне Формат ячеек откройте вкладку Число .
- В списке Категория выберите пункт Дата и выберите нужный формат даты в поле Тип .
Как отключить автоматическое форматирование в Excel?
Одновременное настройка всех параметров автоматического форматирования
- Выберите Файл > Параметры.
- В окне «Параметры Excel»щелкните «Параметры >».
- На вкладке «Автоформат при типе» отведите флажки для форматирования, которые вы хотите использовать.
Как сделать чтобы в Экселе число не менялось на дату?
Для этого необходимо задать формат ячейки – что в ней будет находиться число, а не цифра. Для этого, щелкаем правой кнопкой мыши по ячейке и выбираем вкладку “Формат ячеек”. В ней выбираем вариант “Текстовый”. Все, после этого в данной ячейке Excel даже набрав цифры с точкой у вас не появится число и месяц.
Как сделать чтобы дата менялась в Excel?
Как поставить текущую дату в Excel
Данная функция не имеет аргументов, поэтому вы можете просто ввести в ячейку: «=СЕГОДНЯ()» и нажать ВВОД. Если же необходимо чтобы в ячейке автоматически обновлялось значение не только текущей даты, но и времени тогда лучше использовать функцию «=ТДАТА()».
Как сохранить дату в текст Excel?
Для отображения даты в нужном нам формате используем функцию ТЕКСТ() : =»Cегодня «&ТЕКСТ(A1;»дд. ММ. гг») . Получим желаемое — «Сегодня 02.10.10».
Как в Excel преобразовать дату в год?
- Преобразование строки даты в дату = ЛЕВСИМВ (дата; 10) + ПСТР (дата; 12;8) …
- Преобразовать дату в Юлианский формат = ГОД (дата) и ТЕКСТ (дата-ДАТА (ГОД (дата); 1;0); «000») …
- Преобразование даты в месяц и год …
- Преобразование даты в текст …
- Преобразование даты текста дд/мм/гг в мм/дд/гг …
- Преобразование текста в дату
Как в Экселе убрать дату в ячейке?
Выберите ячейки, в которые необходимо ввести числа. Щелкните Главная > Числовой формат > Текст.…Отмена автоматической замены чисел датами
- Пробел перед вводом числа. …
- Апостроф (‘) перед вводом числа, например ’11-53 или ‘1/47.
Как в Excel изменить дату на месяц?
Чтобы показать его как дату, необходимо выбрать ячейку (или ячейки) и нажать Ctrl+1 . Откроется диалоговое окно Формат ячеек (Format Cells). На вкладке Число (Number) в списке числовых форматов выберите Дата (Date) и затем укажите нужный вам формат.
Как отключить быстрый анализ Excel?
1. Нажмите Отправьте > Опции , чтобы перейти к Параметры Excel диалоговое окно. 2. В диалоговом окне нажмите Общие на левой панели, а затем снимите флажок Показывать параметры быстрого анализа при выборе вариант под Параметры пользовательского интерфейса раздел, см.
Как отключить автоматическое создание гиперссылок в Excel?
Выберите Файл ► Параметры, чтобы открыть диалоговое окно Параметры Excel. Перейдите в раздел Правописание. Нажмите кнопку Параметры автозамены для отображения диалогового окна Автозамена. Перейдите на вкладку Автоформат при вводе и снимите флажок адреса Интернета и сетевые пути гиперссылками (рис.
Как убрать в Экселе?
Как вы знаете, удалить данные/информацию в ячейках Excel можно:
- нажатием кнопки Delete на клавиатуре, после выделения нужной области
- нажатием кнопки Удалить содержимое Главная — раздел Редактирование — в нем жмите кнопку Очистить содержимое (см. описание выше)
- можно удалить не только данные, но и сами ячейки.
Примеры практического применения функций для работы с датами
Эти примитивные функции очень полезны при группировки данных по: годам, месяцам, дням недели и конкретным дням.
Допустим у нас имеется простой отчет по продажам:
Нам нужно быстро организовать данные для визуального анализа без использования сводных таблиц. Для этого приведем отчет в таблицу где можно удобно и быстро группировать данные по годам месяцам и дням недели:
Теперь у нас есть инструмент для работы с этим отчетом по продажам. Мы можем фильтровать и сегментировать данные по определенным критериям времени:
Кроме того, можно составить гистограмму для анализа самых продаваемых дней недели, понять на какой день недели приходится наибольшее количество продаж:
В таком виде очень удобно сегментировать отчеты по продажам за длительные, средние и короткие периоды времени.
Стоит сразу отметить что для того чтобы получить разницу между двумя датами нам не поможет ни одна из выше описанных функций. Для данной задачи следует воспользоваться специально предназначенной функцией РАЗНДАТ:
Тип значений в ячейках «дата» требует особого подхода при обработке данных. Поэтому следует использовать соответствующие данному типу функции в Excel.
Формат даты и времени в Excel
Работая с ячейками в Microsoft Excel, которые содержат дату или время, Вы имеете возможность применять к ним различное форматирование. Например, Вы можете отображать в ячейке только день недели, или только месяц и год, или же только время.
Получить доступ ко всем возможным форматам Вы можете на вкладке Число диалогового окна Формат ячеек. В категории Дата приведены встроенные форматы дат:
А в категории Время – встроенные форматы времени:
Для того чтобы применить форматирование к ячейке, достаточно выбрать нужный формат в разделе Тип и нажать ОК. Нужное форматирование будет применено. Если встроенных числовых форматов Вам будет недостаточно, то можно воспользоваться категорией Все форматы. Здесь Вы также сможете найти много всего интересного.
Если ни один из вариантов Вас так и не устроил, то можно создать пользовательский числовой формат. Его создать достаточно легко, если в качестве образца использовать встроенные числовые форматы. Чтобы создать пользовательский числовой формат, выполните следующие действия:
- Выделите ячейку, к которой необходимо применить требуемый числовой формат.
- Откройте диалоговое окно Формат ячеек и перейдите на вкладку Число.
- В категории Все форматы в поле Тип введите необходимый код числового формата. В нашем случае мы введем: ДДДД ДД.ММ.СС чч:мм:cc и нажмем ОК.
- Пользовательский числовой формат будет применен к ячейке.
Как видите все достаточно просто!
В этом уроке мы научились настраивать формат отображения дат и времени в Microsoft Excel, а также разобрали несколько полезных вариантов их ввода на рабочий лист. В следующем уроке мы поговорим о 7-ми функциях Excel, которые позволяют извлекать нужные параметры из значений дат и времени. На этом урок закончен. Всего Вам доброго и успехов в изучении Excel.
Как преобразовать дату в текст в Excel
В Excel каждой дате соответствует определенное число дней, прошедших с принятой точки отсчета – 1 января 1900 года. Функция ДАТАЗНАЧ возвращает число, соответствующее числовому представлению даты, которая указана в виде текста, с учетом указанной выше особенности хранения дат в Excel. Формат возвращаемого значения зависит от настроек формата ячейки, в которой будет выведен результат вычислений.
Зачастую даты в Excel записывают без использования функции ДАТА. Табличный редактор определяет такие значения как обычные текстовые строки. Поэтому процедуры форматирования, сортировки по дате, а также различные вычисления (например, разница дат) приводят к некорректным результатам или появлению ошибок. Поэтому функция ДАТАЗНАЧ полезна для преобразования текстовых значений к данным формата Дата.
Пример 1. В таблице Excel находится столбец, в котором хранятся даты как текстовые строки, при этом записи имеют вид: «28 сентября 2018 года». Преобразовать эти значения в данные формата Дата.
Вид таблицы данных:
Для получения даты в формате, поддерживаемом Excel, используем следующую функцию:
Единственный аргумент состоит из подстрок, склеенных амперсандами (&):
Функция ЛЕВСИМВ возвращает номер дня (первые два символа строки, содержащейся в ячейке A2)
Очень важно, чтобы однозначные номера дней (например, 8 апреля) записывались как 08 апреля (имели нуль в начале), иначе будет возникать ошибка.
Комбинация функций ПСТР и ЛЕВСИМВ выделяет из строки три первых символа названия месяца и возвращает их.
Комбинация функций ПСТР и ПРАВСИМВ выделяет 4 символа, соответствующие числовому представлению года.. Растянем формулу вниз по столбцу, чтобы рассчитать остальные значения:
Растянем формулу вниз по столбцу, чтобы рассчитать остальные значения:
Таким образом мы преобразовали текстовые строки в формат даты, которые теперь можно использовать для вычислений в формулах.
Отмена автоматической замены чисел датами
Microsoft Excel предварительно программируется, чтобы упростить ввод дат. Например, 12/2 изменяется на 2-дек. Это очень неприятно, если вы вводите что-то, что не хотите менять на дату. К сожалению, отключить эту возможность не получится. Однако существует несколько способов обойти это.
Предформатация ячеек, в которые нужно ввести числа, в виде текста. В этом случае Excel не будет пытаться изменить то, что вы вводите в даты.
Если нужно ввести всего несколько чисел, можно сделать так, чтобы Excel не превнося их в даты, введите:
Пробел перед вводом числа. После нажатия ввод пробел останется в ячейке. (См. заметки)
Апостроф (‘) перед вводом числа, например ’11-53 или ‘1/47. Апостроф не отображается в ячейке после нажатия ввода.
Нуль и пробел перед вводом дроби, например 1/2 или 3/4, чтобы они не менялись, например, на 2-янв или 4-мар. Введите 0 0 1/2 или 0 3/4. Ноль не остается в ячейке после нажатия ввод, и ячейка становится типом дробного числа.
Выйдите из ячеек, в которые нужно ввести числа.
Нажмите клавиши CTRL+1 (1 в строке чисел над клавишами QWERTY), чтобы открыть формат ячеек.
Выберите тексти нажмите кнопку ОК.
Выберите ячейки, в которые необходимо ввести числа.
Щелкните Главная > Числовой формат > Текст.
Рекомендуется использовать апостроф вместо пробела для ввода данных, если вы планируете использовать функции подменю для данных. Такие функции, как ПОИСКПОЗ и ВПР, не учитывают апострофы при вычислении результатов.
Если число в ячейке выровнено по левому краю, обычно это означает, что оно не отформатировано как число.
При введении в ячейку числа с буквой «е», например 1e9, оно автоматически преобразуется в научное число: 1,00E+09. Чтобы избежать этого, введите перед числом апостроф: ‘1e9
В зависимости от введенного числа в левом верхнем углу ячейки может появиться маленький зеленый треугольник, указывающий на то, что число хранится как текст ,что для Excel является ошибкой
Не обращайте внимание на треугольник или щелкните его. Слева появится поле
Щелкните поле, а затем выберитеигнорировать ошибку , из-за чего треугольник будет отопуститься.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Использование текста в столбце
Еще один простой способ преобразования текста в числа в Excel – это одновременное преобразование целого столбца значений. Вы можете сделать это, используя функцию Text to Column.
- Выберите весь столбец данных, которые вы хотите преобразовать из текста в числа.
- Выберите «Данные» в меню, а затем выберите «Текст в столбцы» в разделе «Инструменты данных» на ленте.
- В окне мастера оставьте выбранным разделитель по умолчанию и выберите Далее.
- На следующей странице мастера оставьте выбранную вкладку по умолчанию и снова выберите Далее.
- Наконец, на последней странице мастера убедитесь, что в разделе Формат данных столбца выбрано Общее. В поле «Место назначения» вы можете выбрать новый столбец, в который нужно перенести числовые данные, или просто оставить текущий выбранный столбец без изменений. Выберите Готово.
Теперь все ваши данные будут преобразованы в числовые значения, которые вы можете использовать в формулах и вычислениях Excel.
Примечание. Вы заметите, что фактическое форматирование ячейки не меняется с Текст на Общее, даже если сами значения теперь можно использовать в качестве чисел. Однако, если вы установите выходной столбец в новый столбец, вы заметите, что форматирование нового столбца установлено на Общие. Это только косметическая проблема, и она не влияет на поведение чисел в столбце в формате «Текст».
Как Excel обрабатывает время
Как записать месяц прописью?
Как и в примере выше воспользуемся функцией ТЕКСТ. Формат «ММММ» даст нам полную запись месяца (в именительном падеже):
Ещё одним способом является совместное применение функций МЕСЯЦ (в английской версии MONTH) и ВЫБОР (английский вариант CHOOSE).Месяц вернёт нам порядковый номер месяца указанной даты (от 1 до 12), а ВЫБОР сопоставит числовому значению текстовое (где 1 — январь, 2 — февраль, …, 12 — декабрь):
Если же мы пишем число с месяцем и годом, то зачастую месяц нужно указать в родительном падеже (а не в именительном как в примере выше), например, 2 февраля 2015 года или 14 августа 2012 года и т.п.
Здесь нам опять поможет формат ММММ, который записывает месяц в дате в родительном падеже, отличие от предыдущего варианта записи только в том, что мы убрали из записи день и год:
Есть и альтернативный вариант. На помощь опять придут уже знакомые функции МЕСЯЦ и ВЫБОР, только тут вместо именительного падежа для месяцев прописываем родительный (меняем окончания):
На этом все. Выбирайте наиболее понравившийся и удобный для вас способ.