Как найти значение с последней датой в Excel?

Как сделать сравнение дат в excel?

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

Как Excel обрабатывает время

Excel «рассматривает» дату и время как обычное число. Электронная таблица преобразует эти данные, приравнивая один день к одной единице измерения. В результате временная стоимость составляет долю единицы. Например, 12,00 равно 0,5.

Электронная таблица преобразует значение даты в число, равное количеству дней между 1 января 1900 года и заданной датой (число определяется разработчиками). Например, при преобразовании 13 апреля 1987 года в 31880 получается 31880. Между 1 января 1900 года и сегодняшним днем прошло 31 880 дней.

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

Пример функции ДАТА

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

Синтаксис: год; месяц; день

Все аргументы являются обязательными. Числа могут быть введены как числа или ссылки на ячейки с числовыми данными: для года — от 1900 до 9999; для месяца — от 1 до 12; для дня — от 1 до 31.

Если аргумент «День» установлен в большее число (чем количество дней в указанном месяце), то лишние дни будут перенесены на следующий месяц. Например, если вы укажете 32 дня для декабря, результатом будет 1 января.

Пример использования функции:

Июнь должен иметь большее количество дней:

Примеры использования ссылок на ячейки как аргументов:

Функция РАЗНДАТ в Excel

Выдает разницу между двумя датами.

• Дата начала;
• Дата окончания;
• Код, указывающий на единицы подсчета (дни, месяцы, годы и т. д.).

Способ измерения интервалов между заданными датами:

• Показать результат в днях — «d»;
• В месяцах — «m»;
• В годах — «y»;
• В месяцах без учета лет — «ym»;
• В днях без учета месяцев и лет — «md»;
• В днях без учета лет — «yd

В некоторых версиях Excel использование двух последних аргументов («md», «yd») может привести к тому, что функция выдаст неверное значение. Лучше использовать альтернативные формулы.

Примеры использования функции RANGE:

В версии Excel 2007 эта функция отсутствует в справочнике, но она работает. Однако результаты все равно следует проверять, так как могут возникнуть ошибки.

Функция ГОД в Excel

Возвращает год как целое число (от 1900 до 9999), соответствующий заданной дате. Структура функции имеет только один аргумент — дату в числовом формате. Аргумент должен быть введен функцией DATE или представлять собой результат вычисления других формул.

Пример использования функции YEAR (ГОД):

Функция МЕСЯЦ в Excel: пример

Возвращает месяц как целое число (от 1 до 12) для указанной даты в числовом формате. Аргументом является дата месяца, которая должна быть отображена, в числовом формате. Даты в текстовом формате некорректно обрабатываются функцией.

Вы можете использовать функцию MONTH для:

Примеры функций ДЕНЬ, ДЕНЬНЕД и НОМНЕДЕЛИ в Excel

Возвращает день как целое число (от 1 до 31) для заданной даты в числовом формате. Аргументом является дата дня, которую нужно найти, в числовом формате.

Чтобы вернуть порядковый номер дня недели для указанной даты, можно воспользоваться функцией DAYNED:

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

Функция ЧИСЛО применяется для отображения порядкового номера недели на указанную дату:

Дата 24 мая 2015 года приходится на 22-ю неделю года. Неделя начинается в воскресенье (по умолчанию).

Во втором аргументе указано 2. Соответственно, формула предполагает, что неделя начинается в понедельник (второй день недели).

Загрузить примеры функций для работы с датами

Для указания текущей даты используется функция TODAY (без аргументов). Для отображения текущего времени и даты используется функция TDATA ().

Excel сравнение дат

В разделе Программное обеспечение, в ответ на вопрос Здравствуйте. Подскажите, пожалуйста, как сравнить две даты в Excel. То есть в одной ячейке например день рождения, заданный автором Misfit лучший ответ это сравнить, значит уметь расставлять если ячейка имеет тип «дата», то все очевидно — расставляется автоматически

Ответ от Abram Pupkin
вы изначально поставили неправильно вопрос. возмем 2-е даты: например 02.01.1990 и 25.11.2010 как надо сравнить эти даты : по расположению (справа/слева) , какая дата больше, сколько прошло дней (недель, месяцев) , сколько осталось до

Видео

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

Как вводить даты и время в Excel

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

Применение дефисов

Использование дроби

Дата в ячейке может выглядеть очень по-разному (с годом или без, месяц как число или слово и т.д.) и устанавливается через контекстное меню — щелкните правой кнопкой мыши на ячейке и выберите Формат ячеек:

Время вводится в ячейки с двоеточием. Например

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

Наконец, никто не запрещает ставить дату и время вместе с пробелом, т.е.

27.10.2012 16:45

Быстрый ввод дат и времени

Чтобы ввести сегодняшнюю дату в текущую ячейку, можно использовать сочетание клавиш Ctrl + J (или CTRL + SHIFT + 4, если по умолчанию используется другой язык).

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

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

Функция TODAY содержит сегодняшнюю дату во всех ячейках:

Как Excel на самом деле хранит и обрабатывает даты и время

Это изображение появляется, если выделить ячейку, содержащую дату, и установить для нее формат Общий (щелкните правой кнопкой мыши Формат ячеек — Число — вкладка Общий).

То есть, в терминах Excel, 27.10.2012 15:42 = 41209,65417

На самом деле Excel хранит и обрабатывает каждую дату именно таким образом — как число с целой и дробной частью. Целая часть этого числа (41209) — это количество дней с 1 января 1900 года (это точка отсчета) до сегодняшнего дня. Дробная часть (0,65417) — это доля дня (1 день = 1,0).

На основании этих фактов можно сделать два чисто практических вывода:

• Прежде всего, Excel не умеет работать (без дополнительных настроек) с датами до 1 января 1900 года. Но мы это преодолеем 😉
• Во-вторых, в Excel можно выполнять любые математические операции с датами и временем. Потому что это действительно числа! А это уже открывает множество возможностей для пользователя.

Количество дней между двумя датами

Чтобы рассчитать разницу в днях, вычтите дату начала из даты окончания и переведите результат в общий численный формат:

Количество рабочих дней между двумя датами

В данном случае ситуация немного сложнее. Выходные, праздники и воскресенья необходимо игнорировать. NETWORKDAYS — лучший метод, когда речь идет об этом расчете из категории Дата и время. В качестве аргументов этой функции необходимо указать начальную и конечную даты и ячейки с датами праздников (государственных праздников, больничных, отпусков, отгулов и т.д.):

Примечание: Эта функция является стандартным набором функций Excel, начиная с версии 2007 и выше. В более ранних версиях необходимо сначала активировать надстройку Analysis Package. Для этого перейдите в Инструменты — Дополнения и установите флажок Analisys Toolpak. После этого функция NETWORKDAYS появится в мастере функций в категории Дата и время.

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

Это гораздо проще рассчитать, если вы прочитаете здесь о том, как это сделать.

Сдвиг даты на заданное количество дней

Поскольку день считается единицей в системе отсчета дат Excel (см. выше), для расчета даты с разницей в 20 дней просто добавьте это число к дате.

Сдвиг даты на заданное количество рабочих дней

Функция WORKDAY выполняет эту задачу. Она позволяет рассчитать дату, которая сдвинута вперед или назад по отношению к начальной дате на нужное количество рабочих дней (с учетом суббот и воскресений и национальных праздников). Это аналогично использованию функции СЕТЕВЫЕ ДНИ, описанной выше.

Вычисление дня недели

Вы не родились в понедельник? Нет? Вы уверены? Вы можете легко проверить это с помощью функции WEEKDAY в категории Дата и время.

Первый аргумент этой функции — ячейка даты, второй — тип подсчета дней недели (наиболее удобный — 2).

Вычисление временных интервалов

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

Здесь есть только один нюанс. Если сложить несколько временных интервалов и сумма окажется больше 24 часов, Excel обнулит ее и начнет суммирование с нуля. Следовательно, для ячейки «Итого» необходим формат 37:30:55:

Формат даты Excel без года

Я ввожу дни рождения сотрудников, но они приходят без годов по причинам чувствительности. Когда я ввожу месяц и день, Excel добавляет 2012 год в качестве года! Очевидно, что это неправильно. Я смог отформатировать ячейки, чтобы отображались только месяц и день, но мне не нравится, что отображается год, потому что это неточно.

Есть ли способ сохранить формат даты, но не включать автоматически год?

6 ответов

Короткий ответ :
Невозможно иметь дату без года, это просто обязательная часть даты. Точно так же, как вторая часть является обязательной частью времени суток — если вы входите, скажем, «1:15», фактическое значение будет «01:15:00» (или что-то в этом роде, в зависимости от ваших региональных настроек) , Единственное, что вы можете сделать, это форматировать столбец как текст, использовать формат даты, который скрывает год, или трюки, подобные использованию формул, чтобы извлечь день и месяц в другой столбец и скрыть столбец с фактической датой.

Объяснение :
Excel хранит даты как количество дней с 0 (да, ноль) января 1900 года. Так что сегодня, 23 августа 2012 года, будет храниться как 41144, а 23 августа 2008 года будет сохранено как 39683. Из-за этого Excel требуется , чтобы узнать год, чтобы рассчитать правильное количество дней. 23 августа само по себе является неоднозначным, поскольку он может решить более 100 различных значений (по одному на каждый год с 1900 года), поэтому Excel добавляет сам год, если вы его опустите.

В течение дня Excel добавляет к числу дней дробь, которая является долей прошедшего 24-часового дня. Например, 06:00 (или 6:00) записывается как 0,25, потому что в это время прошло 25% дня.

Вы можете увидеть это на практике, введя дату и/или время (или просто =NOW() для текущей даты/времени) в ячейку в Excel и изменив формат на Число. Для достижения полной точности следует также увеличить количество знаков после запятой.

Например, текущее время (20:19 в моем часовом поясе на момент написания статьи) записывается как 41144,846945255.

Насколько я знаю, это невозможно. Вы можете отформатировать ячейку как mm/dd, но если это дата, то она состоит из трех частей: месяц, день и год. Excel добавляет год, когда вы вводите только месяц и день. Вы увидите его как mm / dd, но поскольку дата, по определению, должна включать год, чтобы выполнить арифметику даты, у вас нет особого выбора. Единственный способ сделать это — отформатировать его как текстовое поле. Но вы потеряете преимущества форматирования данных. Что вам нужно, позвольте спросить? В большинстве случаев вы можете не использовать год в арифметике, если он вас беспокоит.

Создайте два столбца. В первом просто укажите месяц. Во второй строке укажите день, разделенный пробелом, используя 0 для дней с одной цифрой. Таким образом, 2 апреля будет выглядеть как Первый столбец: Апрель, Второй столбец 0 2. Excel не воспринимает это как дату и не форматирует ее. 🙂

Во-первых, у вас нет права на правую колонку, которую вы хотите расширить.

Вам нужно перейти к иконке «Текст в столбцы». Нажмите на него.

Оставьте флажок «Без ограничений» и нажмите «Далее».

Нажмите «Другое». Вот мои даты (с косой чертой): xx /xx /xxx. Набрав / для «другое», я нажал «далее».

На следующем экране выберите «Общие» и нажмите кнопку «Готово».

Теперь ваши даты будут расположены в 3 столбцах. Колонка месяц будет выглядеть неважно, следующая колонка — день, а последняя — год. Не волнуйтесь.

Выберите пункт Текст из контекстного меню правой кнопки мыши после выделения столбца разделенного месяца. Теперь ваш месяц в порядке.

Удалите колонку в течение года, потому что не хотели

Таким образом, теперь у вас есть месяц и день в отдельных столбцах.

Вы хотите, чтобы они были вместе? Скажем, месяц находится в столбце B1, а день — в столбце B2. При пустом столбце слева примерная формула такова: =Concatenate(b1,»/»,b2) и Enter.

В результате у вас получится что-то похожее на 12/31. Возьмите 12/31 и вставьте его по всей длине вашей таблицы/данных.

Excel works!

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

Функция ГОД в Excel

Пример. Дана таблица данных о продажах (см. выше), и вам нужно найти данные о продажах по годам.

В отдельном столбце записываем функцию ГОД, как показано на картинке выше. У этой функции только один аргумент — дата в числовом формате (именно в числовом формате — если формула не работает проверьте верно ли задана дата). Соответственно результатом работы формулы будет номер года в числовом значении.

Затем в колонке I рассчитайте сумму в год, используя SLEEP

Функция МЕСЯЦ в Excel

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

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

Сделать это несложно с помощью автофильтра

Функция ДЕНЬ в Excel

Функция ДЕНЬ работает точно так же как и две предыдущих — она считает порядковый номер дня этой даты в месяце. Т.е. если сегодня 22.07, число 22 получаем именно функцией ДЕНЬ:

D AYNED является еще более интригующим. Он находит номер дня недели по числу и выводит это число.

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

После чего мы можем построить график — в какой день недели было больше всего продаж.

Получается, что в пятницу продажники почти не работают — делаем выводы.

Для функции ДЕНЬНЕД есть небольшая хитрость — это дополнительный аргумент, если оставить это поле пустым, то дни недели будут считаться по американской системе (воскресенье первый день недели), а если поставить 2, то по европейской — к которой мы привыкли.

В одном файле можно загрузить три функции и примеры графиков.

Статьи, которые будут полезны вместе с этой статьей. Функции ГОД, МЕСЯЦ, ДЕНЬ в Excel:

• Как группировать данные по дням в сводной таблице
• Как подсчитать количество дней между датами, включая количество рабочих дней
• Функция TODAY и все, что с ней связано.

Время и дата очень обширные темы в Excel. Возможно, я что-то забыл описать — пишите, пожалуйста, вопросы в комментариях.