Перевод часов в минуты в microsoft excel

Конвертация часов в минуты в Excel

Вся сложность преобразования часов в минуты в том, что Excel рассматривает время не так, как мы привыкли, а в днях. То есть для этой программы 24 часа равны одному. Час 12:00 представлен в расписании как 0,5, потому что 12 часов — 0,5 дня.

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

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

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

Способ 1: применение формулы умножения

Самый простой способ перевести часы в минуты — это умножить их на определенный коэффициент. Выше мы обнаружили, что Excel воспринимает время в днях. Следовательно, чтобы получить минуты из выражения в часах, вам нужно умножить это выражение на 60 (количество минут в часах) и на 24 (количество часов в сутках). Следовательно, коэффициент, на который нам нужно умножить значение, равен 60 × 24 = 1440. Посмотрим, как это будет выглядеть на практике.

1. Выделите ячейку, которая будет содержать окончательный результат в минутах. Ставим знак «=». Щелкните ячейку, содержащую данные в часах. Ставим знак «*» и набираем с клавиатуры число 1440. Чтобы программа обработала данные и отобразила результат, нажмите кнопку Enter.

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

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

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

Как видите, после этого действия значения всего ряда будут конвертированы в минуты.

Функция ДАТА

Синтаксис:

ДАТА(год,месяц,день)

Результат: Сериальное число (от 0 до 2958456), соответствующее дате в числовом формате (в качестве начала отсчета принимается 01 января 1900 года).

Аргументы:

• год — число (год) от 1900 до 9999 (или от 0 до 8099 соответственно);
• месяц — номер месяца в году (если значение аргумента больше 12, то это число делится на 12, частное прибавляется к указанному значению года, а остаток выступает в качестве номера месяца; так, функция ДАТА(98,14,2) возвращает числовой формат даты 2 февраля 1999 года);
• день — номер дня в месяце (если аргумент- день больше числа дней в указанном месяце, то алгоритм вычислений аналогичен описанному для аргумента месяц’, так, функция ДАТА(99,1,35) возвращает числовой формат даты 4 февраля 1999 года).

ПРИМЕЧАНИЕ

См. функцию ГОД.

Суммирование времени

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

1. Выделяем соответствующие ячейки. Если это диапазон, то просто зажимаем левую кнопку мыши и обводим его. В случае, если мы имеем дело с отдельными ячейками, разбросанными по листу, то тогда выделение их проводим, кроме всего прочего, зажав кнопку Ctrl на клавиатуре.
2. Кликаем правой кнопкой мыши, тем самым вызвав контекстное меню. Переходим по пункту «Формат ячеек…». Вместо этого можно также после выделения на клавиатуре набрать комбинацию Ctrl+1.

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

Способ 1: показание часов через промежуток времени

Прежде всего, давайте посмотрим, как рассчитать, сколько будут показывать часы по прошествии определенного периода времени, выраженного в часах, минутах и секундах. В нашем конкретном примере нужно узнать, сколько будет на часах через 1 час 45 минут и 51 секунду, если сейчас на них установлено время 13:26:06.

1. На отформатированном участке листа в разных ячейках с помощью клавиатуры вводим данные «13:26:06» и «1:45:51».

В третьей ячейке, в которой тоже установлен формат времени, ставим знак «=». Далее кликаем по ячейке со временем «13:26:06», жмем на знак «+» на клавиатуре и кликаем по ячейке со значением «1:45:51».

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

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

Способ 2: использование функции

Альтернативным вариантом предыдущему способу является использование функции СУММ.

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

Открывается Мастер функций. Ищем в перечне элементов функцию «СУММ». Выделяем её и жмем на кнопку «OK».

Запускается окно аргументов функции. Устанавливаем курсор в поле «Число1» и кликаем по ячейке, содержащей текущее время. Затем устанавливаем курсор в поле «Число2» и кликаем по ячейке, где указано время, которое нужно прибавить. После того, как оба поля заполнены, жмем на кнопку «OK».

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

Способ 3: суммарное сложение времени

