Работа с функциями времени в программе «Эксель»

Как записывается время в программе «Excel».

Время в ячейках программы «Excel» записывается в виде десятичной дроби являющейся долей времени от суток. То есть, если перевести формат ячейки, в котором записаны часы и минуты в формат чисел, мы увидим длинную дробь, обозначающую, какую часть от суток занимает указанный промежуток времени. Сутки приравниваются к 100 % или единице.

Например:

• 1 час равен 1/24 выраженной в десятичной дроби ( 0,042); (сорок две тысячных);
• 1 минута равна 1/1440 в десятичной дроби это 0,0007 (семь десятитысячных) ;
• 1 секунда равна 1/86400 в десятичной дроби это 0,000012 (двенадцать миллионных);

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

Рассмотрим эти функции.

Введите текущее время с помощью функции NOW

Функция NOW является одной из наиболее часто используемых в Google Sheets. Его основная цель — помочь вам вставить текущую дату и время в рабочий лист в мгновение ока. Кроме того, эта функция широко используется во множестве различных формул, которые позволяют добавлять соответствующие даты и время.

Хотя некоторые функции имеют сложный синтаксис, синтаксис этой функции удивительно прост: «= NOW ()». Чтобы ввести текущее время в ячейку, все, что вам нужно сделать, это:

1. Нажмите на ячейку, в которой вы хотите отобразить дату и время.
2. Введите или вставьте «= Сейчас ()».
3. Нажмите Ввод».

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

Кроме того, вы можете использовать сочетание клавиш: Ctrl + Shift + # или [email protected] Разница между этими двумя сочетаниями клавиш заключается в формате: первый показывает время в 24-часовом формате, а второй отображает текущее время в 12-часовом формате.

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

Автоматически вставлять метку времени в Google Таблицы с помощью скрипта

Хотя оба вышеуказанных метода (сочетания клавиш и формулы) работают хорошо, они не будут автоматически вставлять метку времени за вас.

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

Это можно сделать с помощью простого скрипта в Google Таблицах.

Ниже приведены шаги по использованию сценария для автоматического добавления отметок времени в Google Таблицах:

• Откройте документ Google Таблиц
• В меню выберите «Инструменты».
• Щелкните параметр «Редактор сценариев». Это откроет редактор сценариев в Google Таблицах.
• В окне кода редактора сценариев скопируйте и вставьте следующий код (этот сценарий принадлежит Stackoverflow):
• function onEdit () <

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

Приведенный выше код работает только с Sheet1 (как мы указали в третьей строке. Вы можете изменить имя «Sheet1» на любой лист, с которым вы хотите, чтобы этот код работал.

Кроме того, код был создан с учетом того, что вы вводите данные в столбец A и нуждаетесь в отметке времени в столбце B. Вы можете изменить их по мере необходимости.

Вставьте дату или время в любую программу, используя горячую клавишу клавиатуры

AutoHotkey — это бесплатный язык сценариев с открытым исходным кодом для Windows, который позволяет пользователям легко создавать небольшие и сложные сценарии для всех видов задач, таких как заполнители форм, автоматический щелчок, макросы и т. Д.

1. Скачайте AutoHotkey и установите его.
2. Щелкните правой кнопкой мыши на рабочем столе, нажмите «Создать» и выберите Скрипт AutoHotkey.
3. Переименовать файл скрипта New AutoHotkey Script.ahk в insert_date.ahk
4. Щелкните правой кнопкой мыши файл и выберите Редактировать скрипт
5. Удалите все строки в скрипте и замените его следующим кодом:

Настройка скрипта

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

• !
• +
• ^
• # <> Winkey

Например, для Ctrl + Alt + Shift + D вы бы использовали ^!+d .

Полный список ключей, которые вы можете отправить или перехватить, см. В документации AutoHotkey SendInput.

Без использования горячих клавиш

Если вы хотите вставить метку времени, набрав определенное слово — например, td , затем отредактируйте скрипт .ahk и замените его содержимое следующим:

Теперь введите td (и сопровождается пробелом) в любой программе. Слова td будет заменен текущей датой / отметкой времени. Смотрите эту анимацию:

Точно так же вы можете настроить формат даты или метки времени.

Для получения дополнительной информации см. Документацию AutoHotkey синтаксиса FormatTime.

Вышеупомянутый скрипт AutoHotkey использует просто 1.5 MB памяти.

И вы даже можете скомпилировать скрипт .ahk в файл .exe, чтобы вам не нужно было устанавливать программу AutoHotkey. Это особенно полезно, если вы управляете большим количеством компьютеров в домашней или рабочей сети.

Сериализация информации о дате и времени в Excel

В Excel используются две системы дат: одна начинается с 1 января 1900 года, а другая – с 1 января 1904 года. Последние версии Excel, как для Windows, так и для Mac, по умолчанию используют систему дат 1900. Мы объясним все функции с системой дат 1900 в этой статье. Процесс вычислений остается таким же, даже если вы используете систему дат 1904, в то время как внутренняя сериализация дат в Excel может измениться.

В системе дат 1900 года Excel начинает дату с 01.01.1900 (01 января 1900 года) и назначает сериализацию как 1. Таким образом, дата 01.10.1900 (10 января 1900 года) будет преобразована в число 10 в Excel и 31.03.2018 (31 Март 2018 г.) будет сериализован как 43190. Точно так же информация о времени сохраняется внутри как десятичная. Например, 0,5 означает полдень, 0,25 – 6 утра и 0,75 – 6 вечера в Excel.

Теперь давайте посчитаем 31 марта 2018 г., 9:45. Откройте лист Excel и введите 31 марта 2018 г. в одну ячейку. Щелкните правой кнопкой мыши и выберите «Форматировать ячейки…». На вкладке «Число» выберите формат даты (без времени) для этой ячейки. Введите 31 марта 2018 г. 09:45:00 AM в другую ячейку и отформатируйте эту ячейку как дату. времени. Теперь скопируйте две ячейки и вставьте в другую строку. Выделите обе ячейки, щелкните правой кнопкой мыши и отформатируйте их как простые числа. Вы можете выбрать без десятичной дроби для первой ячейки и 5 десятичных знаков для второй ячейки. Это должно выглядеть, как показано ниже, с указанием 31 -Mar-18 9:45 = 43190,40625 в Excel.

Преобразование даты в числа

Вы можете просто ввести 43190,40625 в ячейку и изменить формат даты со временем. Excel автоматически преобразует число в формат даты со временем 31 марта 18 9:45. Помните, что число без десятичных знаков считается 00:00:00 в формате времени. Только десятичное число считается временем в Excel.

Формат даты, числа и времени в Excel

Расчет даты и времени в Excel

Давайте добавим несколько дней к существующей дате. Например, добавив 10 дней к 01.01.2018, вы получите 1/11/2018. Введите 01.01.2018 в ячейку B3, введите = 10 + B3 в ячейку B4 и нажмите клавишу ВВОД. Вы увидите результат как 1/11/2018.

Добавление дней к дате

Простое добавление двух дат может привести к абсолютному сложению в Excel, что не имеет смысла. Например, если вы добавите 31/31/18 и 3/1/18, то получится 4/2/36. Но вы можете вычесть две даты и показать разницу дней в числовом формате. Например, 3/1/18 – 1/31/18 покажет результат как 29 дней.

Вычитание двух дат

Как и в случае с датами, вы можете складывать или вычитать две ячейки в формате времени. Убедитесь, что вы всегда вычитаете меньшее значение из более высокого и формат ячеек соответствует времени. Например, с 18:00 до 15:00 должно получиться 3:00.

Сложение и вычитание времени

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

«12/24/2018 ″ -« 12/05/2018 »(в результате вы получите 19)
«20:00 ″ – «C9» (в результате вы увидите 18:45, поскольку ячейка C9 имеет значение 1:15 )

Использование СЕЙЧАС() и СЕГОДНЯ () для расчетов

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

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

NOW () вернет текущее время

СЕЙЧАС () – «02:00» вычитает два часа из текущего времени.

СЕЙЧАС () + "03:00 ″ добавит три часа к текущему времени.

Перед использованием функции СЕГОДНЯ () убедитесь, что ячейка отформатирована как формат даты:

СЕГОДНЯ () вернет сегодняшнюю дату

СЕГОДНЯ () – 3 вычтут три дня из сегодняшнего дня

СЕГОДНЯ () + 5 добавит пять дней с сегодняшнего дня

Основные функции даты

В Microsoft Excel есть следующие функции даты – СЕГОДНЯ (), ГОД (), МЕСЯЦ (), ДЕНЬ () и ДЕНЬ НЕДЕЛИ (). Пример использования показан ниже. Введите функцию = СЕГОДНЯ () и нажмите клавишу ввода в ячейке C4. Вы можете использовать все остальные функции со ссылкой на ячейку C4.

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

Функция WEEKDAY () имеет дополнительный тип возвращаемого параметра. Это требуется для определения первого рабочего дня недели. Если вы введете = WEEKDAY (C4,2) в приведенном выше примере, вы получите 1 в качестве ответа. Это потому, что 2 означает, что неделя начинается с понедельника по воскресенье. Следовательно, понедельник в контрольной ячейке возвращается как 1, что означает, что это первый рабочий день.

Функция буднего дня Excel

Также существует функция DAYS (), которая позволяет вычислить количество дней между любыми двумя заданными датами. Синтаксис: = ДНЕЙ (конечная_дата, начальная_дата).

Основные функции времени

Подобно функциям даты, Excel также поддерживает различные функции времени – СЕЙЧАС (), ЧАС (), МИНУТА () и СЕКУНДА ().

Функции времени в Excel

Функции ДАТА и ВРЕМЯ

Функция ДАТА просто используется для сбора года, месяца и даты из разных ссылочных ячеек и отображения их в формате даты. Точно так же ВРЕМЯ – это функция для объединения часов, минут и секунд из разностных эталонных ячеек.

Пример функции даты и времени

Использование функции DATEDIF для вычитания даты в Excel

Помимо всех этих формальных функций даты и времени, Excel также поддерживает неформальную функцию DATEDIF. Согласно документу поддержки Microsoft, эта функция по-прежнему работает для поддержки старых книг, перенесенных в Excel из Lotus 1-2-3. Но он может давать неверные результаты в разных ситуациях, поэтому использовать его не рекомендуется.

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

РАЗНДАТ (дата начала, дата окончания, единица)

Стоимость единицы может быть одной из следующих:

• Y – количество полных лет между начальной и конечной датами.
• M – количество полных месяцев
• D – количество дней
• MD – количество дней между начальной и конечной датами без учета лет и месяцев. Это значение обычно дает неверные результаты.
• YM – количество месяцев между датами начала и окончания, без учета дней и лет.
• YD – количество дней между двумя заданными датами без учета лет.

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

Использование функции РАЗНДАТ в Excel

Обратите внимание, что DATEDIF является вспомогательной функцией для целей миграции, поэтому при вводе функции в ячейку вы не увидите текст справки по формуле.

Содержание

Даты вычисляются на основании смещения относительно нулевого дня. Для нулевого дня можно использовать следующие варианты:

Выберите команду Сервис — Параметры — LibreOffice Calc — Вычислить, чтобы выбрать формат даты.

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

Функция СЕКУНДЫ возвращает секунды заданного времени, определяется как целое число в интервале от 0 до 59.
Синтаксис
СЕКУНДЫ(время в числовом формате)
Время в числовом формате – время из которого нужно вернуть секунды. Значение аргумента такое же, как для функции ЧАС, например, =СЕКУНДЫ(“15:25:30”) вернет результат 30 и т.д.

Для примера рассчитаем время расписания занятий.
Сначала с помощью функции ВРЕМЯ напишем время начала 1 урока.

Время окончания: В мастере функций выберем функцию ВРЕМЯ. В аргументах функции пропишем, чтобы часы, минуты и секунды брались из ячейки D2, но в минутах прибавим длительность урока (ячейку B2).

Аналогично пропишем начало 2 урока: в аргументах функции ВРЕМЯ пропишем функции ЧАС, МИНУТЫ, СЕКУНДЫ со ссылками на время окончания 1 урока. В минутах прибавим время перерыва (С2).

Далее формулы можно просто продлить.

Далее можно легко пересчитать время занятий, например для короткого дня сделаем длительность урока 35 мин, а перерыв 5 мин. Время начала и время окончания Excel пересчитает автоматически.