Auditportal.ru

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

Как извлечь время или час только из даты и времени в Excel?

Введите время без двоеточий (ччммсс) и рассчитайте разницу во времени в Excel

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

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

3 ответа 3

Один из вариантов — объединить форматирование ячеек с функциями ВРЕМЯ в Excel.

Создайте текстовый файл с ячейками для ввода времени (в моем примере A2 и B2). Предполагается, что формат всегда будет hhmmss, поэтому введите ноль для начальной точки. Если вы используете эту формулу, вы можете рассчитать:

Он отображает крайние левые 2 символа как «Часы», средние 2 символа как «Минуты» и крайние правые 2 символа как «Секунды» и преобразует их в то, что Excel распознает как время. Затем он вычитает эти два значения и выводит результат в формате hhmmss :

EDIT: Поскольку требование не совсем такое, как указано в вопросе, я изменил формулу, добавив ведущие нули:

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

Если это так, то я предполагаю, что вам действительно нужен результат в формате hh:mm:ss.

Вот еще один способ ввести свое время так, как вам удобно:

Скриншот

Чтобы сделать действие более наглядным, я оставил начальное и конечное время в виде числовых значений. В этом примере время начала действия — 25 секунд после полуночи или полудня, что равно 000025. Если вы отформатируете число 00:00:25 как число, оно будет сохранено как 25.

Этот подход отделяет часы и минуты на основе их положения в числе, равном 100. Неважно, как отображаются секунды, они всегда являются правильными двумя цифрами. Формула в C2:

Функция MOD является противоположностью функции INT. Она выдает остаток после деления.

Как выяснилось, я наткнулся на этот форум, когда искал способы сэкономить время при вводе табелей учета рабочего времени, не ставя «двоеточия» в табелях, а делая двоеточие видимым, чтобы можно было произвести расчет. Все это без использования VBA Script, скрытых ячеек и формул и т.д. и т.п. Похоже, он также работает с ведущими нулями, 24-часовым временем и расписанием, охватывающим полночь (хотя расписание нужно будет скорректировать, если вы ожидаете, что оно будет охватывать несколько полуночей, но я не видел необходимости в этом в моем случае).

Читайте так же:
Как использовать командную кнопку для сохранения активного рабочего листа в виде файла PDF в Excel?

Мне тоже не нужны «секунды», но их легко можно увеличить, если следовать этой концепции:

Настройте форматирование начальной и конечной ячеек:

Это превращает их в «текстовые поля», но гарантирует, что числа всегда отображаются четырьмя цифрами (даже с ведущими нулями или нулями для американцев).

Обратите внимание, что в формате времени можно ввести ЛЮБОЕ четырехзначное число. Следовательно, теоретически вы можете ввести 0768, что создаст время 07:68, но этого не существует. Поэтому либо

а) создайте список проверки данных следующим образом:- на отдельном рабочем листе (вкладке) в одном столбце создайте список всех допустимых значений. В моем случае это были 0500, 0515, 0530, 0545 . 0445, поскольку я имел дело только с 15-минутными приращениями. Если вам нужно детализировать до секунд (серьезно?) это будет довольно долго . т.е. 050001, 050002 . и т.д. Или вы можете написать другую формулу просто для создания этого списка, а затем скопировать текстовые данные (только для вставки значений) в новый столбец, чтобы удалить формулы (я бы). - Выберите все эти ячейки с «допустимыми значениями» и на вкладке «Forumulas» панели инструментов выберите «Define Name». Дайте ему имя типа «MonkeyTimeList». - Вернитесь к своему рабочему листу, выберите все ячейки, охватывающие время начала и окончания, и перейдите на вкладку «Данные» панели инструментов, выберите «Проверка данных> Проверка данных> Настройки», в разделе «Разрешить» выберите « List «из выпадающего списка, в разделе» Source «введите имя, которое вы определили, перед которым стоит» = «, поэтому в моем случае» = MonkeyTimeList «. Так как вы нажимаете «ОК», выбираете вкладку «Предупреждение об ошибке», ставите флажок «Показать предупреждение об ошибке . » и в раскрывающемся списке «Стиль» выбираете «Стоп». Не стесняйтесь добавлять сообщение об ошибке типа «Человек-обезьяна говорит, что вы идиот», попробуйте ввести время в ПРАВИЛЬНОМ ФОРМАТЕ!». Затем нажмите «ОК». - Это гарантирует, что только данные из вашего «приемлемого» списка могут быть введены как время.

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

Читайте так же:
Как изменить все заглавные буквы на строчные, кроме первой буквы в Excel?

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

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

S O. СЕЙЧАС ИСКЛЮЧЕНЫ ЧАСЫ от времени начала до времени окончания. опять же, это основано только на минутах. но если вы можете следовать логике, это можно расширить до секунд.

Если вы используете столбец A в качестве времени начала, B в качестве времени окончания и C в качестве количества часов, введите следующую формулу в третьем столбце:

= ЕСЛИ (ЗНАЧЕНИЕ (В2)> = ЗНАЧЕНИЕ (А2), (СУММА (ЗНАЧЕНИЕ (MID (ТЕКСТ (В2, «0000»), 1,2)), ЗНАЧЕНИЕ (MID (ТЕКСТ (В2, «0000»), 3 , 2))/60))- (SUM (ЗНАЧЕНИЕ (MID (ТЕКСТ (А2, «0000»), 1,2)), ЗНАЧЕНИЕ (MID (ТЕКСТ (А2, «0000»), 3,2))/60)), (СУММА (ЗНАЧЕНИЕ (MID (ТЕКСТ (В2, «0000»), 1,2)), 24, ЗНАЧЕНИЕ (MID (ТЕКСТ (В2, «0000»), 3,2))/60))- (SUM (ЗНАЧЕНИЕ (MID (ТЕКСТ (А2, «0000»), 1,2)), ЗНАЧЕНИЕ (MID (ТЕКСТ (А2, «0000»), 3,2))/60)))

Теперь это должно быть уточнено.

Функция «IF» различает конечное время на 24-часовых часах до полуночи и после полуночи. Если время уже за полночь, к времени окончания добавляются 24 часа, чтобы можно было рассчитать время окончания и время начала. Если время до полуночи, нет необходимости добавлять 24 часа. Таким образом, для целей расчета время 0100 фактически рассматривается как 2500, 0200 как 2600 и так далее. NOTe. Это предполагает, что работа длится не более 23 часов 45 минут. Если он длиннее, возможно, потребуется включить в формулу столбцы дат. Опять же, они мне не нужны.

Функция MID работает с TEXT (отсюда и функция TEXT) и возвращает цифры, расположенные в определенных позициях в указанной ячейке в заданном формате. Так, «MID (TEXT (B2, «0000»), 1,2)» просматривает ячейку B2 и всегда рассматривает ее в 4-значном/буквенном формате и возвращает две цифры, начиная с позиции «1». В моем случае это целые часы. Другой пример: «MID (TEXT (A2,» 000000 «), 5,2)» будет искать в ячейке A2 и вернет две цифры, начиная с позиции «5», которые могут быть целыми секундами.

V ALUE преобразует возвращаемый текст в число, позволяя нам работать с ним в процессе вычисления.

Таким образом, он позволяет нам обрабатывать часы, минуты и секунды отдельно в каждой ячейке. С часами все просто, так как они уже являются целыми числами. На самом деле, минуты — это доли часа или доли 60 минут, поэтому разделите числа «минут» на «60», чтобы получить доли часа. Дробь часа преобразуется путем деления секунд на 3600 (количество секунд в часе).

Читайте так же:
Как изменить поворот 3D-диаграммы в Excel?

Теперь, когда времена представлены в виде чисел с дробями, вы можете складывать и вычитать по своему усмотрению. В моей формуле это даст общее количество часов между начальным и конечным временем. Я форматирую ячейку с формулой как Custom> 00.00, что позволяет умножить ее на почасовую ставку и т.д.

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

Удалить секунды из числа даты-времени в Excel

У меня есть столбец даты и времени с числами «DD/MM/YYYY HH:MM:SS». На данном этапе я хочу удалить «SS» и оставить «DD/MM/YYYY HH:MM». Я пробовал функцию DATE и TIME отдельно, но осталась только одна часть, DATE: «DD/MM/YYYYY» и TIME: «HH:MM», я также пробовал функцию CONCATENATE, но она не работает, кто-нибудь может мне помочь? Заранее спасибо.

6 ответов

  • Преобразование формата времени mm.ss.dd в секунды в МС Excel

В моем файле Excel время отформатировано как mm.ss.dd (где dd — десятичное число). Примеры: 12.54.88 4.6.21 Каково преобразование в секунды?

Как удалить секунды из поля времени? Я пытался: =Rset(Fields!hr_saida.Value,5) = return #Error =TimeValue(Fields!hr_saida.Value) = return #Error =FormatDateTime(CDate(Fields!hr_saida.Value),hh:mm) = return #Error Я также попытался установить свое свойство текстового поля в Number > 13:30, но все.

Если вы хотите полностью удалить секунды из данных. Попробуйте: =1*TEXT(C2, «dd/mm/ååh:mm»)

После умножения результата на 1 текст превращается обратно в число.

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

Нажмите OK , и все готово.

Ctrl+1 Custom Number Format

Вы можете попробовать LEFT . Если я правильно понял ваш вопрос и у вас есть DD/MM/YYYY HH:MM:SS в той же ячейке, вы можете использовать =LEFT(cell,14)

Попробуйте использовать эту формулу

Попробуйте =TEXT(A1,»DD/MM/YY HH:MM»)

В столбце находится дата «27/01/2015 18:43:00»

Я изменил их так, чтобы они отображались как «42031.78».

Затем я разделил данные/текст точкой или черточкой.

Появляются две колонки, «42031.00» и «7798611111.00

Вторая колонка была удалена, а первоначальная колонка была изменена на формат краткой даты «27/01/2015».

Никаких часов и секунд!

Похожие вопросы:

В моей базе данных есть два типа дат: одна в формате DateTime, а другая в строковом формате. Оба формата имеют следующую структуру: yyyy-MM-dd HH: mm: ss. Однако я хочу удалить HH: mm: ss, поскольку он не требуется.

Читайте так же:
Как импортировать несколько текстовых файлов из папки на один лист?

Есть созданный мной объект дата-время, который хранится в созданном datetime. Объект Datetime должен быть очищен от секунд. Я хочу удалить значение 4/6/2016 5:32:00 PM, возвращаемое моим объектом даты и времени.

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

У меня есть файл Excel с временем в таком формате: mm.ss.dd (dd — десятичная точка). Примеры: 12.54.88 4.6.21 Как перевести это в секунды?

Как удалить секунды из поля времени? Я пытался: =Rset(Fields!hr_saida.Value,5) = return #Error =TimeValue(Fields!hr_saida.Value) = return #Error =FormatDateTime(CDate(Fields!hr_saida.Value),hh:mm) =.

Мне нужно удалить секунды из возвращаемого значения времени. В примере я получаю 12:00:00pm, но мне нужно 12:00pm. Я пробовал date(), но он всегда возвращал 1:00 утра. echo

Как в C# преобразовать секунды в формат даты и времени? Формат даты и времени следующий. YYYY-MM-DD HH:MM:SS . У меня было время в секундах, а не в миллисекундах. Я хочу его.

Метод Date.prototype.toLocaleTimeString() возвращает строку с чувствительным к языку представлением временной части этой даты. Он доступен для современных браузеров. К сожалению, родная функция этого не делает.

В Excel есть ячейки для даты и времени. Ячейки даты и времени объединены в столбец. При копировании значения даты и времени не вставляются, скажем, в Notepad++.

Что нужно сделать, чтобы очистить секунды из объекта даты, чтобы его можно было преобразовать обратно в дату? let testDate = new Date(2020, 05, 03, 1, 2); Этот ответ преобразуется в строку ISO, мне это не нужно, но я возвращаю его.

Функции даты и времени в Excel

Даты хранятся в Excel в виде последовательных чисел. Дата 1 января 1900 года — это порядковый номер 1, а дата 1 января 2008 года — порядковый номер 39448. Десятичная дробь времени хранится в Excel (время является частью даты).

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

Excel имеет несколько встроенных функций для вычисления дат. Все встроенные функции, связанные с датой, можно разделить на функции, отображающие дату (или ее часть), и функции, выполняющие вычисления по датам. Функции отображения даты включают:

— DATE и TIME, которые возвращают числовые значения даты или времени;

Читайте так же:
Как извлечь часть текстовой строки из ячейки в Excel?

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

— YEAR, MONTH, DAY, TIME, MINUTES, SECONDS, которая возвращает значение искомого параметра из полной даты;

— TODAY и TDATA, которые возвращают переменную дату, соответствующую сегодняшней дате согласно встроенному календарю и компьютерным часам. При использовании TODAY возвращается только дата, а при использовании TDATA — дата и время;

— NUMBER, который возвращает неделю года, на которую выпадает указанная дата;

— DAYNED, который возвращает номер дня недели, соответствующего дате

Даты можно рассчитать с помощью следующих функций:

— DATAMES, которая возвращает дату в числовом формате, которая находится на указанное количество месяцев вперед или назад от указанной даты;

Он возвращает количество дней между двумя датами на основе 360-дневного года (двенадцати 30-дневных месяцев);

Функция DOLLARS возвращает долю года, разделяющую две даты;

— CONMOND, который возвращает числовой формат последнего дня месяца с указанным количеством месяцев от даты;

— RABDEN, который возвращает число, представляющее дату, которая опережает или отстает на указанное число рабочих дней;

— CHOICE, который дает количество рабочих дней между двумя датами.

Даты также можно вычислять в Excel с помощью простых арифметических операций. Например, количество дней между датами может быть рассчитано путем вычитания нижней даты из верхней. Количество лет между датами можно рассчитать, разделив полученный результат на 365 (рис. 22).

Чтобы включить эти функции или исправить сообщение об ошибке #IMY?, необходимо установить дополнение Analysis Package (команда Add-ons в меню Tools).

Отображение дат в текстовом формате можно осуществлять с помощью функции ТЕКСТ, которая преображает значение в текст в заданном числовом формате. Например, функция «=ТЕКСТ(ДЕНЬНЕД(A2;2);"ДДДД")», помещенная в любую ячейку листа, изображенного на рис. 22, выдаст значение «суббота». Тогда как функция «=ДЕНЬНЕД(A2;2)» — значение «6». Отображать части дат в текстовом формате можно и с помощью формата ячеек. Так, если ячейку А2 (рис. 23) отформатировать в соответствии со встроенным форматом «март», то в ней будет значение «Январь».

Если вы хотите отобразить дни в часах, вы можете использовать формат ячейки. Например, если ячейка C2 (рисунок 22) отформатирована в соответствии со встроенным форматом «37:30:55», в ней будет отображено значение «80328:00:00:00».

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