Но чаще на практике нужно не определить показание часов через определенное время, а сложить общее количество времени. Например, это требуется для определения общего количества отработанных часов. Для этих целей можно воспользоваться одним из двух ранее описанных способов: простое сложение или применение функции СУММ. Но, намного удобнее в этом случае воспользоваться таким инструментом, как автосумма.

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

Далее нужно выделить диапазон, заполненный значением времени и одну пустую ячейку после него. Находясь на вкладке «Главная», нажимаем на значок «Сумма», расположенный на ленте в блоке инструментов «Редактирование». Как альтернативный вариант, можно набрать на клавиатуре сочетание клавиш «Alt+=».

После этих действий в пустой выделенной ячейке появится результат вычислений.

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

Как в Excel рассчитать количество отработанного времени в ночную смену?

Во многих компаниях есть службы, которые работают в круглосуточном режиме: производство, коммуникации, охрана. Как рассчитать, сколько времени отработал сотрудник, если он приступил к работе, например, в 21:00, а закончил в 7:00? Простой формулой «время окончания минус время начала» тут не обойтись: получим отрицательное число!

Время в Excel хранится в виде простого числа и отображается как время (в часах и минутах), если ячейке задан соответствующий формат. 24 часа (продолжительность суток) в Excel принято за единицу, и каждому значению времени соответствует определенная доля от единицы. Так, например, 12:00 — это 0,5 (середина суток), 18:00 — это 0,75 и т.д. Надеюсь, не слишком запутала вас:)

Для примера возьмем список сотрудников с указанием времени начала и времени окончания работы (как всегда, в конца поста ссылка для скачивания).

ШАГ 1. Для удобства сразу преобразуем диапазон в Таблицу и назовем ее «Время_работы» (подробнее о Таблицах здесь).

ШАГ 2. В свободном столбце запишем формулу: =ОСТАТ( Время_работы;] — Время_работы;] ;1)

Время_работы;] и Время_работы;] в моей формуле — это ссылки на соответствующие ячейки в столбцах «Окончание» и «Начало» таблицы «Время_работы». Если у вас отличается имя таблицы или названия столбцов, не забывайте учитывать это.

Если по какой-то причине вы не используете Таблицы (например, тем, кто работает в Excel 2003, этот инструмент не доступен), формула будет выглядеть так: =ОСТАТ( C2 — B2 ;1) . Не забудьте скопировать формулу во все ячейки столбца.

Английский вариант функции ОСТАТ — MOD.

Выделим столбец и назначим ячейкам формат Время. [ Подробнее о форматах ячеек. ]

Нажимаем на стрелочку рядом с надписью «Число» на вкладе Главная.

Открылось окно «Формат ячеек». Выбираем нужный нам Формат и Тип, подтверждаем кнопкой «ОК».

Пытливые умы уже мучаются вопросом, как работает функция ОСТАТ [ Специально для них. ]

Функция ОСТАТ (MOD) относится к категории «Математические» и, как сообщает мастер функций, вычисляет «остаток от деления».

Да, но при чем тут время?!

Как я уже писала выше, время в Excel — это циклическая последовательность чисел от 0 до 1, после полуночи отсчет каждый раз начинается с 0.

В рамках одного цикла (суток) количество времени вычисляется как простая разница между окончанием и началом: = окончание — начало. Если же произошла смена суток, ко времени окончания мы должны прибавить единицу: =(окончание + 1) — начало.

А что же такое остаток от деления?

Вспоминаем школьную программу. Если ленту 7 м делить на куски по 3 м, получится 2 куска и остаток 1 м.

Теперь математика. Результат деления 7 на 3 – 2,25. Чтобы вычислить остаток, берем ближайшее к 2,25 меньшее целое число, т.е. 2, и умножаем на 3, получится 6. Отнимаем 6 от 7 и получаем остаток – 1 м.

Чудеса начинаются при вычислениях с отрицательными числами.

Например, сотрудник приступил к работе в 18:00 (0,75) и закончил в 6:00 (0,25). Если отнять время начала 0,75 от времени окончания 0,25, получаем -0,5. Теперь вычисляем остаток от деления -0,5 на 1.

Ближайшим меньшим целым для числа -0,5 является -1, поэтому получаем

