Загрузка...Как преобразовать часы, минуты и секунды в десятичные часы в Excel?
Как преобразовать время в десятичное число в Google Таблицах
Иногда вы можете столкнуться с ситуацией, когда вам нужно преобразовать время в десятичное число в электронных таблицах Google. Предположим, вам нужна разница между двумя моментами времени. Интуитивно понятнее было бы сказать, что разница составляет 3,25 часа, а не 3 часа и 15 минут. Это особенно важно, если вам нужно использовать эту разницу в последующих расчетах, таких как общая заработная плата на основе отработанных часов и почасовая заработная плата.
И как это делается? В Google Spreadsheets есть несколько способов получить желаемый результат: использование функций ВРЕМЯ (), МИНУТА () и СЕКУНДА () или простое умножение с помощью функции TIMEVALUE (). Мы рассмотрим их в этом руководстве.
Как преобразовать время в десятичные числа в Excel
Используя формулы, я покажу вам примеры преобразования времени в десятичные числа (например, перевод времени в часы, минуты и секунды).
В Excel время можно перевести в десятичную систему счисления несколькими способами:
- Использование арифметических операций — самый простой способ преобразовать время в десятичные числа. Например:
- Чтобы перевести время в часы, умножьте значение времени на 24
- Чтобы перевести время в минуты, умножьте значение времени на 24*60
- Чтобы перевести время в секунды, умножьте значение времени на 24*60*60.
В следующих разделах я покажу вам примеры преобразования часов Excel в десятичные значения с помощью этих методов.
Преобразование времени в часы в Excel
В этом разделе я рассмотрю три различных способа преобразования времени в часы в Excel.
Использование простого умножения для получения значения часа
Предположим, что время в ячейке 18:00 (в ячейке A2), и вы хотите преобразовать его в часы.
Просто умножьте его на 24, и вы узнаете, сколько часов прошло в это время.
Это работает потому, что Excel хранит дату и время в виде чисел. Excel хранит полный день (24 часа) как 1. Другими словами, каждый час хранится как 1/24.
Поэтому, даже если в бэкенде вы видите значение 18:00, оно хранится как значение 0,75 (указывающее на то, что 18 из 24 часов уже прошли). Таким образом, если вы умножите на 24, вы получите количество прошедших часов.
Если у вас есть такое значение, как 18:32, использование приведенной выше формулы даст результат 18,53 (где часть минут/секунд отображается в десятичном формате, а часть полный/часовой — в виде целых чисел). Числовое значение 32 минут в Excel равно 0,53 часа.
Примечание. Если вы получаете значение часа с десятичными дробями (например, 18.53), убедитесь, что вы отформатировали его так, чтобы отображались и десятичные дроби. Если вы этого не сделали, вы можете увидеть результат как 19, поскольку Excel показывает округленное целое число. Однако это не повлияет на значение в ячейке, которое останется 18,53.
Если вы хотите получить только полное значение часа и игнорируете минутную часть, используйте следующую формулу с INT.
INT просто дает вам целый кусок, и вам не нужно беспокоиться о лишних минутах.
Использование функций Excel (ЧАС, МИНУТА и СЕКУНДА)
Если вам не нравится ручной метод умножения, еще один действительно простой способ перевести время в часы — использовать встроенные функции, связанные со временем (ЧАСЫ, МИНУТЫ и СЕКУНДЫ).
Предположим, у вас есть время, показанное ниже, и вы хотите узнать, сколько часов прошло за сутки.
Формула для пересчета времени в часы приведена ниже.
Функция ЧАС, как и функции МИНУТЫ и СЕКУНДЫ, возвращает количество часов, прошедших за определенное время.
Но поскольку нам нужно конечное значение в часах, нам нужно разделить значение минут на 60 (чтобы преобразовать его в часы) и второе значение на 60 * 60 (опять же, чтобы преобразовать его в часы).
Использование функции преобразования
Вы также можете использовать функцию ПРЕСКРИПЦИЯ, пожалуй, самую простую из всех трех методов.
Эта функция преобразует числовое значение (в данном случае время) в часы (или в минуты/секунды).
Ниже приведена формула, которая переводит время в часы:
Во-вторых, функция используется для определения существующего формата данных, а в-третьих, вы указываете новый формат, в который хотите преобразовать.
Иногда результаты могут отличаться от ожидаемых. Это может быть связано с форматом ячейки, которая может отображать результат в формате даты, а не в десятичном формате. Это можно легко изменить, перейдя на вкладку Главная и установив формат в раскрывающемся списке (он находится в группе Число).
На практике вы, скорее всего, получите дату и время вместе (а не только время, как я показал в примерах выше). Вы все еще можете использовать методы, показанные выше, но сначала вы должны разделить значения даты и времени, а затем использовать методы, показанные выше, только для временной части. Позже в этом учебнике я также расскажу о двух методах разделения даты и времени в Excel с помощью формул.
Кроме того, вы можете конвертировать время в минуты и секунды, используя те же методы, которые описаны выше.
Давайте рассмотрим эти примеры.
Преобразование времени в минуты в Excel
Ниже приведены три различных метода преобразования времени в минуты с помощью Excel.
Использование простого умножения для получения минутного значения
В сутках 1440 минут (24 * 60)
Поэтому, чтобы перевести время в минуты, просто умножьте значение времени на 1440.
Рассмотрим ситуацию, когда у вас есть время 18:00 (в ячейке A2) и вы хотите преобразовать его в минуты.
Ниже приведена формула:
Это позволит узнать общее количество минут, прошедших с указанного времени в этот день.
Помните, что если во времени есть секунды, они будут преобразованы в минуты и показаны в десятичной форме. Полученные ячейки должны быть отформатированы так, чтобы в них отображались результаты с точностью до 2/3 десятичных знаков, чтобы они отображались в результате.
Если вам нужно только значение минут, а секундная часть игнорируется, используйте следующую формулу, используя INT.
Использование функций Excel (ЧАС, МИНУТА и СЕКУНДА)
Те же функции, которые мы использовали для получения значения часов, можно так же легко использовать для преобразования времени в минуты.
Ниже приведена формула, преобразующая время в минуты с помощью этих функций:
Поскольку цель здесь — получить все части в минутах (часы, минуты и секунды), мы умножаем 60 на часы, чтобы получить значение минут в них, и делим секунды на 60, чтобы получить значение минут.
Использование функции преобразования
Опять же, формула преобразования является самым простым методом перевода времени в минуты.
В данной формуле время переводится в минуты;
Второй аргумент сообщает функции, что значение в ячейке A2 имеет формат дня, а третий аргумент («mn») говорит ей, что нужно преобразовать его в минуты.
Преобразование времени в секунды в Excel
В этом разделе я рассмотрю три разных способа преобразования времени в секунды в Excel.
Использование простого умножения для получения значения секунд
В одном дне 86400 секунд (24 * 60 * 60)
Поэтому, чтобы перевести время в секунды, просто умножьте значение времени на 86400
В ячейке A2 указано время 18:32:30, которое нужно преобразовать в секунды.
Для этого используется следующая формула:
Это даст вам общее количество секунд, прошедших в этот день в показанном времени.
Использование функций Excel (ЧАС, МИНУТА и СЕКУНДА)
Ниже приведена формула, которая переводит время в минуты с использованием этих функций:
Чтобы получить все части (часы, минуты и секунды) в секундах, мы умножим 60*60 на часы, чтобы найти секунды, затем умножим минуты на 60, чтобы найти секунды.
Умножение времени на коэффициент
К одному из самых простых методов, позволяющих сделать перевод часов в минуты, относится умножение времени на коэффициент. Учитывая тот момент, что программа Excel оперирует временем в сутках, необходимо имеющееся выражение умножить на 60 и на 24, где 60 — количество минут в часах, а 24 — количество часов в сутках. В результате данной выкладки производим умножение 60*24 и получаем коэффициент равный 1440. Зная теоретические сведения, можно перейти к практическому применению рассматриваемого способа.
- Чтобы сделать это в ячейке, где программа будет отображать конечный результат в минутах, сначала установите формат «Общий», затем выделите и поставьте знак равенства в ячейке.
- Затем нажмите на ячейку с информацией о часах. Вставьте в эту ячейку знак умножения и введите 1440.
- Чтобы Excel обработал введенные данные и получил результат, нажмите клавишу «Enter». И это все. Программа выполнила преобразование.
Как преобразовать часы, минуты и секунды в десятичные часы в Excel?
Как отформатировать список времени, отображаемый в десятичных часах, в hh: mm: ss (часы, минуты и секунды)? В следующих методах вы найдете решения для преобразования часов, минут, секунд и десятичных часов.
Преобразуйте часы, минуты и секунды в десятичные числа одним щелчком мыши
Преобразуйте часы, минуты и секунды в десятичные числа с помощью формулы
Предположим, у вас есть список времени в формате часов, минут и секунд в столбце A, и чтобы преобразовать их в десятичное время, вам нужно выполнить следующие действия:
1. Выберите пустую ячейку, например Ячейку B1, введите эту формулу. = A1 * 24 в ячейке (ячейка A1 указывает ячейку, данные которой вы хотите преобразовать в десятичное время, вы можете изменить ее по своему усмотрению), затем щелкните Перейти кнопку на клавиатуре. Смотрите скриншот:
2. Затем перетащите маркер заполнения, чтобы заполнить диапазон, с которым вы хотите работать. Смотрите скриншот:
3. Затем вам нужно отформатировать ячейки результатов в столбце B как общее форматирование, выбрав диапазон, щелкнув правой кнопкой мыши и нажав Формат ячеек в контекстном меню. Смотрите скриншот:
4. Затем нажмите Число Вкладка в Формат ячеек диалоговое окно и щелкните Общие из Категорияy: поле, затем щелкните OK чтобы закрыть диалог. Смотрите скриншот:
Вы можете увидеть результат, как показано ниже:
Преобразуйте часы, минуты, секунды в десятичные часы одним щелчком мыши
Если Kutools for Excel установлен в Excel, вы можете использовать утилиту Convert Time для быстрого преобразования чч:мм:сс в часы/минуты/десятичные секунды.
После бесплатной установки программы Kutools for Excel выполните следующие действия
Выберите ячейки времени и нажмите Кутулс > Content > Конвертировать время, и выбрать вариант преобразования в соответствии с вашими потребностями. Смотрите скриншот:
Затем появится диалоговое окно, напоминающее количество преобразованных ячеек, нажмите ОК, чтобы закрыть его, и все выбранное время будет преобразовано.
Совет: Если вы хотите сохранить преобразованное десятичное число в другом месте, вы можете нажать Cthulles > Contents > Convert Time, чтобы вызвать диалог Convert Time, в диалоге выберите тип преобразования, который вам нужен, установите флажок Save to another location и выберите ячейку, в которой вы хотите получить результат, нажмите Ok, и время будет преобразовано и помещено в новое место. См. скриншот:
Преобразование времени в десятичные часы / минуты / секунды
Преобразуйте десятичную дробь в часы, минуты и секунды с помощью формулы
И наоборот, если вы хотите преобразовать десятичное время в часы, минуты и секунды, вы можете выполнить следующие действия
Предположим, что данные находятся в столбце A, и вам нужно преобразовать их в формат часов, минут, секунд.
1. Выберите пустую ячейку, в данном случае я выбираю ячейку B1 и ввожу эту формулу. = A1 / 24 в B1 (ячейка A1 указывает ячейку, данные которой вы хотите преобразовать в форматирование часов, минут и секунд, вы можете изменить его по своему усмотрению), затем щелкните Перейти кнопку на клавиатуре. Смотрите скриншот:
2. Повторите шаг 1, чтобы ввести указанную выше формулу, как вам нужно, в столбец B один за другим, например введите = A2 / 24 в ячейке B2 и щелкните Перейти. Смотрите скриншот:
3. Затем вам нужно отформатировать ячейки в столбце B как формат чч: мм: сс, выбрав диапазон, в котором используется формула, и щелкнув правой кнопкой мыши, щелкните Формат ячеек в контекстном меню. Смотрите скриншот:
4. Затем нажмите Число Вкладка в Формат ячеек диалоговое окно и щелкните Время из Категория: поле и выберите 13: 30: 55 из Тип: поле в правом разделе, затем щелкните OK чтобы закрыть диалог. Смотрите скриншот:
Вы можете видеть, что данные были преобразованы.
Преобразование десятичной дроби в часы и минуты с помощью VBA
С приведенной выше формулой = A1 / 24 преобразование времени по одному — это несколько пустая трата времени, но с помощью кода VBA можно сразу преобразовать список десятичных времен в часы и минуты.
1. Удерживая нажатой клавишу ALT, нажмите F11 на клавиатуре, чтобы открыть окно Microsoft Visual Basic for Applications.
2. Нажмите Вставить > Модули, и скопируйте VBA в модуль.
VBA: Преобразование десятичного времени в часы и минуты
3. Нажмите Run или нажмите F5 чтобы запустить VBA, затем KutoolsforExcel Появится диалоговое окно для выбора рабочего диапазона. Смотрите скриншот:
4. Нажмите OK во всплывающем диалоговом окне данные выбранного диапазона были преобразованы в форматирование часов и минут. Смотрите скриншот:
Вы потеряете исходные данные, если выполните приведенный выше код VBA, поэтому лучше скопируйте и зарезервируйте их перед выполнением кода VBA.
Проценты и экспоненциальное представление чисел
Проценты
Чтобы отобразить числа в процентах, добавьте знак процента (%) к цифровому формату.
Экспоненциальное представление чисел
Экспоненциальное представление позволяет компактно записывать большие числа или мелкие дроби. Для примера, 650000 записывается как 6,5 x 10^5, а 0,000065 — как 6,5 x 10^-5. В LibreOffice эти числа имеют вид 6,5E+5 и 6,5E-5. Чтобы отобразить числа в числовом формате, использующем экспоненциальное представление, необходимо ввести # или 0, за которыми следует один из приведенных ниже кодов: E-, E+, e- или e+.
Форматы чисел и дат в Google Таблицах
В этой статье мы рассмотрим типы форматирования чисел и текста в ячейках электронной таблицы Google.
О форматах следует помнить, что формат не равен содержимому ячейки. Или, говоря по-другому, внешнее отображение данных не обязательно отражает эти данные, «карта — это не территория».
В ячейке может быть 1, но она, скорее всего, будет округлена, и реальное значение будет 1,4982. Хотя в ячейке оно кажется отрицательным, на самом деле цифры положительные.
Вы можете ничего не видеть, но в камере что-то есть.
В строке формул всегда видно истинное значение:
Число в ячейке C4 имеет дробную (десятичную) часть, но она не видна в самой ячейке — поскольку формат ячейки округлен, отображается только целая часть.
Числовые форматы
Автоматический
Формат хорош тем, что отображаются числа с дробной частью, но не числа без дробной части, т.е. нет «пустых» нулей после запятой, например 1000,00.
Это формат, используемый по умолчанию.
Разделителей нет.
Число
Разделители отображаются в таком формате. И десятичные дроби. Их можно удалить (или добавить) с помощью этих кнопок панели инструментов:
Процентный
Подходит для отображения дробей и отклонений. 1 единица составляет 100%. В формулах не стоит умножать результат на 100%, лучше использовать процентный формат.
Вы можете (и должны) удалять десятичные знаки из вычислений. Точность до сотых долей процента требуется редко.
Экспоненциальный
Этот формат хорошо работает с очень большими и очень маленькими числами. Как его читать?
E+25 представляет собой 25 порядков, или силы десяти, или «нулей», хотя, строго говоря, это может быть любое число, но не нули.
Например, число 12700000000000000000000000000000000000 в экспоненциальном формате равно 1.27E+19.
Один миллион равен 1.00E+06 (1 и шесть нулей).
-1000000 (минус один миллион) равно -1,00E+06
А 0,0000001 — это 1,00E-07
Знак минус после буквы E означает десятичные знаки.
Финансы
Формат как у числового, но символ валюты добавляется с выравниванием влево. Вместо нуля — тире.
Финансы (второй формат)
Как правило, в бухгалтерском формате отрицательные числа отображаются в круглых скобках, но без знака минус. В остальном формат аналогичен тому, который используется для числовых данных.
Валюта
Знак валюты и ноль — это число или тире, а не прочерк, как в финансовом формате.
Валюта с округлением
Так же, как если бы вы вручную удалили десятичные знаки предыдущего формата с помощью панели инструментов
Пользовательские форматы
Формат -> Числа -> Другие форматы -> Другие форматы чисел (валют)
Если вы перейдете по этому адресу, вы сможете выбрать из широкого спектра дополнительных форматов или создать свой.
Что касается валюты, вы можете выбрать любую валюту:
Если вы имеете дело с числами, выберите различные форматы для положительных, отрицательных, нулевых чисел и текста. Для указания формата перечисленных данных необходимо использовать точку с запятой:
В то же время вы можете указать формат только для некоторых, например положительных:
Но если форматирование явно опущено, эти значения вообще не будут отображаться. В следующем случае отрицательные числа просто не будут отображаться:
Например, этот готовый к использованию формат определяет форматы для положительных и отрицательных чисел. Они разделяются точками с запятой. Во втором формате после нуля и текста не будет точки с запятой.
00 — обязательные символы, #,## — разделители чисел, [Красный] — цвет отображения данных. Например, если после точки с запятой указано [Red], то отрицательные числа будут красного цвета.
Форматы даты и времени
Давайте рассмотрим форматы даты и времени, применяемые к одному и тому же значению: функция TDATA, которая не имеет аргументов и отображает текущие время и дату.
Все ячейки имеют одну и ту же функцию — TDATA. Возвращаются и дата, и время.
Форматы даты и времени используются автоматически при вводе этой функции, но их можно также принудительно использовать.
Также можно отобразить только дату или только время. Но в действительности в клетке есть и то, и другое.
Обратите внимание на числовой формат. Дата и время — 28 февраля 2018 года, 18:40 (43,159,78). Дело в том, что для таблиц (таких, как Excel, кстати) дата — это целое число. Они могут вычислять путем сложения и вычитания. Ячейка будет содержать дату, соответствующую числу, если ее формат изменить на «Дата». Одна единица — это один день. 0 — это 30 декабря 1899 года, а 100, например, — это день на 100 дней позже 30 декабря 1899 года, то есть 09 апреля 1900 года.
Таблицы также поддерживают пользовательские форматы даты и времени. Чтобы создать их, перейдите в Формат -> Числа -> Другие форматы -> Другие форматы даты и времени
Здесь вы сможете выбрать один из списка готовых форматов:
Или создайте свой собственный. Элементы в календаре можно форматировать, нажимая на стрелки и выбирая представление (день, месяц, год, часы, минуты, секунды):
Чтобы добавить новый элемент, нажмите на маленькую кнопку в крайнем правом углу поля:
Между элементами можно добавлять слова, фразы и символы; все ограничивается только вашим воображением:
