Как преобразовать серийный номер на сегодняшний день в Excel?

Как преобразовать серийные номера в даты в Excel (2 простых способа)

Excel хранит значения даты и времени как порядковые номера в серверной части.

Это означает, что даже если вы видите в ячейке дату, например, «10 Jan2021-2022» или «01.10.2020», Excel хранит ее на стороне сервера как число. Это полезно, поскольку позволяет пользователям легко добавлять/удалять даты и время в Excel.

Например, в Microsoft Excel для Windows «01 января 1900 года» хранится как 1, «02 января 1900 года» — как 2 и т. д.

В колонке A отображаются числа, а в колонке B — дата, которую представляют эти числа.

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

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

Как же мы можем превратить эти серийные номера в даты?

Это и является темой данного урока.

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

Примечание: Excel для Windows использует систему дат 1900 (это означает, что число 1 будет обозначать 1 января 1900 года, в то время как Excel для Mac использует систему дат 1904 (число 1 будет обозначать 1 января 1904 в Excel на Mac).

Формат даты в Excel

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

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

Excel может сохранить дату с 01 января 1900 года; если вы попытаетесь сохранить что-либо до этой даты, Excel не распознает даты до этой даты.

Рассмотрите ячейку с номером 1.

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

Нажмите на формат даты, указанный выше, и вы увидите дату 1 января 1900 г.

Аналогичный формат будет использоваться также для 2 и 3. Они будут отформатированы как 02 января 1900 года и 03 января 1900 года соответственно. Даты в Excel форматируются с помощью этого метода.

# 1 — Как изменить формат даты в Excel?

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

Теперь нам нужно заменить его на что-то вроде этого. Выполните следующие шаги, чтобы изменить его.

Шаг 1: Во-первых, выберите все даты.

Шаг 2: Щелкните правой кнопкой мыши и выберите Формат ячеек Excel, или вы можете нажать Ctrl + 1 как сочетание клавиш.

Шаг 3: появится следующее окно.

Шаг 4: Теперь перейдите в раздел Настроить.

Шаг 5: На данном этапе у нас есть формат dd-mm-yyy. Поэтому измените это на dd-mm-mm-yyy.

В Шаге 6, после применения этого формата, нажмите OK. Теперь появятся наши результаты.

# 2 — Различные типы форматов даты

Формат 1: Таким образом, мы можем изменить формат даты по своему усмотрению. Теперь примените приведенный ниже код, чтобы увидеть результат 25 февраля 2018 года.

Вот такой результат мы имеем сейчас.

Теперь дата будет отформатирована с использованием кода дд ммгг.

Результат будет следующим

Формат 3: Подобно тому, как мы можем отображать полное название месяца, мы также можем отображать день с соответствующим названием дня с сокращенным и полным названием.

Результат получился следующим.

Формат 4: Если вам нужен целый день и целый месяц, используйте код «ddddddd mmmm yyyyyy».

В результате получается следующее

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

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

Код дня:

Код месяца:

Годовой кодекс ::.

# 3 — Как исправить ошибки даты в Excel?

Мы изучили динамику форматирования даты в Excel. Когда мы загружаем данные из Интернета, мы часто не видим именно тот формат даты, который хотели; эти ошибки необходимо исправлять.

Мы не можем увидеть точные даты, когда дата отформатирована как TEXT.

Несколько подходов могут помочь нам преодолеть эту проблему. В текстовом формате используйте функцию VALUE в Excel, чтобы получить серийный номер из дат.

Функция VALUE преобразует текстовое число в действительное.

V ALUE возвращает серийные номера в качестве результата. Теперь даты будут правильными после применения формата даты.

Теперь у нас есть правильные даты.

Иногда, когда мы загружаем данные из Интернета, мы обычно не получаем правильный формат даты; один из таких случаев — дата с обратной косой чертой.

Шаг 1: Нажмите Ctrl + H, чтобы открыть диалоговое окно Excel «Найти и заменить».

Второй шаг — найти что: прямоугольный обратный слеш (). И в пункте Заменить: введите косую черту (/).

Результаты появятся после того, как мы нажмем кнопку Replace All (Заменить все).

Как добавить или вычесть дату и время в Microsoft Excel?

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

Сериализация информации о дате и времени в 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 в другую ячейку и отформатируйте эту ячейку как дату с временем.

Теперь скопируйте эти две ячейки и вставьте их в другой ряд. Выделите две ячейки, щелкните правой кнопкой мыши и отформатируйте их как простые числа. Вы можете выбрать отсутствие десятичных знаков для первой ячейки и 5 десятичных знаков для второй. Это должно выглядеть, как на картинке ниже, 31 Mar 18 9:45 = 43190.40625 в Excel.

Конвертация дат в числа

Вы можете просто ввести 43190.40625 в ячейку и изменить формат даты на время. Microsoft 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 и нажмите Enter. Вы увидите результат 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.

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

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

= «24.12.2018 ″ -« 12.05.2018 ″ (в результате вы получите 19)
= «20:00» — «C9» (в результате вы увидите 18:45, поскольку ячейка C9 имеет значение 1:15)

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

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

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

= NOW () возвращает текущее время

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

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

Убедитесь, что TODAY () отформатирован как дата:

= TODAY () возвращает дату сегодняшнего дня

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

= TODAY () + 5 прибавляет пять дней от сегодняшнего дня

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

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

Функции работы с датами в Excel

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

Функция определения дня недели в Excel

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

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

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

Временные функции Excel

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

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

Пример функции определения даты и времени

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

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

Функция ДИСТАНЦИЯ позволяет рассчитать количество лет, месяцев или дат между двумя датами. Синтаксис следующий:

= Расстояние (дата начала, дата окончания, единица измерения)

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

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

Примеры вычитания данных при помощи DATEDIF:

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

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

Получение сегодняшней даты

Если вам нужно выполнить расчет на основе сегодняшней даты, просто используйте функцию Excel DAY (). Результат будет отражать дату, когда он был использован.

Следующий снимок экрана с использованием функции TODAY () был сделан 16 мая 2016 года —

РАБОЧИЙ ДЕНЬ

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

РАБОЧИЙ ДЕНЬ.ИНТЛ

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

РАБОЧИЕ ДНИ

Возвращает серийный номер даты до или после указанного числа рабочих дней.

РАБОЧИЙ ДЕНЬ. ИНТЛ

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

Например, с помощью TODAY и WORKDAY можно указать 15-й рабочий день, начиная с сегодняшнего (скриншот ниже, сделанный 16 мая 2016 года).

Предположим, 25 мая 2016 года и 1 июня 2016 года — выходные. Тогда ваш расчет будет следующим:

Как преобразовать серийный номер в дату в Excel

Чтобы перевести серийный номер в формат даты, выполните следующее

1. Выберите ячейку, которую нужно отформатировать.
2. На вкладке Главная в группе Номер нажмите правую нижнюю стрелку.
3. В диалоговом окне Формат ячеек перейдите на вкладку Число.
4. Нажмите Дата
5. На странице Дата выберите формат даты, в котором должен отображаться серийный номер.
6. Затем нажмите OK.

Выберите поле, которое нужно отформатировать.

Щелкните стрелку в правом нижнем углу на вкладке «Главная страница» в Номер группа или пресс Ctrl + 1 все вместе.

Отобразится диалоговое окно Формат ячеек.

В диалоговом окне перейдите на вкладку Числа.

Нажмите кнопку Дата на левой панели.

Выберите желаемый формат даты в поле просмотра на странице Дата.

Затем хорошо прижмите.

Порядковый номер становится днём.

Как вы форматируете дату в Excel?

Что касается формата даты, то сначала пишется день, затем месяц, затем год (yyyy-mm-yyy). В некоторых странах, например, в странах Востока, таких как Иран, Китай и Корея, сначала пишется год, затем месяц, а день — последним (yyyy-mm-dd).

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

.