Как мы видим, при вычислениях со временем формула = ОСТАТ ( окончание — начало ; 1 ) прибавляет единицу, если разница между окончанием и началом является отрицательным числом. Вот и весь секрет!

Умножаем результат вычисления 0,5 на 24 (чтобы привести временное значение к числовому) и получаем 12 часов — столько времени прошло с 18:00 до 6:00.

ШАГ 3. Если вы планируете проводить дальнейшие вычисления, например, использовать количество отработанного времени при расчете вознаграждений, нужно преобразовать результат в число.

Для этого изменим формат ячеек на Числовой (о форматах ячеек выше) и умножим результат вычисления на 24 часа: =ОСТАТ( Время_работы;] — Время_работы;] ;1)*24

Итак, мы получили универсальную формулу для вычисления количества времени.Удачи всем!

UPDATE: Расширенная формула для расчета количества отработанного времени с учетом перерыва на обед. [ Прочитать. ]

=ОСТАТ (Время_работы;] — Время_работы;] -( Время_работы;] — Время_работы;] );1)*24

Время_работы;] и Время_работы;] — ссылки на соответствующие ячейки столбцов «Обед оконч» и «Обед над» таблицы «Время_работы». Если у вас отличаются заголовки столбцов или имя таблицы, не забудьте это учесть.

голоса

Рейтинг статьи

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

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

ДАТАМЕС()

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

КОНМЕСЯЦА()

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

РАБДЕНЬ()

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

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

ЧИСТРАБДНИ()

Это простая функция определяет количество рабочих дней между датой 1 и датой 2.

Подпишитесь к нам в дзен-канал, для получения свежих новостей it мира:

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

Как сложить время и число часов или минут в Excel

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

Как суммировать время и числа в Excel

Чтобы слоить время с часами и минутами в Excel лучше всего использовать функцию для работы с числами и временем в Excel. Ниже на рисунке ячейка B2 содержит следующую формулу:

В данном примере ко всем значениям времени в таблице было добавлено 5 часов и 30 минут.

Функция ВРЕМЯ позволяет сгенерировать временное значение на лету при заполнении 3-х аргументов:

1. Количество часов.
2. Количество минут.
3. Секунды.

Например, ниже приведенная формула возвращает значение 14:30:30:

Сложение времени и десятичных дробей

Внимание! Но что, если нам необходимо к исходному времени добавить количество минут, записанное числом в десятичной дроби. Например, нам нужно добавить 365,55 минут

Здесь надо быть очень внимательным. Функция ВРЕМЯ может сыграть с нами злую шутку. Если во второй аргумент данной функции поместить число с десятичной дробью, то функция преобразует его в целое отбросив все значения после запятой и результат вычисления будет ошибочным! Поэтому следует использовать другую формулу:

Как видно на рисунке мы сначала преобразуем число в десятичной дроби в временной формат значений разделив его на 1440 – ровно столько минут в 24-х часах (сутках). После чего выполняем математическую операцию сложения и получаем правильный результат.

Функция ДАТАЗНАЧ

Синтаксис:

ДАТАЗНАЧ(дата_как_текст)

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

Аргументы:

дата_как_текст — текст, содержащий дату в формате даты Excel. При использовании в Excel истемы дат, принятой по умолчанию для Windows 95/98, аргумент дата_как_текст должен представлять собой дату в диапазоне от 1 января 1900 года до 31 декабря 9999 года. При использовании в Excel системы дат, принятой по умолчанию для Macintosh, аргумент дата_как_текст должен представлять собой дату в диапазоне от 1 января 1904 года до 31 декабря 9999 года. Функция ДАТАЗНАЧ возвращает значение ошибки #ЗНАЧ!, если значение аргумента дата_как_текст выходит за пределы указанных диапазонов. Если в аргументе дата_как_текст опушен год, то функция ДАТАЗНАЧ использует значение текущего года из встроенных часов компьютера. Информация о времени суток в аргументе дата_ как_текст игнорируется.

ПРИМЕЧАНИЕ

См. функцию ГОД.

Каки перевести время в десятичное число без формул

Как мы уже не раз говорили, дата и время в Excel представляют собой обычные числа, где отсчет начинается с 1 января 1900 года. Быстрый способ показать число, представляющее определенное время, — использовать диалоговое окно «Формат ячеек».

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

