Как перевести часы и минуты в десятичную дробь excel
Порой случаются ситуации, когда выгрузки трудозатрат из различных баз данных производятся в формате времени. Если в программе Excel просто изменением формата поставить «Числовой» вместо формата «время», получаются совсем некорректные цифры непригодные для проведения расчетов.
Рассмотрим, каким образом перевести трудозатраты из временного формата в десятичные дроби с целью проведения дальнейших вычислений.
№ п.п. | Месяц выполнения работ | Подразделение | Должность | Планируемое время на выполнение работы | Планируемое время на выполнение работы в часах |
1 | Сентябрь | Слесарный цех | Инженер по ПБ | 3:00:00 | 3,0 |
2 | Сентябрь | Слесарный цех | Инженер-энергетик | 21:20:00 | 21,33 |
Сделать это можно при помощи системы функций (формул).
Самый простой способ перевода используется, когда трудозатраты не могут быть более суток, то есть не превышают 24 часа.
Для перевода используется следующая функция:
ЕСЛИ(ОКРУГЛ(МИНУТЫ(Q58)/60*100;0)>9;СЦЕПИТЬ(ЧАС(Q58);»,»;ОКРУГЛ(МИНУТЫ(Q58)/60*100;0));СЦЕПИТЬ(ЧАС(Q58);»,0″;ОКРУГЛ(МИНУТЫ(Q58)/60*100;0))) , где –
Функция ЧАС(G4 ) выводит целое количество часов;
Функция МИНУТЫ(G4) выводит целое количество минут;
Функция ЕСЛИ сравнивает полученное значение сотых долей и, если количество сотых менее 9, добавляет «ноль» десятых долей.
ОКРУГЛ(МИНУТЫ(G4)/60*100;0) – преобразует минуты в часы десятичной дробью;
Эта система функций имеет небольшой недостаток, ЧАС(G4 ) выводит целое количество часов только до значения 24, не более. Если у вас стоит время (трудозатраты) 26:25 часов, то ЧАС(G4 ) будет равен 2.
Для тех случаев, когда трудозатраты более суток, то есть превышают 24 часа, можно использовать следующую систему:
=ЕСЛИ(ОКРУГЛ(МИНУТЫ(Q58)/60*100;0)>9;СЦЕПИТЬ((ЧАС(Q58)+ДЕНЬ(Q58)*24);»,»;ОКРУГЛ(МИНУТЫ(Q58)/60*100;0));СЦЕПИТЬ((ЧАС(Q58)+ДЕНЬ(Q58)*24);»,0″;ОКРУГЛ(МИНУТЫ(Q58)/60*100;0)))
, где добавлена функция ДЕНЬ(G4), выводящая количество целых дней.
Как мы знаем целый (полный ) день состоит из 24 часов, соответственно ДЕНЬ(G4)*24 будет равно количеству часов в полных днях. Если к этим часам добавить количество часов в неполных днях, то получим общее количество часов: (ЧАС(G4)+ДЕНЬ(G4)*24)
Если часов набегает больше чем на 31 день, можно по аналогии применить систему функций:
ЕСЛИ(ОКРУГЛ(МИНУТЫ(Q58)/60*100;0)>9;СЦЕПИТЬ((ЧАС(Q58)+ДЕНЬ(Q58)*24+(МЕСЯЦ(G4)-1)*31*24);»,»;ОКРУГЛ(МИНУТЫ(Q58)/60*100;0));СЦЕПИТЬ((ЧАС(Q58)+ДЕНЬ(Q58)*24+(МЕСЯЦ(G4)-1)*31*24);»,0″;ОКРУГЛ(МИНУТЫ(Q58)/60*100;0)))
Здесь представлена формула, которая преобразует время в десятичные часы в Excel и объясняет, как работает формула.
Если вы хотите следовать этому руководству, загрузите пример электронной таблицы.
Общая формула:
Time*24 |
аргументы
Time: the excel time that is used to convert to decimal hours.. |
Возвращаемое значение
Формула возвращает значение в формате времени, пожалуйста, отформатируйте результат как общий или числовой, как вам нужно, чтобы получить десятичные часы.
Как работает эта формула
Чтобы преобразовать время в ячейке B3 в десятичные часы в ячейке C3, используйте следующую формулу:
Нажмите Enter ключ для получения значения в формате времени.
Нажмите Главная вкладку и перейдите в Формат номера раскрывающийся список для выбора Общие or Число для отображения результата в виде числа.
объяснение
Есть 24 часа в сутки, другими словами, один час равен 1/24 дня. Чтобы преобразовать время в десятичные часы, необходимо умножить значение 24. Например, преобразовав время 10:00 в десятичные часы:
= 10: 00 * 24
= (10/24) * 24
= 10
Относительные формулы
В этом руководстве представлена формула для преобразования десятичных минут во время Excel.
Чтобы преобразовать десятичные секунды во время Excel в Excel, вам может помочь формула.
В некоторых случаях вы хотите преобразовать дату только в месяц, год, день или месяц год, день месяц или день года в текстовом формате.
В этом руководстве представлена функция ТЕКСТ для преобразования даты в текст.
Относительные функции
Тест на указанные условия, затем возвращает соответствующие значения
Преобразуйте текст в число.
МЕСЯЦ используется для получения месяца в виде целого числа (от 1 до 12) от даты.
Функция ДЕНЬ получает день в виде числа (от 1 до 31) от даты
Функция ГОД возвращает год на основе заданной даты в формате 4-значного серийного номера.
Лучшие инструменты для работы в офисе
Kutools for Excel - поможет вам выделиться из толпы
Хотите быстро и безупречно выполнять свою повседневную работу? Kutools for Excel предлагает мощные расширенные функции 300 (объединение книг, сумма по цвету, разделение содержимого ячеек, дата преобразования и т. Д.) И экономия 80% времени для вас.
Как преобразовать время в десятичные часы / минуты / секунды в Excel?
Предположим, у вас есть диапазон времени или текстов с [ЧЧ: ММ: СС] формат в Excel, и теперь вы хотите преобразовать их в десятичные часы, минуты или секунды, как вы можете быстро решить эту проблему? В этом руководстве представлены некоторые приемы для решения этой задачи.
Преобразуйте время в десятичные часы / минуты / секунды с помощью Convert Time
Quickly convert time to decimal value in Excel
Преобразуйте время в десятичные часы / минуты / секунды с помощью формул
В Excel вы можете использовать некоторые формулы для преобразования [чч: мм: сс] в десятичные часы, минуты или секунды.
1. Выберите ячейку и введите формулу. = A1 * 24 в него и нажмите Enter, чтобы получить результат, при необходимости перетащите дескриптор заполнения за пределы диапазона, чтобы применить формулу.
2. Щелкните правой кнопкой мыши выбранный диапазон и щелкните Формат ячеек из контекстного меню. И в появившемся диалоговом окне выберите Numbeг из Категория list и укажите нужные десятичные разряды. Смотрите скриншоты:
3. Нажмите OK. Теперь каждый раз был сразу переведен в десятичный час.
Наконечник:
1. Вы также можете использовать эту формулу для преобразования времени в часы. =HOUR(A1)+MINUTE(A1)/60+SECOND(A1)/3600 .
2. Если вы хотите преобразовать время в десятичные минуты, используйте это = A1 * 24 * 60 or = ЧАС (A1) * 60 + МИНУТА (A1) + СЕКУНДА (A1) / 60 .
3. Если вы хотите преобразовать время в десятичные секунды, используйте это = A1 * 24 * 60 * 60 or = ЧАС (A1) * 3600 + МИНУТА (A1) * 60 + СЕКУНДА (A1) .
Преобразование времени в десятичные часы / минуты / секунды с помощью Time Convert
Если вы не любите запоминать формулы, вы можете использовать Конвертировать время полезности Kutools for Excel конвертировать время в десятичные часы, минуты или секунды одним щелчком мыши.
После установки Kutools for Excel, сделайте следующее: (Бесплатная загрузка Kutools for Excel прямо сейчас!)
Выберите временной диапазон, который вы хотите преобразовать в десятичные числа, нажмите Кутулс > Content > Конвертировать время, и выберите нужный вариант в контекстном меню. Смотрите скриншоты:
После выбора параметра преобразования появилось диалоговое окно, напоминающее вам, сколько ячеек было преобразовано. Нажмите OK закрыть его.
Теперь вы можете видеть каждый раз, когда конвертируется.
Внимание:
Если вы хотите преобразовать время в десятичные числа и сохранить результат в другом месте, вы можете сделать следующее:
1. Выберите диапазон данных и нажмите Кутулс > Content > Конвертировать время > Конвертировать время. Смотрите скриншот:
2. В появившемся диалоговом окне отметьте нужный вариант преобразования в Тип преобразования раздел и проверка Сохранить в другое место (диапазон преобразования - одна область) Установите флажок, затем щелкните, чтобы выбрать диапазон, в котором будет отображаться результат, и щелкните OK > Ok > OK закрыть диалоги. Смотрите скриншоты:
Теперь данные преобразованы и сохранены в другом месте.
Преобразование времени в десятичные часы / минуты / секунды с помощью суперфункций
Собственно, в Kutools for Excel вы также можете использовать Kutools Функции для преобразования времени в десятичные числа.
После установки Kutools for Excel, сделайте следующее: (Бесплатная загрузка Kutools for Excel прямо сейчас!)
1. Выберите ячейку, в которую хотите поместить преобразованный результат, и щелкните Кутулс > Kutools Функции > Дата и время, и в контекстном меню выберите нужный вариант преобразования. Смотрите скриншот:
2. в Аргументы функций диалоговом окне выберите данные, которые вы хотите преобразовать в Данные о времени текстовое окно. Щелчок OK чтобы закрыть диалог. Смотрите скриншот:
3. Затем выберите преобразованный результат и перетащите маркер заполнения в диапазон, необходимый для применения функции. Теперь диапазон данных преобразован.
В группе Kutools Functions вы можете суммировать или подсчитывать данные по цвету заливки / шрифта, подсчитывать символы, извлекать текст и перевернуть текстовую строку и так далее.
Если у вас есть список времени, отображаемый в формате десятичных часов, но вы привыкли к чч: мм: сс (форматирование часов, минут и секунд), как вы можете это сделать? С помощью следующих методов вы можете решить проблему разговоров между часами, минутами, секундами и десятичными часами.
Преобразуйте часы, минуты и секунды в десятичные числа одним щелчком мыши
Преобразуйте часы, минуты и секунды в десятичные числа с помощью формулы
Предположим, у вас есть список времени в формате часов, минут и секунд в столбце A, и чтобы преобразовать их в десятичное время, вам просто нужно выполнить следующие шаги:
1. Выберите пустую ячейку, например Ячейку B1, введите эту формулу. = A1 * 24 в ячейке (ячейка A1 указывает ячейку, данные которой вы хотите преобразовать в десятичное время, вы можете изменить ее по своему усмотрению), затем щелкните Enter кнопку на клавиатуре. Смотрите скриншот:
2. Затем перетащите маркер заполнения, чтобы заполнить диапазон, с которым вы хотите работать. Смотрите скриншот:
3. Затем вам нужно отформатировать ячейки результатов в столбце B как общее форматирование, выбрав диапазон, щелкнув правой кнопкой мыши и нажав Формат ячеек в контекстном меню. Смотрите скриншот:
4. Затем нажмите Число Вкладка в Формат ячеек диалоговое окно и щелкните Общие из Категорияy: поле, затем щелкните OK чтобы закрыть диалог. Смотрите скриншот:
Вы можете увидеть результат, как показано ниже:
Преобразуйте часы, минуты, секунды в десятичные часы одним щелчком мыши
Если у вас есть Kutools for Excel установлен в Excel, вы можете использовать Конвертировать время утилита для быстрого преобразования чч: мм: сс в дециамные часы / минуты / секунды.
После бесплатная установка Kutools for Excel, сделайте следующее:
Выберите ячейки времени и нажмите Кутулс > Content > Конвертировать время, и выбрать вариант преобразования в соответствии с вашими потребностями. Смотрите скриншот:
Затем появится диалоговое окно, напоминающее количество преобразованных ячеек, нажмите ОК, чтобы закрыть его, и все выбранное время будет преобразовано.
Наконечник: Если вы хотите сохранить преобразованное десятичное число в другом месте, вы можете нажать Кутулс > Content > Конвертировать время для отображения Конвертировать время диалоговое окно, а затем в диалоговом окне проверьте тип преобразования вам нужно и проверьте Сохранить в другое место флажок и выберите ячейку для вывода результата, щелкните Ok, и время было преобразовано и размещено в новом месте. Смотрите скриншот:
Преобразование времени в десятичные часы / минуты / секунды
Преобразуйте десятичную дробь в часы, минуты и секунды с помощью формулы
И наоборот, если вы хотите преобразовать десятичное время в форматирование часов, минут и секунд, вы можете сделать следующее:
Предположим, что ваши данные находятся в столбце A, и вам нужно преобразовать их в форматирование часов, минут, секунд.
1. Выберите пустую ячейку, в данном случае я выбираю ячейку B1 и ввожу эту формулу. = A1 / 24 в B1 (ячейка A1 указывает ячейку, данные которой вы хотите преобразовать в форматирование часов, минут и секунд, вы можете изменить его по своему усмотрению), затем щелкните Enter кнопку на клавиатуре. Смотрите скриншот:
2. Повторите шаг 1, чтобы ввести указанную выше формулу, как вам нужно, в столбец B один за другим, например введите = A2 / 24 в ячейке B2 и щелкните Enter. Смотрите скриншот:
3. Затем вам нужно отформатировать ячейки в столбце B как формат чч: мм: сс, выбрав диапазон, в котором используется формула, и щелкнув правой кнопкой мыши, щелкните Формат ячеек в контекстном меню. Смотрите скриншот:
4. Затем нажмите Число Вкладка в Формат ячеек диалоговое окно и щелкните Время из Категория: поле и выберите 13: 30: 55 из Тип: поле в правом разделе, затем щелкните OK чтобы закрыть диалог. Смотрите скриншот:
Вы можете видеть, что данные были преобразованы.
Преобразование десятичной дроби в часы и минуты с помощью VBA
С приведенной выше формулой = A1 / 24 преобразование времени по одному - это несколько пустая трата времени, но с кодом VBA вы можете преобразовать список десятичного времени в часы и минуты сразу.
1. Держать ALT и нажмите F11 на клавиатуре, чтобы открыть Microsoft Visual Basic для приложений окно.
2. Нажмите Вставить > Модули, и скопируйте VBA в модуль.
VBA: преобразование десятичного времени в часы и минуты
3. Нажмите Run или нажмите F5 чтобы запустить VBA, затем KutoolsforExcel Появится диалоговое окно для выбора рабочего диапазона. Смотрите скриншот:
4. Нажмите OK во всплывающем диалоговом окне данные выбранного диапазона были преобразованы в форматирование часов и минут. Смотрите скриншот:
Функции: При запущенном выше коде VBA вы потеряете свои исходные данные, поэтому вам лучше скопировать и зарезервировать их до запуска VBA.
Время в ячейках программы «Excel» записывается в виде десятичной дроби являющейся долей времени от суток. То есть, если перевести формат ячейки, в котором записаны часы и минуты в формат чисел, мы увидим длинную дробь, обозначающую, какую часть от суток занимает указанный промежуток времени. Сутки приравниваются к 100 % или единице.
Например:
- 1 час равен 1/24 выраженной в десятичной дроби ( 0,042); (сорок две тысячных);
- 1 минута равна 1/1440 в десятичной дроби это 0,0007 (семь десятитысячных) ;
- 1 секунда равна 1/86400 в десятичной дроби это 0,000012 (двенадцать миллионных);
Такие числа неудобны для расчетов времени, например в случаях, когда необходимо посчитать и наглядно отобразить трудозатраты в минутах или часах, поэтому в «Эксель» существуют функции, преобразующие десятичные дроби формата времени в обычные минуты, часы и секунды.
Рассмотрим эти функции.
Первая функция для работы со временем – функция «СЕКУНДЫ»
Эта функция выбирает данные равные количеству секунд из ячейки в формате «Время» и преобразует их в секунды ( от 1 до 59).
Пример использования:
- Установить курсор в ячейку;
- Выбрать в мастере функций функцию «СЕКУНДЫ»;
- В появившемся окне выбрать ячейку, которую преобразуем в секунды;
- Нажать «ОК».
Вторая функция для работы со временем – функция «МИНУТЫ»
Эта функция выбирает данные равные количеству минут из ячейки в формате «Время» и преобразует их в минуты( от 1 до 59).
Пример использования:
- Установить курсор в ячейку;
- Выбрать в мастере функций функцию «МИНУТЫ»;
- В появившемся окне выбрать ячейку, которую преобразуем в минуты;
- Нажать «ОК».
Третья функция для работы со временем – функция «ЧАС»
Эта функция выбирает данные равные количеству часов из ячейки в формате «Время» и преобразует их в часы ( от 1 до 59).
Пример использования:
- Установить курсор в ячейку;
- Выбрать в мастере функций функцию «ЧАС»;
- В появившемся окне выбрать ячейку, которую преобразуем в часы;
- Нажать «ОК».
Функция «ВРЕМЯ» преобразует данные секунды, минуты и часы в формат времени и присваивает это значение ячейке.
- Установить курсор в ячейку;
- Выбрать в мастере функций функцию «ВРЕМЯ»;
- В появившемся окне выбрать ячейки, из которых будут взяты часы, минуты и секунды.
- Нажать «ОК».
Читайте также: