Источником большей части путаницы с датами и временем в Excel является способ хранения и времени в Excel
Иногда Бывает, что пишешь в ячейку, например, 03.06.2014, А на доход получаешь что-то непонятное: 41793 откуда взялось это число?!
Дело в том, что Excel хранит числовые значения в виде чисел, которые выделяют количество дней, 1900 года прошедших с начала, а уже числовые форматируются для отображения в виде дат.
То есть 1 января 1900 года — это 1.
2 января 1900 года – это 2.
20 августа 2020 г., доброе утро, как 44063 и так далее.
Чтобы посмотреть, какое целое число присвоено дате Великобритании, достаточно перевести скорость хранения в цифровую эпоху.
Это кажется запутанным, но благодаря такому подходу значительно проще сложить, вычитать и сосчитать количество дней между датами (операция с расширенным охватом выполняется проще и быстрее, чем с шестью: день, месяц, год).
У самых внимательных уже должен был быть назреть вопрос: какова будет хронометрия до 1 января 1900 г.? Дело в том, что они не распознаются в Excel как дата. То есть нет «отрицательных» порядковых номеров. Поэтому, как ни меняйте формат клетки, как была дата — 19.02.1861, такой и жил.
Как в Excel хранится время
Excel сохраняет время, используя тот же формат упорядоченной нумерации, что и для дат. День у Excel «начинается», как и у нас с вами в полночь. Каждый час составляет 1/24 дня, поэтому он представлен в виде десятичного значения: 0,041666…
Если вы хотите узнать, что «видит» Excel, когда вы записываете в ячейку 9:00 утра, то умножьте: 0,6 0416: 0,6 0416
Одна минута в Excel представлена числом: 1 / (24 * 60) = 1/1440 = 0,000694.
Секунда составляет 1 /(24 * 60 * 60)= 1/86400 = 0,00001157407…
Поэтому, если вы запишете в ячейку в 8:30 утра, Excel совершит следующий расчет:
(8 * (1/24)) + (30 * (1/1440)) = 0,354167
Так что, если вы опаздываете на работу на час и хотите узнать об этом боссу, напишите ему / ей и скажите, что будете на месте через 0,02083333….
Если время указано без даты, например, 9:00, Excel сохранит его, как 9:00 0 января 1900 года. Это может затруднить выполнение математических расчетов для значений времени (без учета статистики), так как вычитание 6:00 3:00 из отрицательных и будет считаться ошибкой, так как от 0,125 до 0,25 = -0,125, что будет как #########.
Если же время указано вместе с датой, то проблем не возникнет. Так 11:00 20 августа 2020 года будет сохранено как 44063.46.
Функции, задающие дату и время
В Excel есть несколько функций, упрощающих объявление дат. Познакомиться с другими важными функциями, которые упростят вашу работу в Excel, можно в нашем бесплатном Гайде.
CЕГОДНЯ()
Функция СЕГОДНЯ всегда возвращает текущую версию в формате России:
ТДАТА()
АНАЛОГИЧЕСКАЯ ФУНКЦИЯ всегда возвращает некущую услугу и время:
ДАТАМЕС () и КОНМЕСЯЦА ()
Функция Datemes возвращает нормативный номер даты, отстоящей на заданное количество месяцев вперед или назад от заданной даты.
Функция конмесяца возвращает нормативный номер последнего месяца, отстоя на указанном количестве месяцев от процента, коэффициент в аргументе
Аргумент число_месяцев может быть целым числом, но будет только исчисленное значение в Республике (например, если Вы напишете 2.8, то программа округлит до 2).
РАБДЕНЬ()
Иногда может быть полезно расчитать отстоящую от величины на несколько рабочих дней вперед или назад. Нам часто говорят: «Посылка придёт через 10 рабочих дней». Вот и используйте сиди-считай, когда ее нужно получить… В такой удобной ситуации функция Excel РБЕ Функция возвращает порядковый номер даты. Поэтому, если вам нужна дата в формате: ДД.ММ.ГГГГ, то ячейку отформатировать.
В качестве аргументов функции выступают: начальная дата (от которой подразумевается); количество рабочих дней; праздники (если таковые имеются, то заканчиваются праздники).
Если необходимо найти, включают в себя, то количество дней, допускающих число, как на рисунке выше. Если необходимо найти относительную статистику, то – сильное отрицание.
Аргумент [праздники] не является обязательным. Но если таковые имеются, то нужно предоставить доступ к празднику. Если такой день один, то его можно указать в формуле в кавычках. Если же праздники выпадают на несколько дней, например, на 21.08.2020 и 25.08.2020, то эти целевые числа должны быть в отдельных ячейках и в качестве параметра диапазона этих ячеек.
РАБДЕНЬ.МЕЖД ()
Для более новых версий Excel (начиная с 2010 г.) существует еще одна версия форматной версииДМДЕНЖЕН Она также возвращает нормативный номер статистики, отстоящей вперед или назад на заданное количество. Но РАБДЕНЬ.МЕЖД дает возможность провести приятные дни, которые бывают выходными.
Функции синтаксиса:
= РАБДЕНЬ.МЕЖД (нач_дата, число_дней, [выходные], [праздники])
Детский сад, человек работает в понедельник, среду и пятницу, вторник и четверг у недыхо. При этом праздники те же, что и в наблюдениях.
Параметры: нач_дата, число_дней, и [праздники] те же, что и в понимании работы функции РАБДЕНЬ.
Основная «фишка» этой функции — моделирование любой графики.
В качестве параметра [выходные] солдаты допускают значения:
Если Вы не нашли подходящего положения в предложенной таблице, то можете установить любой график с использованием строки из семи цифр (0 или 1). 0 – рабочий день, 1- выходной. Историческое значение соответствует дню недели.
Так, если человек работает только в понедельник, среду и пятницу, то его рабочий режим можно описать следующей строкой: «0101011».
Функции извлечения дат
ДЕНЬ(), МЕСЯЦ() и ГОД()
Все сопоставимые функции в качестве результата возвращаются, как мы уже упомянули, порявько Не это число, не закон, не число, не число, не число, не число.
Для этого в Excel есть специальные функции для извлечения каждого из этих значений. Для работы с календарем есть ДЕНЬ, МЕСЯЦ и ГОД. Синтаксис прост:
Функции извлечения времени
ЧАС(), МИНУТА() и СЕКУНДА()
Данные функции аналогичны функции извлечения дат.
После первого:
Дополнительные функции извлечения даты
ДЕНЬНЕД() и НОМНЕДЕЛИ()
Довольно часто, бывает так: нам говорят: «Давайте встретимся 24.08.2020!». Многие люди не могут по дате сориентироваться: свободны они или нет. У нас так условно «заведено» — мысли дня недели. Для нас понедельник, например, (какая бы дата ни была) – день тяжелый, а вот пятница – «проказница».
Иногда полезно найти продажи от дней недели. Как это сделать?! Не станешь же все даты размечать.
В таком случае есть полезная функция: ДЕНЬНЕД(). Эта функция выдает в качестве результата число от 1 до 7, которое соответствует дню недели. Синтаксис очень прост:
= ДЕНЬНЕД (данные_в_числовом_формате, [подсказка])
Параметр [тип] показывает, с какого числа начинается неделя и может принимать значение, которое можно принять:
Замечание! Кто внимательно смотрел, те не заметили, что варианты 1 и 17; 2 и 11 – короне. Действительно, функциональные эти значения. Самые ранние значения для сравнения с более ранними версиями Excel.
По умолчанию параметр [тип] принимает значение 1 (то есть неделя начинается с воскресенья). Но в россии первым днем не принято считать понедельник, поэтому для расчетов мы будем использовать значение параметра [тип] = 2.
Функция Номнедели () Возвращает, как понятно из названия, номер недели, на требуемую конкретную дату.
Синтаксис расширенной с производной формулой.
Рассмотрим работу функции на подъеме:
Видно, что 20 августа 2020 года – это четвертый день недели (четверг) и выпадает на 34 недели с начала
Значение параметра [тип_возвр] принимает фиксированные значения из списка:
Подсчет и отслеживание дат
Как мы уже жили, Благодаря Тому, что Excel Хранит статистику в виде чисел, с датами можно делать операции сложения и вычитания. Операция сложения иллюстрировали функции РАБДЕНЬ и РАБДЕНЬ.МЕЖДУ. Рассмотрим функции, позволяющие вычитать одну из других.
ДНИ()
Самая простая функция. Доходность число, соответствующее количеству дней между датами. При этом результат может быть и отрицательным, если начальная дата больше конечной.
ЧИСТРАБДНИ() и ЧИСТРАБДНИ.МЕЖДУ()
ЧИСТРАБДНИ – функция подсчета количества рабочих дней при пятидневной рабочей нед Синтаксис формулы следующей:
= ЧИСТРАБДНИ (нач_дата, кон_дата, [праздники])
Аргументы нач_даты и кон_даты могут быть большими датами в кавычках или ссылками на ячейку
Параметр [Праздники], как и в функции является необязательным, но позволяет дисквалифицировать адвоката из расчетов.
ЧИСТРАБДНИ.МЕЖДУ использовать в более новых версиях Excel (2010 и более поздних версиях). По аналогии с РАБДЕНЬ.МЕЖДУ функция дает возможность насложнить режим «выходных» дней.
Синтаксис ЧИСТРАБДНИ.МЕЖДУ() следующих:
= ЧИСТРАБДНИ.МЕЖДУ (нач_дата, кон_дата, [выходные], [праздники])
Аргументы нач_дата, кон_дата и [праздники] работают точно так же, как и в ЧИСТРАБДНИ().
[Выходные] принимают возможные значения:
ДОЛЯГОДА ()
Иногда полезно использовать, какова разница между двумя датами существования года.
Давайте проведем грубые расчеты, сколько россиян находятся в «нетгрубомрезвом» состоянии на новогодних праздниках относительно всего года. Воспользуемся частотой ДОЛЯГОДА():
=ДОЛЯГОДА нач_дата, кон_дата, [базис])
Первые аргументымогут быть распространенными датами или ссылками на клетку.
[базис] является необязательным аргументом, он позволяет указать «правила» для измерения равила Наиболее часто используемый вариант 1:
0.02(2) года… Какая маленькая цифра. А еще говорят, что у нас самый «загульный» народ. А мы опровергли ложные домыслы!
Преобразование даты и времени из текста
ДАТАЗНАЧ () и ВРЕМЯЗНАЧ ()
Все вышеперечисленные функции отлично работают со стандартными номерами в формате даты в Excel. К сожалению, сроки и время часто импортируются на листы в виде текста. Чтобы перевести текстовые таблицы в формат статистики и использовать функции ДАТАЗНЗАЧ() и ВРАЧ() и ВРАЧ() Синтаксис для каждого следующего:
= ДАТАЗНАЧ (дата_как_текст)
= ВРЕМЯЗНАЧ (время_как_текст)
Аргументы функций могут принимать любую текстовую версию, которая выглядит как дата обновления
Примеры работыфункции:
Будьте внимательны!
Одна из проблем, с которой часто используются пользователи Excel, Заключается в том, что программа иногда неверно интерпретирует текстовые поля как скорость Пример здесь:
Видите, что разное положение цифр 20 произошло на исходный результат. Поэтому будьте осторожны при вводе данных, особенно если Вы импортируете данные из других источников. Что не получилось, как в моей истории в начале статьи))
Помните: время очень ценный ресурс. И каждую секунду на счет. Excel знает об этом все. Поэтому будьте внимательнее и не теряйте время!