Такой перевод времени в число вы видите на скриншоте ниже.

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

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

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

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

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

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

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

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

МЕСЯЦ()

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

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

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

ДЕНЬНЕД()

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

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

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

СЕГОДНЯ()

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

ТДАТА()

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

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

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

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

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

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

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

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

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

ВРЕМЯ()

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

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

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

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

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

Преобразование 1800 в 18:00

Если вы вводили ( с целью ускорения ввода ) значения времени без двоеточия (1800 вместо 18:00), то EXCEL будет обращаться с этими значениями как с числами (т.е. при прибавлении 65, вы получите 1865, а не 19:05). Преобразуем число 1800 (в ячейке А1 ) в значение в формате времени (в шесть часов вечера 18:00) 4-мя способами.

1. Использование функции ВРЕМЯ Напишем формулу: =ВРЕМЯ(ЛЕВСИМВ(A1;ДЛСТР(A1)-2);ПРАВСИМВ(A1;2);) Если в А1 введено 427, формула вернет 04:27. Результат — значение в формате времени.

2. Использование функции ВРЕМЗНАЧ Напишем формулу: =ВРЕМЗНАЧ(ЗАМЕНИТЬ(A1;ДЛСТР(A1)-1;0;»:»)) . В формуле использована функция ЗАМЕНИТЬ() , которая вставляет двоеточие в нужное место, и функция ВРЕМЗНАЧ() , которая преобразует текстовое значение в формат времени ( ЗАМЕНИТЬ() возвращает в текстовом формате). Формат ячейки необходимо поменять на формат Время .

3. Преобразование числа в текст, затем в формат времени Переведем наше число 1800 в текстовый формат с помощью функции ТЕКСТ() и одновременно отформатируем его по шаблону «00\:00″ (обратный слеш нужен для корректного интерпретирования двоеточия). Запишем формулу: =ТЕКСТ(A1;»00\:00») . Тем самым, из числа 1800 получим текст «18:00». Другой вариант =ТЕКСТ(A1;»ч:мм»)

Затем слегка преобразуем формулу: =—ТЕКСТ(A1;»00\:00″) . Применив к тексту арифметические операции (два минуса), мы запустим механизм автоформатирования: EXCEL попытается интерпретировать текст «18:00» как число, дату или время (мы как бы моделируем ситуацию, когда в пустую ячейку пользователь вручную вводит «18:00»). Единственное, что не сделает EXCEL в этом случае — это не изменит формат ячейки на Время . Сделаем это вручную.

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

4. Прямое преобразование в формат времени В формате Дата принято, что одним суткам соответствует число единица. Соответственно, конкретное время, это часть суток: двенадцать дня это 0,5; шесть вечера это 0,75. Число равное 1/24 (т.е. 0,041667) соответствует одному часу. Учитывая, что в сутках 24 часа, в часе 60 минут, а в сутках 1440 минут, напишем следующую формулу: =ЦЕЛОЕ(A1/100)/24+ОСТАТ(A1;100)/1440 . Далее изменяем формат ячейки на Время .

Получение номера месяца, года и т.д. из заданной даты.

Наверное, это простейший вариант использования функций.  Применяются функции ДЕНЬ, МЕСЯЦ, ГОД. Для работы указывается ячейка с исходной датой или дата прописывается в формуле с использованием кавычек.

=ДЕНЬ(«18.09.2018»)

Функция ДЕНЬ покажет число, т. е. порядковый день месяца, а функции МЕСЯЦ и ГОД извлекут из даты соответственно номер месяца и номер года.

Рисунок 1

Чтобы узнать номер дня недели, применяем функцию ДЕНЬНЕД. Для нее первой указывают обрабатываемую дату, а затем тип значения. Тип определяет порядок и нумерацию дней недели. Если его не указать, то считается, что неделя начинается с воскресенья, и счет дней недели идет с единицы. Если тип равен 3, то неделя начинается с понедельника, но номер дней идет с нуля. То есть в этом случае понедельник имеет номер 0. В наших условиях первый день – понедельник, счет дней начинается с единицы. Поэтому используем тип, равный 2.

Рисунок 2

Так же можно указать тип числами от 11 до 17. В этом случае счет дней будет идти с 1, а первый день будет от понедельника(11) до воскресенья(17). То есть функция =ДЕНЬНЕД(“23.08.2017”;11) и функция =ДЕНЬНЕД(“23.08.2017”;2) дадут одинаковый результат, равный 3. Желающие могут открыть календарь и убедиться, что это действительно был третий день недели по принятому в бывшем СССР варианту, то есть среда.

Для нахождения номера квартала отдельной функции, к сожалению, нет. Однако можно воспользоваться такой формулой:

=ЦЕЛОЕ((МЕСЯЦ(D10)+2)/3)

D10 – это ячейка с исходной датой. Естественно, никто не мешает указать ее явным образом.

Рисунок 3

В данном случае 16 июля 2020 года действительно относится к третьему кварталу.

Если нужно найти номер недели, используют функцию со схожим названием НОМНЕДЕЛИ. В ней надо указать дату, для которой надо найти номер недели, а затем тип отчета.

Для этой функции могут использоваться две разные системы.

• Система 1    Неделя, на которую приходится 1 января, считается первой неделей года, и для нее возвращается число 1.
• Система 2    Неделя, на которую приходится первый четверг года, считается первой неделей, и для нее возвращается число 1. Эта методика определена в стандарте ISO 8601, который широко используется в Европе для нумерации недель

Используют следующие типы:

 1 или опущен –  Воскресенье, 1 система отчета;

 2 –  Понедельник, 1 система отчета;

11 – Понедельник, 1 система отчета;

12 –  Вторник, 1 система отчета;

13 –  Среда, 1 система отчета;

14 –  Четверг, 1 система отчета;

15 – Пятница, 1 система отчета;

16 – Суббота, 1 система отчета;

17 –  Воскресенье, 1 система отчета;

21 – Понедельник, 2 система отчета.

Примеры использования:

=НОМНЕДЕЛИ(“21.05.2018”;11) – результат будет 21, неделя начинается с понедельника.

=НОМНЕДЕЛИ(“21.05.2018”;17) результат будет 21, неделя начинается с воскресенья.

При необходимости найти номер недели в соответствии с форматом ISO применяют функцию

=НОМНЕДЕЛИ.ISO, для которой достаточно указать только дату.

Суммирование времени

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

1. Выделяем соответствующие ячейки. Если это диапазон, то просто зажимаем левую кнопку мыши и обводим его. В случае, если мы имеем дело с отдельными ячейками, разбросанными по листу, то тогда выделение их проводим, кроме всего прочего, зажав кнопку Ctrl на клавиатуре.
2. Кликаем правой кнопкой мыши, тем самым вызвав контекстное меню. Переходим по пункту «Формат ячеек…». Вместо этого можно также после выделения на клавиатуре набрать комбинацию Ctrl+1.

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

Урок: Форматирование таблиц в Excel

Способ 1: показание часов через промежуток времени

Прежде всего, давайте посмотрим, как рассчитать, сколько будут показывать часы по прошествии определенного периода времени, выраженного в часах, минутах и секундах. В нашем конкретном примере нужно узнать, сколько будет на часах через 1 час 45 минут и 51 секунду, если сейчас на них установлено время 13:26:06.

1. На отформатированном участке листа в разных ячейках с помощью клавиатуры вводим данные «13:26:06» и «1:45:51».

В третьей ячейке, в которой тоже установлен формат времени, ставим знак «=». Далее кликаем по ячейке со временем «13:26:06», жмем на знак «+» на клавиатуре и кликаем по ячейке со значением «1:45:51».

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

Способ 2: использование функции

Альтернативным вариантом предыдущему способу является использование функции СУММ.

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

Открывается Мастер функций. Ищем в перечне элементов функцию «СУММ». Выделяем её и жмем на кнопку «OK».

Запускается окно аргументов функции. Устанавливаем курсор в поле «Число1» и кликаем по ячейке, содержащей текущее время. Затем устанавливаем курсор в поле «Число2» и кликаем по ячейке, где указано время, которое нужно прибавить. После того, как оба поля заполнены, жмем на кнопку «OK».

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

Урок: Мастер функций в Excel

Способ 3: суммарное сложение времени

Но чаще на практике нужно не определить показание часов через определенное время, а сложить общее количество времени. Например, это требуется для определения общего количества отработанных часов. Для этих целей можно воспользоваться одним из двух ранее описанных способов: простое сложение или применение функции СУММ. Но, намного удобнее в этом случае воспользоваться таким инструментом, как автосумма.

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

Далее нужно выделить диапазон, заполненный значением времени и одну пустую ячейку после него. Находясь на вкладке «Главная», нажимаем на значок «Сумма», расположенный на ленте в блоке инструментов «Редактирование». Как альтернативный вариант, можно набрать на клавиатуре сочетание клавиш «Alt+=».

После этих действий в пустой выделенной ячейке появится результат вычислений.

Урок: Как посчитать сумму в Экселе

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

Мы рады, что смогли помочь Вам в решении проблемы.

Опишите, что у вас не получилось.

Наши специалисты постараются ответить максимально быстро.

Как записать время в Excel

Иногда вам может понадобиться преобразовать время в формат, который выглядит примерно так: «# дней, # часов, # минут и # секунд».. Хорошо, что вы уже знаете все ингредиенты формулы:

• Извлечь дни с помощью функции INT;
• Извлечение единиц времени с помощью функций ЧАС, МИНУТА и СЕКУНД, а также
• Объединить все части в одну формулу.

Возникли трудности с определением правильной формулы для вашего рабочего листа? Следующий пример облегчит задачу!

Предположим, у вас есть даты предстоящих событий в столбце B, начиная с ячейки B4, и текущая дата и время, возвращаемые функцией NOW() в ячейке B1.

Формула для расчета разницы во времени так же проста, как =B4-$B$1. Конечно, ничто не мешает вам вычесть текущую дату и время напрямую с помощью =B4-NOW().

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

Формула для ввода в ячейку D4 выглядит следующим образом:

=ЦЕЛОЕ(C4) & «дни», & ЧАС(C4) & «часы», & МИНУТЫ(C4) & «минуты и» & СЕКУНДЫ(C4) & «секунды»

Если вы хотите избавиться от значений 0, как в ячейках D6 и D7 на снимке экрана выше, включите следующие операторы IF:

=ЕСЛИ(ЦЕЛОЕ(C4)>0, ЦЕЛОЕ(C4)&» дней, «,»») & ЕСЛИ(ЧАС(C4)>0, ЧАС(C4) & » часов, «,»») & ЕСЛИ(МИНУТЫ (C4)>0, МИНУТЫ(C4) & «минуты и «,»») & ЕСЛИ(СЕКУНДЫ(C4)>0, СЕКУНДЫ(C4) & «секунды»,»»)

Все нули исчезли!

Примечание. Когда любая из приведенных выше формул ссылается на отрицательное число, функция #ЧИСЛО! появится ошибка. Это может произойти, когда вы вычитаете большее время из меньшего.

Альтернативный способ записать время словами в Excel — применить к ячейке следующий пользовательский формат времени: д «день», ч «часы», м «минуты» и «с» секунды. Никаких формул и расчетов не требуется! Дополнительные сведения см. в разделе Создание пользовательского формата времени в Excel.

Как в экселе перевести минуты в часы?

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

Первый шаг. Перед нами таблица, в которой в столбце «А» указано время в минутах. Необходимо написать в столбце «В» время в часах.

Второй шаг. В программе эксель время запрограммировано в сутках. Поэтому нам необходимо привести значения в столбце «А» к суткам. Как известно сутки состоят из 24 часов, а один час из 60 минут. Чтобы перевести время в сутки, необходимо содержимое в ячейках «А» разделить на произведение 24*60. Для этого в ячейки «А2» пишем формулу =A2/(60*24), потом её копируем на оставшиеся ячейки.

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

Четвертый шаг. В открывшейся таблице смотрим на закладку «Число», в разделе «Числовые форматы», выбираем последнюю строку «все форматы». Выбираем формат «ч:мм». После чего нажимаем на кнопку «Ок».

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