Poldet1.ru

Пул Дет №1
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

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

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

Как преобразовать серийные номера в даты в 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.

Формат даты в Excel, шаг 1

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

Читайте так же:
Как разрешить изменение только определенных ячеек в Excel?

Формат даты Excel, шаг 2

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

Формат даты Excel, шаг 3

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

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

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

изменить формат даты Шаг 1

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

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

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

изменить формат даты Шаг 3

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

изменить формат даты Шаг 4

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

изменить формат даты Шаг 5

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

пример формата даты в Excel 2.5

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

пример формата даты в Excel 2.6

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

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

пример формата даты в Excel 3.9

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

Пример формата даты в Excel 3.10

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

пример формата даты в Excel 3.3

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

пример формата даты в Excel 3.4

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

Пример формата даты в Excel 3.5

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

пример формата даты в Excel 3.6

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

пример формата даты в Excel 3.7

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

пример формата даты в Excel 3.8

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

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

Код дня:

Пример формата даты в Excel 4.1

Код месяца:

Пример формата даты в Excel 4.2

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

Пример формата даты в Excel 4.3

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

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

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

формат как текст, пример 5.1

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

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

преобразовать в число пример 5.2

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

применить пример формата 5.3

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

пример результата 5.4

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

Читайте так же:
Как разделить книгу на отдельные файлы Excel в Excel?

пример данных даты 6.1

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

найти и заменить пример 6.2

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

найти и заменить пример 6.3

Результаты появятся после того, как мы нажмем кнопку 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.

Читайте так же:
Как просуммировать диагональ диапазона в Excel?

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

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

Вы можете просто ввести 43190.40625 в ячейку и изменить формат даты на время. Microsoft Excel автоматически преобразует число в формат даты, который имеет время 31 марта 18 года 9:45 утра.

Помните, что число без десятичной точки в формате времени считается как 00:00:00. В Excel временем считается только десятичное число.

Формат даты, числа и времени в 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 вычитает три дня из сегодняшнего дня

Читайте так же:
Как преобразовать шестнадцатеричное число в десятичное в Excel?

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Читайте так же:
Как ранжировать данные без сортировки в Excel?

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

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

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

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

S.No.Описание функции1.

РАБОЧИЙ ДЕНЬ

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

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

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

РАБОЧИЕ ДНИ

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

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

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

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

В поисках рабочего дня

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

расчет

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector