Excel формат даты только число

One nice feature of Microsoft Excel is there’s usually more than one way to do many popular functions, including date formats. Whether you’ve imported data from another spreadsheet or database, or are merely entering due dates for your monthly bills, Excel can easily format most date styles.

How to Change Excel Date Format Via the Format Cells Feature

With the use of Excel’s many menus, you can change up the date format within a few clicks.

1. Select the Home tab.

2. In the Cells group, select Format and choose Format Cells.

3. Under the Number tab in the Format Cells dialog, select Date.

4. As you can see, there are several options for formatting in the Type box.

   You could also look through the Locale (locations) drop-down to choose a format best suited for the country you’re writing for.

5. Once you’ve settled on a format, select OK to change the date format of the selected cell in your Excel spreadsheet.

Make Your Own With Excel Custom Date Format

If you don’t find the format you want to use, select Custom under the Category field to format the date how you’d like. Below are some of the abbreviations you’ll need to build a customized date format.

1. Select the Home tab.

2. Under the Cells group, select the Format drop-down, then select Format Cells.

3. Under the Number tab in the Format Cells dialog, select Custom. Just like the Date category, there are several formatting options.

4. Once you’ve settled on a format, select OK to change the date format for the selected cell in your Excel spreadsheet.

How to Format Cells Using a Mouse

If you prefer only using your mouse and want to avoid maneuvering through multiple menus, you can change the date format with the right-click context menu in Excel.

1. Select the cell(s) containing the dates you want to change the format of.

2. Right-click the selection and select Format Cells. Alternatively, press Ctrl+1 to open the Format Cells dialog.

   Alternatively, select Home > Number, select the arrow, then select Number Format at the bottom right of the group. Or, in the Number group, you can select the drop-down box, then select More Number Formats.

3. Select Date, or, if you need a more customized format, select Custom.

4. In the Type field, select the option that best suits your formatting needs. This might take a bit of trial and error to get the right formatting.

5. Select OK when you’ve chosen your date format.

   Whether using the Date or Custom category, if you see one of the Types with an asterisk (*) this format will change depending on the locale (location) you have selected.

Using Quick Apply for Long or Short Date

If you need a quick format change from to either a Short Date (mm/dd/yyyy) or Long Date (dddd, mmmm dd, yyyy or Monday, January 1, 2019), there’s a quick way to change this in the Excel Ribbon.

1. Select the cell(s) for which you want to change the date format.

2. Select Home.

3. In the Number group, select the drop-down menu, then select either Short Date or Long Date.

Using the TEXT Formula to Format Dates

This formula is an excellent choice if you need to keep your original date cells intact. Using TEXT, you can dictate the format in other cells in any foreseeable format.

To get started with the TEXT formula, go to a different cell, then enter the following to change the format:

=TEXT(##, “format abbreviations”)

## is the cell label, and format abbreviations are the ones listed above under the Custom section. For example, =TEXT(A2, “mm/dd/yyyy”) displays as 01/01/1900.

Using Find & Replace to Format Dates

This method is best used if you need to change the format from dashes (-), slashes (/), or periods (.) to separate the month, day, and year. This is especially handy if you need to change a large number of dates.

1. Select the cell(s) you need to change the date format for.

2. Select Home > Find & Select > Replace.

3. In the Find what field, enter your original date separator (dash, slash, or period).

4. In the Replace with field, enter what you’d like to change the format separator to (dash, slash, or period).

5. Then select one of the following:

   • Replace All: Which will replace all the first field entry and replace it with your choice from the Replace with field.
   • Replace: Replaces the first instance only.
   • Find All: Only finds all of the original entry in the Find what field.
   • Find Next: Only finds the next instance from your entry in the Find what field.

Using Text to Columns to Convert to Date Format

If you have your dates formatted as a string of numbers and the cell format is set to text, Text to Columns can help you convert that string of numbers into a more recognizable date format.

1. Select the cell(s) that you want to change the date format.

2. Make sure they are formatted as Text. (Press Ctrl+1 to check their format).

3. Select the Data tab.

4. In the Data Tools group, select Text to Columns.

5. Select either Delimited or Fixed width, then select Next.

   Most of the time, Delimited should be selected, as date length can fluctuate.

6. Uncheck all of the Delimiters and select Next.

7. Under the Column data format area, select Date, choose the format your date string using the drop-down menu, then select Finish.

Using Error Checking to Change Date Format

If you’ve imported dates from another file source or have entered two-digit years into cells formatted as Text, you’ll notice the small green triangle in the top-left corner of the cell.

This is Excel’s Error Checking indicating an issue. Because of a setting in Error Checking, Excel will identify a possible issue with two-digit year formats. To use Error Checking to change your date format, do the following:

1. Select one of the cells containing the indicator. You should notice an exclamation mark with a drop-down menu next to it.

2. Select the drop-down menu and select either Convert XX to 19XX or Convert xx to 20XX, depending on the year it should be.

3. You should see the date immediately change to a four-digit number.

Using Quick Analysis to Access Format Cells

Quick Analysis can be used for more than formatting the color and style of your cells. You can also use it to access the Format Cells dialog.

1. Select several cells containing the dates you need to change.

2. Select Quick Analysis in the lower right of your selection, or press Ctrl+Q.

3. Under Formatting, select Text That Contains.

4. Using the right drop-down menu, select Custom Format.

5. Select the Number tab, then select either Date or Custom.

6. Select OK twice when complete.

Thanks for letting us know!

Преобразование строки даты в дату

= ЛЕВСИМВ (дата; 10) + ПСТР (дата; 12;8)

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

Чтобы преобразовать строку даты в дату-время (дату со временем), вы можете разобрать текст на отдельные компоненты, а затем построить правильное время и дату.

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

Для извлечения даты формула в C5:

= ДАТАЗНАЧ (ЛЕВСИМВ(B5;10))

Чтобы извлечь дату, формула в D5:

= ВРЕМЗНАЧ (ПСТР (B5;12;8))

Чтобы собрать дату-время, формула в E5:

= C5 + D5

Чтобы получить дату, мы извлекаем первые 10 символов значения с помощью ЛЕВСИМВ:

ЛЕВСИМВ(B5;10) // возвращает «2015-03-01»

Результатом является текст, поэтому, чтобы заставить Excel интерпретироваться как дата, мы помещаем ЛЕВСИМВ в ДАТАЗНАЧ, который преобразует текст в правильное значение даты Excel.

Чтобы получить время, мы извлекаем 8 символов из середины значения с ПСТР:

ПСТР (B5;12;8) // возвращает «12:28:45»

Опять же, результатом является текст. Чтобы заставить Excel интерпретироваться как время, мы помещаем ПСТР в ВРЕМЗНАЧ, который преобразует текст в правильное значение времени Excel.

Чтобы построить окончательную дата-время, мы просто добавляем значение даты к значению времени.

Хотя этот пример извлекает дату и время отдельно для ясности, вы можете комбинировать формулы, если хотите. Следующая формула извлекает дату и время и объединяет их в один шаг:

= ЛЕВСИМВ(дата; 10) + ПСТР(дата; 12;8)

Обратите внимание, что в этом случае значения ДАТАЗНАЧ и ВРЕМЯЗНАЧ не нужны, поскольку математическая операция (+) заставляет Excel автоматически принудительно передавать текстовые значения в числа.

Преобразовать дату в Юлианский формат

= ГОД (дата) и ТЕКСТ (дата-ДАТА (ГОД (дата); 1;0); «000»)

Если вам нужно преобразовать дату в формат даты в Юлиане в Excel, вы можете сделать это, построив формулу, в которой используются функции ТЕКСТ, ГОД и ДАТА.

«Формат даты в Юлиане» относится к формату, в котором значение года для даты комбинируется с «порядковым днем для этого года» (т. Е. 14-й день, 100-й день и т. д.) для формирования штампа даты.

Есть несколько вариантов. Дата в этом формате может включать в себя 4-значный год (гггг) или год с двумя цифрами (гг), а номер дня может быть заполнен нулями или может быть не дополнен тремя цифрами. Например, на дату 21 января 2017 года вы можете увидеть:

1721 // ГГД

201721 // ГГГГ

2017021 // ГГГГДДД

Для двухзначного года + число дня без дополнения используйте:

= ТЕКСТ (B5; «гг») & B5-ДАТА(ГОД (B5); 1;0)

Для двузначного года + число дня, дополненное нулями до 3-х мест:

= ТЕКСТ (B5; «гг») & ТЕКСТ (B5-ДАТА (ГОД (B5); 1;0); «000»)

Для четырехзначного года + число дня, дополненное нулями до 3-х мест:

= ГОД(B5) & ТЕКСТ(B5-ДАТА(ГОД(B5); 1;0); «000»)

Эта формула строит окончательный результат в 2 частях, объединенных конъюнкцией с оператором амперсанда (&).

Слева от амперсанда мы генерируем значение года. Чтобы извлечь 2-значный год, мы можем использовать функцию ТЕКСТ, которая может применять числовой формат внутри формулы:

ТЕКСТ (B5; «гг»)

Чтобы извлечь полный год, используйте функцию ГОД:

ГОД (B5)

С правой стороны амперсанда нам нужно определить день года. Мы делаем это, вычитая последний день предыдущего года с того дня, с которым мы работаем. Поскольку даты — это просто серийные номера, это даст нам «n» день года.

Чтобы получить последний день года предыдущего года, мы используем функцию ДАТА. Когда вы даете ДАТА значение года и месяца и ноль на день, вы получаете последний день предыдущего месяца. Так:

B5-ДАТА(ГОД (B5); 1;0)

Дает нам последний день предыдущего года, который на примере 31 декабря 2015 года.

Теперь нам нужно заполнить значение дня нулями. Опять же, мы можем использовать функцию ТЕКСТ:

ТЕКСТ (B5-ДАТА (ГОД (B5); 1;0); «000»)

Если вам нужно преобразовать юлианскую дату назад к обычной дате, вы можете использовать формулу, которая анализирует юлианскую дату и пробегает ее через функцию даты с месяцем 1 и днем, равным «n-му» дню. Например, это создаст дату с Юлианской датой ггггддд, например, 1999143.

= ДАТА(ЛЕВСИМВ(A1;4); 1; ПРАВСИМВ(A1;3)) // для ггггддд

Если у вас есть только номер дня (например, 100, 153 и т. д.), вы можете жестко закодировать год и вставить следующий день:

= ДАТА (2016;1; A1)

Где A1 содержит номер дня. Это работает, потому что функция ДАТА умеет настраивать значения вне диапазона.

Преобразование даты в месяц и год

= ТЕКСТ(дата; «ггггмм»)

Чтобы преобразовать нормальную дату Excel в формат ггггмм (например, 9/1/2017> 201709), вы можете использовать функцию ТЕКСТ.

В показанном примере формула в C5:

= ТЕКСТ (B5; «ггггмм»)

Функция TEКСT применяет заданный числовой формат к числовому значению и возвращает результат в виде текста.

В этом случае предоставляется формат числа «ггггмм», который присоединяется к 4-значному году с 2-значным значением месяца.

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

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

= ТЕКСТ (дата; формат)

Если вам нужно преобразовать даты в текст (т. е. дату в преобразование строк), вы можете использовать функцию ТЕКСТ. Функция ТЕКСТ может использовать такие шаблоны, как «дд / мм / гггг», «гггг-мм-дд» и т. д., чтобы преобразовать действительную дату в текстовое значение.

Даты и время в Excel хранятся в виде серийных номеров и преобразуются в удобочитаемые значения «на лету» с использованием числовых форматов. Когда вы вводите дату в Excel, вы можете применить числовой формат, чтобы отобразить эту дату по своему усмотрению. Аналогичным образом, функция ТЕКСТ позволяет преобразовать дату или время в текст в предпочтительном формате. Например, если дата 9 января 2000 года введена в ячейку A1, вы можете использовать TEКСТ, чтобы преобразовать эту дату в следующие текстовые строки следующим образом:

= ТЕКСТ(A1; «ммм») // «Янв»

= TEКСТ(A1; «дд/мм/гггг») // «09/01/2012»

= ТЕКСТ(A1; «дд-ммм-гг») // «09-Янв-12»

Вы можете использовать TEКСТ для преобразования дат или любого числового значения в фиксированном формате. Вы можете просмотреть доступные форматы, перейдя в меню «Формат ячеек» (Win: Ctrl + 1, Mac: Cmd + 1) и выбрав различные категории в списке слева.

Преобразование даты текста дд/мм/гг в мм/дд/гг

= ДАТА(ПРАВСИМВ(A1;2) + 2000; ПСТР(A1;4;2); ЛЕВСИМВ(A1;2))

Чтобы преобразовать даты в текстовом формате дд/мм /гг в истинную дату в формате мм/дд/гг, вы можете использовать формулу, основанную на функции ДАТА. В показанном примере формула в C5:

= ДАТА(ПРАВСИМВ(B5;2) + 2000; ПСТР(B5;4;2); ЛЕВСИМВ (B5;2))

Который преобразует текстовое значение в B5 «29/02/16» в правильную дату Excel.

Ядром этой формулы является функция ДАТА, которая используется для сборки правильного значения даты Excel. Функция ДАТА требует действительных значений года, месяца и дня, поэтому они анализируются из исходной текстовой строки следующим образом:

Значение года извлекается с помощью функции ПРАВСИМВ:

ПРАВСИМВ(B5;2) +2000

ПРАВСИМВ получает по крайней мере 2 символа от исходного значения. Число 2000 добавлено к результату, чтобы создать действительный год. Это число переходит в ДАТА в качестве аргумента год.

Значение месяца извлекается с помощью:

ПСТР(B5;4;2)

ПСТР извлекает символы 4-5. Результат переходит в ДАТА в качестве аргумента месяц.

Значение дня извлекается с помощью:

ЛЕВСИМВ(B5;2)

ЛЕВСИМВ захватывает последние 2 символа исходного текстового значения, которое переходит в ДАТА в качестве аргумента дня.

Три значения, извлеченные выше, входят в ДАТУ следующим образом:

= ДАТА (2016; «02»; «29»)

Хотя месяц и день предоставляются в виде текста, функция ДАТА автоматически преобразуется в числа и возвращает действительную дату.

Примечание: значение 2016 года автоматически было преобразовано в число при добавлении 2000.

Если исходное текстовое значение содержит дополнительные начальные или конечные символы пробела, вы можете добавить функцию СЖПРОБЕЛЫ для удаления:

= ДАТА(ПРАВСИМВ (СЖПРОБЕЛЫ (A1); 2) + 2000; ПСТР(СЖПРОБЕЛЫ (A1); 4;2); ЛЕВСИМВ(СЖПРОБЕЛЫ (A1); 2))

Преобразование текста в дату

=ДАТА (ЛЕВСИМВ(текст; 4); ПСРТ(текст; 5;2); ПРАВСИМВ(текст; 2))

Чтобы преобразовать текст в непринятом формате даты в правильную дату Excel, вы можете проанализировать текст и собрать правильную дату с формулой, основанной на нескольких функциях: ДАТА, ЛЕВСИМВ, ПСРТ и ПРАВСИМВ.

В показанном примере формула в C6:

= ДАТА(ЛЕВСИМВ(B6;4); ПСРТ(B6;5;2); ПРАВСИМВ(B6;2))

Эта формула отдельно извлекает значения года, месяца и дня и использует функцию ДАТА, чтобы собрать их в дату 24 октября 2000 года.

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

текст (19610412) Дата представления (Апрель 12, 1961)

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

Функция ДАТА принимает три аргумента: год, месяц и день. ЛЕВСИМВ извлекает самые левые 4 символа и поставляет это в ДАТА в качестве года. Функция ПСРТ извлекает символы 5-6 и поставляет это в ДАТА в качестве месяца, а функция ПРАВСИМВ извлекает самые правые 2 символа и поставляет их в ДАТА в качестве дня. Конечным результатом является правильная дата Excel, которая может быть отформатирована любым способом.

В строке 8 (непризнанный) формат даты дд.мм.гггг и формула в C8:

= ДАТА(ПРАВСИМВ(B8;4); ПСРТ(B8;4;2); ЛЕВСИМВ(B8;2))

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

Чтобы преобразовать даты, добавив нуль, попробуйте Специальную вставку:

• Добавить ноль в неиспользуемую ячейку и скопировать в буфер обмена
• Выберите проблемные даты
• Специальная вставка> Значения> Добавить

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

= A1 + 0

Где A1 содержит непризнанную дату.

Другой способ заставить Excel распознавать даты — использовать текст в столбцах:

Выберите столбец дат, затем попробуйте Дата> Текст в столбах>Исправлено> Конец

Это иногда может исправить все сразу.

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

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

Если выделить ячейку с датой и установить для нее Общий формат (правой кнопкой по ячейке Формат ячеек – вкладка Число – Общий), то можно увидеть интересную картинку:

То есть, с точки зрения Excel, 27.10.2012 15:42 = 41209,65417

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

Из всех этих фактов следуют два чисто практических вывода:

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

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

Считается простым вычитанием – из конечной даты вычитаем начальную и переводим результат в Общий (General) числовой формат, чтобы показать разницу в днях:

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

Здесь ситуация чуть сложнее. Необходимо не учитывать субботы с воскресеньями и праздники. Для такого расчета лучше воспользоваться функцией ЧИСТРАБДНИ(NETWORKDAYS) из категории Дата и время. В качестве аргументов этой функции необходимо указать начальную и конечную даты и ячейки с датами выходных (государственных праздников, больничных дней, отпусков, отгулов и т.д.):

Примечание: Эта функция появилась в стандартном наборе функций Excel начиная с 2007 версии. В более древних версиях сначала необходимо подключить надстройку Пакета анализа. Для этого идем в меню Сервис – Надстройки (Tools – Add-Ins) и ставим галочку напротив Пакет анализа (Analisys Toolpak). После этого в Мастере функций в категории Дата и время появится необходимая нам функция ЧИСТРАБДНИ (NETWORKDAYS).

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

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

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

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

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

Примеры использования функции МЕСЯЦ:

ДЕНЬНЕД

Задача оператора ДЕНЬНЕД – выводить в указанную ячейку значение дня недели для заданной даты. Но формула выводит не текстовое название дня, а его порядковый номер. Причем точка отсчета первого дня недели задается в поле «Тип». Так, если задать в этом поле значение «1», то первым днем недели будет считаться воскресенье, если «2» — понедельник и т.д. Но это не обязательный аргумент, в случае, если поле не заполнено, то считается, что отсчет идет от воскресенья. Вторым аргументом является собственно дата в числовом формате, порядковый номер дня которой нужно установить. Синтаксис выглядит так:

=ДЕНЬНЕД(Дата_в_числовом_формате;[Тип])

“ВЫБОР”.

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

В этой ситуации вам поможет функция ВЫБОР и уже пройденная нами функция МЕСЯЦ. Построим формулу. Для этого нам необходимо указать пользовательское имя для всех 12 месяцев в функции и использовать функцию месяца, чтобы получить номер месяца из даты.

=ВЫБОР(МЕСЯЦ(A1);”Январь”;”Фев”;”Март”;”Апр”;”Май”;”Июнь”;”Июль”;”Авг”;”Сен”;”Октябрь”;” Ноябрь”;”декабрь”)

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

НОМНЕДЕЛИ

Предназначением оператора НОМНЕДЕЛИ является указание в заданной ячейке номера недели по вводной дате. Аргументами является собственно дата и тип возвращаемого значения. Если с первым аргументом все понятно, то второй требует дополнительного пояснения. Дело в том, что во многих странах Европы по стандартам ISO 8601 первой неделей года считается та неделя, на которую приходится первый четверг. Если вы хотите применить данную систему отсчета, то в поле типа нужно поставить цифру «2». Если же вам более по душе привычная система отсчета, где первой неделей года считается та, на которую приходится 1 января, то нужно поставить цифру «1» либо оставить поле незаполненным. Синтаксис у функции такой:

=НОМНЕДЕЛИ(дата;[тип])

Формула условия для дат с функцией ДАТАЗНАЧ (DATEVALUE)

Иногда случается, что записать дату непосредственно в функцию ЕСЛИ, не ссылаясь ни на какую ячейку. В этом случае возникают некоторые сложности.

В отличие от многих других функций Excel, ЕСЛИ не может распознавать даты и интерпретирует их как текст, как простые текстовые строки.

Поэтому вы не можете выразить свое логическое условие просто как >«15.07.2019» или же >15.07.2019. Увы, ни один из приведенных вариантов не верен.

Чтобы функция ЕСЛИ распознала дату в вашем логическом условии именно как дату, вы должны обернуть ее в функцию ДАТАЗНАЧ (в английском варианте – DATEVALUE).

Например, ДАТАЗНАЧ(«15.07.2019»).

Полная формула ЕСЛИ может иметь следующую форму:

=ЕСЛИ(B2<ДАТАЗНАЧ(“10.09.2019″),”Поступил”,”Ожидается”)

Как показано на скриншоте, эта формула ЕСЛИ оценивает даты в столбце В и возвращает «Послупил», если дата поступления до 10 сентября. В противном случае формула возвращает «Ожидается».

Расширенные формулы ЕСЛИ для будущих и прошлых дат

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

Выделим даты, отстоящие более чем на месяц от текущей, в прошлом. Укажем для них «Более месяца назад». Запишем это условие:

=ЕСЛИ(СЕГОДНЯ()-B2>30,”Более месяца назад”,””)

Если условие не выполнено, то в ячейку запишем пустую строку “”.

А для будущих дат, также отстоящих более чем на месяц, укажем «Ожидается».

=ЕСЛИ(B2-СЕГОДНЯ()>30,”Ожидается”,””)

Если все результаты попробовать объединить в одном столбце, то придется составить выражение с несколькими вложенными функциями ЕСЛИ:

=ЕСЛИ(СЕГОДНЯ()-B2>30,”Более месяца назад”, ЕСЛИ(B2-СЕГОДНЯ()>30,”Ожидается”,””))

Перевод разных написаний дат

Разные системы в выгрузках выдают даты по-разному, например: 12.07.2016 12-07-16 16-07-12 и так далее. Иногда месяца пишут текстом. Для того, чтобы привести даты к одному формату мы используем функцию ДАТА:

Синтаксис: =ДАТА(ЛЕВСИМВ(A2;4);ПСТР(A2;5;2);ПРАВСИМВ(A2;2))

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

Получение значения даты

С помощью формулы ЗНАЧ мы выводим текстовое значение даты, потом его форматируем как Дату:

Синтаксис: =ЗНАЧЕН(A7)

Умножение текстового значения на единицу

Принцип аналогичный предыдущему, только мы текстовое значение умножаем на единицу и после этого форматируем как дату:

Примечание: если дата определилась как текст, то вы не сможете делать группировки. При этом дата будет выровнена по левому краю. Excel выравнивает числа и даты по правому краю.

Синтаксис

=DATE(year, month, day) – английская версия

=ДАТА(год; месяц; день) – русская версия

Аргументы

• Year (Год) – значение года, которое важно отобразить в дате;
• Month (Месяц) – значение месяца, которое важно отобразить в дате;
• Day (День) – значение дня, которое важно отобразить в дате.

Вставка текущей даты и времени.

В Microsoft Excel вы можете сделать это в виде статического или динамического значения.

Как вставить сегодняшнюю дату как статическую отметку.

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

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

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

Скажу прямо, не все бывает гладко с этими быстрыми клавишами. Но по моим наблюдениям, если при загрузке файла у вас на клавиатуре был включен английский, то срабатывают комбинации клавиш на английском – какой бы язык бы потом не переключили для работы. То же самое – с русским.

Как сделать, чтобы дата оставалась актуальной?

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

• =СЕГОДНЯ()- вставляет сегодняшнюю дату.
• =ТДАТА()- использует текущие дату и время.

В отличие от нажатия специальных клавиш, функции ТДАТА и СЕГОДНЯ всегда возвращают актуальные данные.

А если нужно вставить текущее время?

Здесь рекомендации зависят от того, что вы далее собираетесь с этим делать. Если нужно просто показать время в таблице, то достаточно функции ТДАТА() и затем установить для этой ячейки формат «Время».

Если же далее на основе этого вы планируете производить какие-то вычисления, то тогда, возможно, вам будет лучше использовать формулу

=ТДАТА()-СЕГОДНЯ()

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

При использовании формул имейте в виду, что:

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

Как поставить неизменную отметку времени автоматически формулами?

Допустим, у вас есть список товаров в столбце A, и, как только один из них будет отправлен заказчику, вы вводите «Да» в колонке «Доставка», то есть в столбце B. Как только «Да» появится там, вы хотите автоматически зафиксировать в колонке С время, когда это произошло. И менять его уже не нужно.

Для этого мы попробуем использовать вложенную функцию ИЛИ с циклическими ссылками во второй ее части:

=ЕСЛИ(B2=”Да”; ЕСЛИ(C2=””;ТДАТА(); C2); “”)

Где B – это колонка подтверждения доставки, а C2 – это ячейка, в которую вы вводите формулу и где в конечном итоге появится статичная отметка времени.

В приведенной выше формуле первая функция ЕСЛИ проверяет B2 на наличие слова «Да» (или любого другого текста, который вы решите ввести). И если указанный текст присутствует, она запускает вторую функцию ЕСЛИ. В противном случае возвращает пустое значение. Вторая ЕСЛИ – это циклическая формула, которая заставляет функцию ТДАТА() возвращать сегодняшний день и время, только если в C2 еще ничего не записано. А если там уже что-то есть, то ничего не изменится, сохранив таким образом все существующие метки.

О работе с функцией ЕСЛИ читайте более подробно здесь.

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

=ЕСЛИ(B2<>””; ЕСЛИ(C2=””;ТДАТА(); C2); “”)

Примечание. Чтобы эта формула работала, вы должны разрешить циклические вычисления на своем рабочем листе (вкладка Файл – параметры – Формулы – Включить интерактивные вычисления). Также имейте в виду, что в основном не рекомендуется делать так, чтобы ячейка ссылалась сама на себя, то есть создавать циклические ссылки. И если вы решите использовать это решение в своих таблицах, то это на ваш страх и риск.

Складывать и вычитать календарные дни

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

Например, вам необходимо создать резерв по сомнительным долгам в налоговом учете. В том числе нужно просчитать, когда у покупателя возникнет задолженность со сроком 45 дней после дня реализации. Для этого в одну ячейку внесите дату отгрузки. К примеру, это ячейка D2. Тогда формула будет выглядеть так: =D2+45. Вычитаются дни по аналогичному принципу. Главное, чтобы ячейка с датой, к которой будете прибавлять число, имела правильный формат. Чтобы это проверить, нажмите правой кнопкой мыши на ячейку, выберите «Формат ячеек» и удостоверьтесь, что установлен формат «Дата».

Как выглядит формат ячейки в Excel

Таким же образом можно посчитать и количество дней между двумя датами. Просто вычтите из более поздней даты более раннюю. Результат Excel покажет в виде числа, поэтому ячейку с итогом переведите в общий формат: вместо «Дата» выберите «Общий».

К примеру, необходимо посчитать, сколько календарных дней пройдет с 05.11.2019 по 31.12.2019. Для этого введите эти даты в разные ячейки, а в отдельной ячейке поставьте знак «=». Затем вычтите из декабрьской даты ноябрьскую. Получится 56 дней. Помните, что в этом случае в подсчет войдет последний день, но не войдет первый. Если вам необходимо, чтобы итог включал оба дня, прибавьте к формуле единицу. Если же, наоборот, нужно посчитать количество дней без учета обеих дат, то единицу необходимо вычесть.

Добавить к дате рабочие дни

Функция РАБДЕНЬ позволяет точно посчитать дату через нужное количество рабочих дней. Эта функция состоит из трех элементов:

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

Например, директор дал вам поручение, которое необходимо выполнить за 25 рабочих дней. Допустим, сегодня вторник, 5 ноября 2019 года. Эту дату вносим в ячейку A1. Функция =РАБДЕНЬ(A1;25) определит крайний день, когда вы должны его выполнить, — 10 декабря 2019 года. При этом не забудьте поставить в ячейке с результатом формат «Дата».

Помните, что функция РАБДЕНЬ автоматически убирает из подсчетов только субботы и воскресенья. О праздниках Excel не знает. Их нужно заносить в функцию вручную. Чтобы вы не запутались, мы подготовили файл, в который уже внесли все праздники 2020 года. Ищите его в электронной версии статьи.

Как прибавить (вычесть) несколько недель к дате

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

• Прибавляем N недель к дате в Excel:

  = A2 + N недель * 7

  Например, чтобы прибавить 3 недели к дате в ячейке А2, используйте следующую формулу:

  =A2+3*7

• Вычитаем N недель из даты в Excel:

  = А2 - N недель * 7

  Чтобы вычесть 2 недели из сегодняшней даты, используйте эту формулу:

  =СЕГОДНЯ()-2*7
=TODAY()-2*7

Добавление лет к датам в Excel осуществляется так же, как добавление месяцев. Вам необходимо снова использовать функцию ДАТА (DATE), но на этот раз нужно указать количество лет, которые Вы хотите добавить:

= ДАТА(ГОД(дата) + N летдатадата))
= DATE(YEAR(дата) + N лет, MONTH(дата), DAY(дата))

На листе Excel, формулы могут выглядеть следующим образом:

• Прибавляем 5 лет к дате, указанной в ячейке A2:

  =ДАТА(ГОД(A2)+5;МЕСЯЦ(A2);ДЕНЬ(A2))
=DATE(YEAR(A2)+5,MONTH(A2),DAY(A2))

• Вычитаем 5 лет из даты, указанной в ячейке A2:

  =ДАТА(ГОД(A2)-5;МЕСЯЦ(A2);ДЕНЬ(A2))
=DATE(YEAR(A2)-5,MONTH(A2),DAY(A2))

Чтобы получить универсальную формулу, Вы можете ввести количество лет в ячейку, а затем в формуле обратиться к этой ячейке. Положительное число позволит прибавить годы к дате, а отрицательное – вычесть.

Как отличить обычные даты Excel от «текстовых дат»

Импортированные данные (или данные, введенные неправильно) могут выглядеть как обычные даты Excel, но они не ведут себя так, как выглядят. Microsoft Excel обрабатывает такие записи как текст. Поэтому вы не сможете правильно отсортировать таблицу в хронологическом порядке, а также использовать эти «неправильные даты» в формулах, сводных таблицах, диаграммах или любом другом инструменте Excel, который работает с временем.

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

Их можно легко распознать, немного расширив столбцы, выделив один из них, вы­брав команду Формат ► Ячейки ► Выравнивание (Format ► Cells ► Alignment) и для па­раметра По горизонтали (Horizontal) выбрав значение Общий (General) (это вид ячеек по умолчанию). Щелкните кнопку ОК и внимательно просмотрите на таблицу. Если какие-либо значения не выровнены по правому краю, значит Excel не считает их датами.

Как конвертировать текст в дату в Excel

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

Математические операции для преобразования текста в дату

Помимо использования функций Excel, о которых мы говорили чуть выше, вы можете выполнить простую математическую операцию, чтобы заставить программу выполнить реорганизацию строки в дату. Обязательное условие: операция не должна изменять ее значение (порядковый номер дня). Звучит немного сложно? Следующие примеры помогут разобраться!

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

• Сложение: =A1 + 0
• Умножение: =A1 * 1
• Деление: =A1 / 1
• Двойное отрицание: =–A1

Как вы можете убедиться, математические операции могут помочь с датами (строки 3,4.,5,7), временем (строки 2 и 6), а также числами, отформатированными как текст (строка 8).

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

Как превратить текстовые строки с пользовательскими разделителями в даты

Если запись содержит какой-либо разделитель, отличный от косой черты (/) или тире (-), функции Excel не смогут распознать их как даты и вернут ошибку #ЗНАЧ!. Чаще всего такие «неправильные» разделители – это пробел и запятая.

Чтобы это исправить, вы можете запустить инструмент поиска и замены, чтобы заменить этот неподходящий разделитель, к примеру, косой чертой (/):

• Выберите все ячейки, которые вы хотите превратить в даты.
• Нажмите Ctrl + H, чтобы открыть диалоговое окно «Найти и заменить».
• Введите свой пользовательский разделитель (запятую, к примеру) в поле Найти и косую черту в Заменить.
• Нажмите Заменить все.

Теперь у ДАТАЗНАЧ или ЗНАЧЕН должно быть проблем с конвертацией текстовых строк в даты. Таким же образом вы можете исправить записи, содержащие любой другой разделитель, например, пробел или обратную косую черту.

Если вы предпочитаете решение на основе формул, вы можете использовать функцию ПОДСТАВИТЬ (SUBSTITUTE в английской версии), как это мы делали на одном из скриншотов ранее:

=ДАТАЗНАЧ(ПОДСТАВИТЬ(A12;”,”;”.”))

И текстовые строки преобразуются в даты, все при помощи одной формулы.

Как видите, функции ДАТАЗНАЧ или ЗНАЧЕН довольно мощные, но они, к сожалению, имеют свои ограничения. Например, если вы пытаетесь работать со сложными конструкциями, такими как четверг, 01 января 2015 г., ни одна из них не сможет помочь.

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

Исправление записей с двузначными годами.

Современные версии Microsoft Excel достаточно умны, чтобы обнаружить некоторые очевидные ошибки в ваших данных, или, точнее, сказать, что Эксель считает ошибкой. Когда это произойдет, вы увидите индикатор ошибки (маленький зеленый треугольник) в верхнем левом углу клетки, и, когда вы выделите её, появится восклицательный знак.
При нажатии на восклицательный знак отобразятся несколько параметров, относящихся к вашим данным. В случае двухзначного года программа спросит, хотите ли вы преобразовать его в 19XX или 20XX.

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

Пользовательский формат – это формат отображения значения задаваемый пользователем. Например, дату 13/01/2010 можно отобразить как: 13.01.2010 или 2010_01_13 или 13-Январь-10

.

Пользовательский формат можно применить через

Формат ячеек

или определить в функции

ТЕКСТ()

. В этой статье приведены некоторые примеры пользовательского формата даты и времени (см.

файл примера

).

Форматы Даты (на примере значения 01.02.2010 12:05)

Форматы времени (на примере значения 12:05 дня)

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

Формат ячеек

, вкладка

Число

, (

все форматы

), нажав

CTRL+1

. Сам формат вводите в поле

Тип

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

Числовой пользовательский формат

.

В случае использования функции

ТЕКСТ()

используйте следующий синтаксис:

=ТЕКСТ(СЕГОДНЯ();»здесь укажите требуемый формат»)

. Например,

=ТЕКСТ(СЕГОДНЯ();»дд.ММ.гггг»)

Естественно, вместо функции

СЕГОДНЯ()

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

Как Excel хранит дату и время

.

Еще один пример: число 1300 можно отобразить как Время (13:00) с помощью формата 00:00 (обратный слеш нужен для корректного интерпретирования двоеточия). Результат 13:00. Но EXCEL будет продолжать производить вычисления с 1300 как с обычным числом (меняется только отображения числа 1300). При прибавлении 65 вместо 14:05 получим 13:65. Аналогичная функция с пользовательским форматом:

=ТЕКСТ(1300;» 00:00″)

Этот формат полезен для ускорения ввода, см. статью

Ускорение ввода значений в формате времени

.

Пользовательское форматирование даты

​Смотрите также​​ так, чтобы значения​ и нажмите инструмент​ (‘). Например: ’11-53​ диапазону ячеек с​ Дополнительные сведения читайте​ языке) .​.​ видов форматирования:​ на вкладке​.​ОК​ и чисел​Поскольку в двух системах​ следует быть предельно​ 99​ целое число. Попробуйте​Примечание:​ в строках отображались​ «Главная»-«Число»-«Процентный формат» или​ или ‘1/47. Апостроф​ датами в текстовом​

​ в статье Выбор​В некоторых случаях даты​Выделяем диапазон на листе,​​Общий;​​Главная​Windows 7​.​.​ дат используются разные​ конкретным при вводе​гг​​ дважды щелкнув правый​​Мы стараемся как​ соответственно названиям столбцов:​ комбинацию горячих клавиш​ не отображается после​ формате.​ ячеек, диапазонов, строк​ могут форматироваться и​ который следует отформатировать.​Денежный;​в группе​Нажмите кнопку​​Вводимые в книгу даты​​Щелкните пункт​​ начальные дни, в​​ дат. Это обеспечит​Лет в виде чисел​ край столбца, содержащего​

Выбор из списка форматов даты

​ можно оперативнее обеспечивать​

1. ​В первом столбце форматы​ CTRL+SHIFT+5. Или просто​

2. ​ нажатия клавиши ВВОД;​

   ​Выберите ячейку или диапазон​ или столбцов на​ храниться в ячейках​

3. ​ Располагаясь во вкладке​​Числовой;​​Буфер обмена​​Пуск​​ по умолчанию отображаются​

4. ​Язык и региональные стандарты​​ каждой из них​​ наивысшую точность в​​ от 1900 до​​ ячейки с ;.​

   

5. ​ вас актуальными справочными​​ уже соответствуют его​​ введите 200% вручную​добавить перед дробным числом​ ячеек с последовательными​ листе.​ в виде текста.​«Главная»​​Финансовый;​​нажмите кнопку​

   ​и выберите пункт​​ с использованием двух​.​ одна и та​ вычислении дат.​ 9999​ Это будет изменение​ материалами на вашем​ названию, поэтому переходим​

6. ​ и форматирование для​ ноль и пробел,​ номерами и на​Совет:​ Например, даты могут​​, кликаем по значку​

​Текстовый;​​Копировать​Панель управления​ цифр для обозначения​В диалоговом окне​ же дата представлена​Excel даты хранятся как​гггг​ размера столбца по​ языке. Эта страница​ ко второму и​ ячейки присвоится автоматически.​ например, чтобы числа​ вкладке​ Для отмены выделения ячеек​ быть введены в​«Формат»​Дата;​

Создание пользовательского формата даты

​.​​.​​ года. Когда стандартный​Язык и региональные стандарты​ разными порядковыми номерами.​ последовательных чисел, которые​При изменении формат, который​ размеру число. Можно​ переведена автоматически, поэтому​

1. ​ выделяем диапазон B3:B7.​Кликниет по ячейке A5​

2. ​ 1/2 или 3/4​

   ​Главная​ щелкните любую ячейку​ ячейки, которые имеют​

3. ​, который находится в​​Время;​​Выделите диапазон ячеек, содержащих​​Щелкните элемент​​ формат даты будет​

4. ​нажмите кнопку​​ Например, 5 июля​​ называются последовательных значений.​​ содержит значения времени​​ также перетащить правую​ ее текст может​ Потом жмем CTRL+1​​ и щелкните по​​ не заменялись датами​в группе​ в таблице.​
   

5. ​ текстовый формат, кроме​​ группе инструментов​​Дробный;​​ неправильные данные.​​Язык и региональные стандарты​​ изменен на другой​​Дополнительные параметры​ 2007 г. может​ Например в Excel​ и использовании «m»​ границу столбца, чтобы​ содержать неточности и​ и на закладке​ инструменту «Финансовый числовой​ 2 янв или​Буфер обмена​Нажмите кнопку ошибки, которая​

6. ​ того, даты могут​«Ячейки»​​Процентный;​​Совет:​.​ с помощью описанной​

​ но об этом​ ячейке после нажатия​.​В меню выберите команду​ в текстовом формате​«Формат ячеек…»​ на более мелкие​ в таблице. Дополнительные​нажмите кнопку​

Советы по отображению дат

• ​ отображаться в соответствии​.​ системы дат.​ и 1 января​ кодом «ss», Excel​

• ​Тип​ вам полезна. Просим​ отображения такой как​ далее…​​ клавиши ВВОД, а​​Сочетание клавиш:​Преобразовать XX в 20XX​ из внешних источников​.​ структурные единицы вышеуказанных​ сведения см. в​Дополнительные параметры​ с новым форматом,​В поле​Система дат​ 2008 — номер​ отображает минуты вместо​

• ​нет подходящего формата,​ вас уделить пару​ указано на рисунке:​Сделайте активной ячейку A6​ тип ячейки становится​  Можно также нажать клавиши​или​ данных.​

• ​После этого активируется уже​ вариантов. Например, форматы​ статье Выделение ячеек,​.​ если формат дат​Если год отображен двумя​Порядковый номер 5 июля​​ 39448, так как​​ месяца.​ вы можете создать​

support.office.com

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

​ секунд и сообщить,​​Так же делаем с​ и щелкните на​ дробным.​ CTRL + C.​Преобразовать XX в 19XX​Даты, отформатированные как текст​ хорошо знакомое нам​ даты и времени​ диапазонов строк и​Откройте вкладку​ не был изменен​ цифрами, отображать год​ 2007 г.​ интервал в днях​Чтобы быстро применить формат​ собственный. Для этого​ помогла ли она​ диапазонами C3:C7 и​ угловую кнопку со​Примечания:​Выберите ячейку или диапазон​

​. Если вы хотите​ выравниваются по левому​ окно форматирования. Все​ имеют несколько подвидов​ столбцов на листе.​Дата​ с помощью диалогового​ как​35000​ после 1 января​ даты по умолчанию,​ проще всего взять​ вам, с помощью​ D3:D7, подбирая соответствующие​ стрелкой в низ​ ​ ячеек, которые содержат​ прекратить индикатором ошибки​

​ краю ячейки (а​​ дальнейшие действия точно​ (ДД.ММ.ГГ., ДД.месяц.ГГ, ДД.М,​На вкладке​.​ окна​измените верхний предел​37806​ 1900 г.​ щелкните ячейку с​

Изучение форматов дат и вычислений с ними

​ за основу формат,​ кнопок внизу страницы.​ форматы и типы​ в разделе «Главная»-«Число»​Вместо апострофа можно использовать​ даты в текстовом​ без преобразования число,​ не по правому​ такие же, как​ Ч.ММ PM, ЧЧ.ММ​Главная​В списке​Формат ячеек​

​ для года, относящегося​1904​Microsoft Excel сохраняет время​ датой и нажмите​ который ближе всего​ Для удобства также​ отображения.​ или нажмите комбинацию​ пробел, но если​ формате, и на​ нажмите кнопку​ краю). Если включена​

​ уже было описано​ и др.).​в группе​Краткий формат​(на вкладке​ к данному столетию.​39268​ как десятичные дроби,​ сочетание клавиш CTRL+SHIFT+#.​ к нужному формату.​ приводим ссылку на​Если ячейка содержит значение​ клавиш CTRL+1. В​ вы планируете применять​ вкладке​

Получение сведений о двух системах дат

​Пропустить ошибку​ Проверка ошибок ,​ выше.​Изменить форматирование ячеек в​Буфер обмена​выберите формат, который​Главная​Если изменить верхний предел​Разница между двумя системами​ поскольку времени будет​Если после применения к​

​Выделите ячейки, которые нужно​ оригинал (на английском​ больше чем 0,​ появившемся окне на​ функции поиска для​Главная​.​ дат в текстовом​И наконец, окно форматирования​ Excel можно сразу​нажмите кнопку​ использует четыре цифры​в группе​ для года, нижний​ дат составляет 1462​ считаться как часть​ ячейке формата даты​ отформатировать.​ языке) .​ но меньше чем​ закладке «Число» выберите​ этих данных, мы​в группе​Преобразование дат в текстовом​ формате с двузначным​

​ диапазона можно вызвать​ несколькими способами. О​Вставить​ для отображения года​Число​ предел изменится автоматически.​

​и выберите команду​ («yyyy»).​щелкните​Нажмите кнопку​ что порядковый номер​ — значение в​ символы​На компьютере Mac нажмите​ ячейке такие как​ даты в третьем​ форматы:» опцию «Дата»,​ Такие функции, как​нажмите кнопку со​

​ОК​ даты в системе​ диапазоне от 0​;##​ клавиши CONTROL+1 или​ «​ столбце будет отображаться​ а в разделе​ ПОИСКПОЗ и ВПР,​ стрелкой​ стандартные даты с​ индикатором ошибки:​ Для этого нужно​Самый популярный способ изменения​.​ОК​).​.​ 1900 всегда на​ (нуля) до 0,99999999,​, вероятно, ширина ячейки​ COMMAND+1.​

Изменение способа интерпретации года, заданного двумя цифрами

​2/2″​​ как: 0 января​ «Тип:» укажите соответствующий​ не учитывают апострофы​Вставить​ четырьмя цифрами.​.​ предварительно выделить изменяемую​ форматов диапазона данных​В диалоговом окне​.​

​Windows 10​Windows 8​ 1462 дня больше​ представляющая время от​ недостаточна для отображения​В диалоговом окне​, Excel предполагается, что​ 1900 года. В​ способ отображения дат.​ при вычислении результатов.​

• ​и выберите команду​После преобразования ячеек с​​Поскольку ошибок в Excel​ область на листе,​ – это использование​Специальная вставка​​Дата изменения в системе​​В поле поиска на​Проведите пальцем по экрану​ порядкового номера этой​

• ​ 0:00:00 (12:00:00 A.M.)​ числа полностью. Дважды​​Формат ячеек​ это является датой​ то время как​ В разделе «Образец:»​​Если число в ячейке​​Специальная вставка​ текстовыми значениями можно​ можно определить текст,​

​ а затем набрать​ контекстного меню.​в разделе​ автоматически при открытии​ панели задач введите​

​ справа налево и​

1. ​ же даты в​ до 23:59:59 (11:59:59​ щелкните правую границу​​откройте вкладку​​ и форматы согласно​​ в четвертом столбце​​ отображается предварительный просмотр​

2. ​ выровнено по левому​​.​​ изменить внешний вид​​ отформатированный дат с​ на клавиатуре комбинацию​​Выделяем ячейки, которые нужно​

3. ​Вставить​​ документа из другой​​ запрос​

4. ​ коснитесь элемента​​ системе 1904. И​​ вечера).​​ столбца, содержащего ячейки​​Число​

5. ​ даты по умолчанию​​ дата уже отображается​​ отображения содержимого ячейки.​

6. ​ краю, обычно это​​В диалоговом окне​ дат путем применения​ двумя цифрами, можно​​Ctrl+1​ соответствующим образом отформатировать.​установите переключатель​

   ​ платформы. Например если​панель управления​Поиск​

7. ​ наоборот, порядковый номер​​Поскольку даты и значения​​ с символами ;##.​

​.​

1. ​ на панели управления.​ иначе благодаря другому​Переходим в ячейку A7​​ означает, что оно​​Специальная вставка​ формата даты.​ использовать параметры автоматического​. После этого, откроется​ Выполняем клик правой​значения​ вы работаете в​​и выберите элемент​​. (Если вы используете​ даты в системе​​ времени представляются числами,​​ Размер столбца изменится​​В списке​​ Excel может отформатировать​

2. ​ типу (системы даты​​ так же жмем​​ не отформатировано как​​в разделе​Чтобы преобразовать дату в​​ исправления преобразуемый Дата​

3. ​ стандартное окно форматирования.​​ кнопкой мыши. Вследствие​​, а в группе​​ Excel и открытие​​Панель управления​

4. ​ мышь, наведите ее​​ 1904 всегда на​​ их можно складывать​

5. ​ в соответствии с​​Категория​ его как «​ 1904 года, подробнее​​ комбинацию клавиш CTRL+1​ число.​Вставить​

   ​ текстовом формате в​ в формате даты.​ Изменяем характеристики так​

6. ​ этого открывается контекстный​​Операция​​ документа, созданного в​

​.​

1. ​ указатель на правый​​ 1462 дня меньше​​ и вычитать, а​​ длиной чисел. Вы​​выберите пункт​

2. ​2 февраля»​​ смотрите ниже). А​​ для вызова диалогового​

3. ​При введении в ячейку​​выберите параметр​​ ячейке в числовом​​ Функция ДАТАЗНАЧ преобразования​​ же, как об​

4. ​ список действий. Нужно​​выполните одно из​​ Excel для Mac,​

5. ​В разделе​​ верхний угол экрана,​ порядкового номера этой​ также использовать в​​ можете также перетащить​Дата​. Если вы измените​

   ​ если число​ окна «Формат ячеек»,​ числа с буквой​

6. ​Значения​​ формате, с помощью​​ большинство других типов​

Изменение стандартного формата даты для отображения года четырьмя цифрами

​ этом было уже​ остановить выбор на​ указанных ниже действий.​система дат 1904​Часы, язык и регион​ переместите его вниз,​ же даты в​ других вычислениях. При​ правую границу столбца​и выберите нужный​ параметры даты на​Обратите внимание, как отображается​ только в этот​ «е», например 1e9,​и нажмите кнопку​ функции ДАТАЗНАЧ. Скопируйте​​ дат в текстовом​​ сказано выше.​​ пункте​​Чтобы получить даты на​​установлен флажок автоматически.​​щелкните элемент​​ а затем щелкните​​ системе 1900. 1462​

​ использовании основного формата​

1. ​ до необходимой ширины.​ формат даты в​ панели управления, соответственно​​ время в ячейках,​​ раз выбираем опцию​​ оно автоматически преобразуется​​ОК​

2. ​ формулу и выделение​​ формате в формат​​Кроме того, отдельные комбинации​​«Формат ячеек…»​ четыре года и​​Систему дат можно изменить​

3. ​Изменение форматов даты, времени​​ элемент​​ дня — это​

4. ​ для ячеек, содержащих​​Чтобы быстро ввести текущую​​ поле​​ изменится формат даты​​ которые содержат дробные​

5. ​ «Время».​​ в научное число:​​.​

6. ​ ячеек, содержащих даты​​ даты.​​ горячих клавиш позволяют​.​ один день позже,​ следующим образом:​

7. ​ и чисел​​Поиск​​ четыре года и​

​ дату и время,​

1. ​ дату, выберите любую​Тип​ по умолчанию в​​ числа.​​Какие возможности предоставляет диалоговое​ 1,00E+09. Чтобы избежать​На вкладке​ в текстовом формате​При импорте данных в​ менять формат ячеек​Активируется окно форматирования. Выполняем​​ установите переключатель​​Выберите пункты​.​​.) В поле поиска​​ один день (сюда​​ можно отобразить дату​​ пустую ячейку на​

2. ​. Вы можете настроить​​ Excel. Если вам​​Так как даты и​​ окно формат ячеек?​ этого, введите перед​​Главная​

3. ​ с помощью​​ Microsoft Excel из​​ после выделения диапазона​​ переход во вкладку​​сложить​

4. ​Файл​​Щелкните пункт​​ введите​

5. ​ входит один день​​ в виде числа​​ листе, нажмите сочетание​ этот формат на​ не нравится стандартный​ время в Excel​

6. ​ Функции всех инструментов​​ числом апостроф: ‘1e9​​щелкните средство запуска​

​Специальной вставки​

1. ​ другого источника или​​ даже без вызова​​«Число»​​.​​ >​

2. ​Язык и региональные стандарты​​Панель управления​​ високосного года).​

3. ​ или время в​​ клавиш CTRL+; (точка​​ последнем этапе.​​ формат даты, можно​​ являются числами, значит​

4. ​ для форматирования, которые​​Изменение формата ячеек в​​ всплывающего окна рядом​

5. ​применить формат даты.​​ введите даты с​​ специального окна:​, если окно было​Чтобы получить даты на​Параметры​

6. ​.​​и коснитесь элемента​​Важно:​

Изменение системы дат в Excel

​ виде дробной части​ с запятой), а​Вернитесь в списке​ выбрать другой формат​ с ними легко​ содержит закладка «Главная»​ Excel позволяет организовать​ с надписью​​Выполните следующие действия.​​ двузначным по ячейкам,​

​Ctrl+Shift+-​ открыто в другом​

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

2. ​Числовые форматы​​ даты в Excel,​​ выполнять математические операции,​ можно найти в​ данные на листе​​число​​Выделите пустую ячейку и​

Проблема: между книгами, использующими разные системы дат, возникают конфликты

​ которые были ранее​— общий формат;​ месте. Именно в​ один день раньше,​Дополнительно​Язык и региональные стандарты​(или щелкните его).​ значений года, вводите​ точкой.​ нажмите клавишу ВВОД.​и выберите пункт​ такие как «​ но это рассмотрим​ этом диалоговом окне​ в логическую и​.​

​ убедитесь, что его​ отформатированные как текст,​Ctrl+Shift+1​ блоке параметров​ установите переключатель​.​нажмите кнопку​В разделе​ все четыре обозначающих​Excel для Mac и​Чтобы ввести дату, которая​форматы​2 февраля 2012 г.»​ на следующих уроках.​ (CTRL+1) и даже​ последовательную цепочку для​В поле​ числовой формат Общие.​ может появиться маленький​— числа с​«Числовые форматы»​вычесть​В разделе​Дополнительные параметры​Часы, язык и регион​

​ его цифры (например,​​ Excel для Windows​

1. ​ будет обновляться до​. В поле​​или «​​В Excel существует две​

2. ​ больше.​ профессиональной работы. С​​Числовые форматы​​Проверка числового формата:​​ зеленый треугольник в​​ разделителем;​​находятся все те​​.​

3. ​При пересчете этой книги​.​

   ​щелкните элемент​​ 2001, а не​ поддерживает системы дат​ текущей даты при​Тип​2/2/12″​ системы отображения дат:​В ячейке A5 мы​

4. ​ другой стороны неправильное​​выберите пункт​​на вкладке​​ левом верхнем углу​​Ctrl+Shift+2​​ варианты изменения характеристик,​​Устранение проблем с внешними​​выберите нужную книгу,​​Откройте вкладку​

   

5. ​Изменение форматов даты, времени​​ 01). При вводе​​ 1900 и 1904.​​ каждом открытии листа​​вы увидите код​​. Можно также создать​​Дата 1 января 1900​​ воспользовались финансовым форматом,​​ форматирование может привести​Дата​

   • ​Главная​ ячейки. Индикатор этой​— времени (часы.минуты);​ о которых шел​​ ссылками​​ а затем установите​

   • ​Дата​ и чисел​ четырех цифр, обозначающих​ Система дат по​​ или перерасчете формулы,​​ формата формат даты,​

​ собственный пользовательский формат​ года соответствует числу​​ но есть еще​

​ к серьезным ошибкам.​, после чего укажите​в группе​ ошибки вы узнаете,​Ctrl+Shift+3​ разговор выше. Выбираем​   ​ или снимите флажок​

• ​.​.​ год, приложению Excel​ умолчанию для Excel​ введите в пустую​

​ который вы выбрали​

• ​ в Microsoft Excel.​ 1.​ и денежный, их​Содержимое ячейки это одно,​ необходимый формат даты​число​

​ что даты хранятся​

support.office.com

Изменение формата ячеек в Excel

​— даты (ДД.ММ.ГГ);​ пункт, который соответствует​В случае использования внешней​Использовать систему дат 1904​В списке​В диалоговом окне​ не потребуется определять​ для Windows —​ ячейку​ на предыдущем шаге.​Сделайте следующее:​Дата 1 января 1904​ очень часто путают.​ а способ отображения​ в списке​щелкните стрелку рядом​ как текст, как​Ctrl+Shift+4​ данным, находящимся в​ ссылки на дату​

​.​Краткий формат​

​Язык и региональные стандарты​​ столетие.​ 1900; и для​

Основные виды форматирования и их изменение

​=СЕГОДНЯ()​ Не удается изменить​Выделите ячейки, которые нужно​ года соответствует числу​ Эти два формата​

• ​ содержимого ячеек на​
• ​Тип​
• ​ с полем​
• ​ показано в следующем​
• ​– денежный;​
• ​ выбранном диапазоне. При​
• ​ в другой книге​
• ​При копировании и вставке​
• ​выберите формат, который​
• ​нажмите кнопку​

​При вводе даты с​ Excel для Mac​и нажмите клавишу​ формат даты, поэтому​ отформатировать.​ 0, а 1​ отличаются способом отображения:​ мониторе или печати​.​

​Числовой формат​ примере.​Ctrl+Shift+5​ необходимости в правой​ с другой системой​

Способ 1: контекстное меню

​ дат или при​ использует четыре цифры​Дополнительные параметры​ двузначным обозначением года​

1. ​ стандартной системы дат​ ВВОД.​ не обращайте влияя.​Нажмите сочетание клавиш CTRL+1.​ это уже 02.01.1904​финансовый формат при отображении​ это другое. Перед​Чтобы удалить порядковые номера​​и выберите пункт​​Для преобразования текстовых значений​

   

2. ​– процентный;​ части окна определяем​​ дат эту ссылку​​ создании внешних ссылок​ для отображения года​.​ в ячейку с​​ 1904.​​Примечание:​ Внесенные вами изменения​На компьютере Mac нажмите​ соответственно.​ чисел меньше чем​ тем как изменить​ после успешного преобразования​Общие​ в формат дат​Ctrl+Shift+6​ подвид данных. Жмем​​ можно изменить с​​ между книгами с​

​ («yyyy»).​Перейдите на вкладку вкладку​

Способ 2: блок инструментов «Число» на ленте

​ текстовым форматом или​Изначально Excel для Windows​Мы стараемся как​ будут применяются только​ клавиши CONTROL+1 или​

1. ​Примечание. Чтобы все даты​​ 0 ставит минус​​ формат данных в​ всех дат, выделите​.​ можно использовать индикатор​​— формат О.ООЕ+00.​​ на кнопку​ помощью одного из​

   

2. ​ учетом две разные​Нажмите кнопку​Дата​ в качестве текстового​

   

3. ​ на основе был​ можно оперативнее обеспечивать​ к пользовательский формат,​ COMMAND+1.​ по умолчанию отображались​ с левой стороны​​ ячейке Excel следует​​ содержащие их ячейки​

   

4. ​В пустой ячейке сделайте​ ошибок.​Урок:​«OK»​ указанных ниже действий.​ системы дат могут​ОК​.​

Способ 3: блок инструментов «Ячейки»

​ аргумента функции, например,​ создан системы дат​ вас актуальными справочными​ который вы создаете.​​В диалоговом окне​​ по системе 1904​

1. ​ ячейки, а денежный​ запомнить простое правило:​ и нажмите клавишу​​ следующее.​​Примечания:​​Горячие клавиши в Экселе​​.​Чтобы задать дату как​​ возникать проблемы. Даты​​.​В поле​​ при вводе =ГОД(«1.1.31»)​​ 1900, так как​

   

2. ​ материалами на вашем​Внесите нужные изменения в​Формат ячеек​ года можно в​ ставит минус перед​ «Все, что содержит​ DELETE.​

Способ 4: горячие клавиши

​Введите​ Во-первых убедитесь, что ошибок​Как видим, существует сразу​После этих действий формат​ четыре года и​ может появиться четыре​Windows 8​Если год отображен двумя​ в приложении Excel​​ он включен улучшение​​ языке. Эта страница​ поле​откройте вкладку​ параметрах внести соответствующие​ числом;​ ячейка, может быть​

​Чтобы даты было проще​= ДАТАЗНАЧ (​ включен в Microsoft​ несколько способов отформатировать​ ячеек изменен.​ один день позже,​

• ​ года и один​​Проведите пальцем по экрану​
• ​ цифрами, отображать год​​ год будет интерпретироваться​ совместимости с другими​
• ​ переведена автоматически, поэтому​​Тип​
• ​Число​​ настройки: «Файл»-«Параметры»-«Дополнительно»-«При пересчете​
• ​денежный формат по умолчанию​​ по-разному представлено, а​
• ​ вводить, Excel Online​​Щелкните ячейку, содержащую дату​
• ​ Excel. Это можно​​ области листа Excel.​

​Форматирование также можно изменить,​​ добавьте к нему​

​ день позже, чем​ справа налево и​ как​ следующим образом:​ программами электронных таблиц,​ ее текст может​с помощью кода​.​ этой книги:»-«Использовать систему​ отображает отрицательные значения​ презентация отображения данных​ автоматически преобразует 2.12​ в текстовом формате,​ сделать:​ Эту процедуру можно​ используя инструменты, находящиеся​ 1,462. Например:​ ожидаемое или более​ коснитесь элемента​

​измените верхний предел​

lumpics.ru

Преобразование дат из текстового формата в формат даты

​Значения от 00 до​​ разработанные для запуска​ содержать неточности и​ из таблицы, приведенной​В списке​ дат 1904».​ красным цветом шрифта​ зависит от форматирования».​ в 2 дек.​ которую следует преобразовать.​Выберите пункты​ совершить, воспользовавшись инструментами​ на ленте. Этот​=[Book2]Sheet1!$A$1+1462​ ранней версии. Эти​Поиск​ для года, относящегося​ 29​ в разделе MS-DOS​ грамматические ошибки. Для​ ниже.​категории​

​Наглядно приводим пример отличия​ (например, введите в​ Это легко понять,​ Но это может​Введите​Файл​ на ленте, вызовом​ способ выполняется даже​Чтобы задать дату как​ проблемы могут возникнуть​. (Если вы используете​ к данному столетию.​     интерпретируются как годы​ и Microsoft Windows​

​ нас важно, чтобы​Для отображения​выберите​ отображения дат в​ ячейку значение -2р.​ если показать на​ сильно раздражать, если​)​ >​ окна форматирования или​ быстрее предыдущего.​​ четыре года и​

​ при использовании Excel​ мышь, наведите ее​Если изменить верхний предел​ с 2000 по​ и поэтому она​ эта статья была​Используйте код​дату​ этих двух системах​ и денежный формат​ примере. Посмотрите, как​ вы хотите ввести​

Преобразование текстовых дат с двузначным годом с помощью проверки ошибок

​Нажмите клавишу ВВОД, а​Параметры​ горячими клавишами. Каждый​Переходим во вкладку​ один день более​ для Windows и​ указатель на правый​ для года, нижний​ 2029. Например, если​ стала стандартной системы​ вам полезна. Просим​Месяцев в виде чисел​.​ на рисунке:​ присвоится автоматически);​ с помощью форматирования​

​ числа, которое не​ функция ДАТАЗНАЧ возвращает​ >​ пользователь сам решает,​

​«Главная»​​ ранней версии, вычитание​ Excel для Mac.​ верхний угол экрана,​ предел изменится автоматически.​

1. ​ вводится дата​​ дат. Изначально Excel​​ вас уделить пару​​ от 1 до​​В группе​​В справке Excel указаны​​в финансовом формате после​

   ​ можно отображать число​​ нужно превращать в​ ​ порядковый номер даты,​Формулы​​ какой вариант для​​. При этом, нужно​​ 1,462 от него.​​Например, если скопировать 5​

2. ​ переместите его вниз,​​Нажмите кнопку​​28.05.19​​ для Mac на​ секунд и сообщить,​ 12​Тип​

3. ​ минимальные и максимальные​​ сокращения валют добавляется​​ 2 разными способами:​​ даты. Хотя преобразование​ которая была представлена​​.​

​ него является наиболее​ выделить соответствующие ячейки​ Например:​ июля 2007 даты​

1. ​ а затем щелкните​ОК​, она распознается как​ основе был создан​ помогла ли она​м​выберите формат даты.​ числа для дат​ 1 пробел при​Большинство пользователей Excel пользуются​

   ​ невозможно отключить его,​​ в текстовом формате.​В Excel 2007, нажмите​ удобным в решении​

2. ​ на листе, а​=[Book1]Sheet1!$A$1-1462​ из книги, использующей​

   

3. ​ элемент​​.​​ 28 мая 2019​​ система дат 1904,​​ вам, с помощью​Месяцев в виде чисел​ Предварительный просмотр вашего​ обоих систем.​​ отображении значений.​​ исключительно стандартными инструментами​

   

   ​ есть несколько способов,​Что такое числовом Excel?​кнопку Microsoft Office​ конкретно поставленных задач,​ в блоке настроек​

   

   ​Формат ячейки в программе​ системы дат 1900​Поиск​Windows 7​ г.​

Преобразование дат в текстовом формате с помощью функции ДАТАЗНАЧ

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

​«Число»​

1. ​ Эксель задает не​ и вставьте дату​.) В поле поиска​
   ​Нажмите кнопку​​Значения от 30 до​​ включен улучшение совместимости​​ Для удобства также​​ 12​​ первой датой в​Первая дата​​ клавиш: CTRL+SHIFT+4, то​​кнопками на панели «Главная»;​​Если вам нужно сложить​​ целые числа, чтобы​

2. ​ выберите пункт​ случаях достаточно применения​

   1. ​на ленте открыть​​ просто внешний вид​

   2. ​ в книгу, использующую​ введите​Пуск​

   3. ​ 99​​ с ранних компьютеров​

   4. ​ приводим ссылку на​мм​ данных будет доступен​Последняя дата​ ячейке присвоится денежный​

      ​готовые шаблоны форматов ячеек​

      ​ много значений, которые​ их можно использовать​Параметры Excel​ общих форматов, а​ поле выбора.​ отображения данных, но​ система дат 1904​Панель управления​и выберите пункт​     интерпретируются как годы​ Macintosh, которые не​ оригинал (на английском​Месяцев в виде «янв»,​ в поле​1900​ формат.​ доступных в диалоговом​ похожи на даты​ в вычислениях. По​>​​ в других —​Просто производим выбор нужного​ и указывает самой​ Дата отображается в​и коснитесь элемента​

3. ​Панель управления​ с 1930 по​ поддерживает даты до​ языке) .​ …, «дек»​Образец​

4. ​1 января 1900 г.​Что касается даты в​ окне открыто с​ (например, чисел с​​ умолчанию 1 января​​формулы​​ требуется точное указание​​ варианта. Диапазон сразу​​ программе, как именно​​ виде 6 июля​

   ​Панель управления​​.​ 1999. Например, если​

5. ​ 2 января 1904​При анализе данных даты​ммм​.​ (значение 1)​​ ячейке A6, то​​ помощью комбинацией горячих​​ дробями), вы можете​​ 1900 г —​.​​ характеристик по подвидам.​​ же после этого​​ их следует обрабатывать:​​ 2011 г., являющееся​

6. ​(или щелкните его).​​Щелкните элемент​​ вводится дата​​ и поэтому она​​ могут играть очень​​Месяцев в виде «январь»,​​Примечание:​​31 декабря 9999 г.​​ здесь стоит упомянуть​

7. ​ клавиш CTRL+1.​​ отформатировать ячейки как​​ порядковый номер 1,​Проверка ошибок​Автор: Максим Тютюшев​​ изменит своё форматирование.​​ как текст, как​

8. ​ 1,462 дней позже.​​В разделе​​Язык и региональные стандарты​​28.05.98​​ стала стандартной системы​ важную роль. Например,​ …, «декабрь»​​  Форматы даты, начинающиеся​​ (значение 2958465)​

9. ​ о правилах Excel.​Диапазон ячеек A2:A7 заполните​ текст. После этого​ а 1 января​установите флажок​Примечание:​

support.office.com

Отмена автоматической замены чисел датами

​Но в указанном списке​ числа, как дату​ Кроме того, если​Часы, язык и регион​.​, она распознается как​ дат.​ часто возникают вопросы:​мммм​ со звездочки (*),​1904​ Формат даты считаются​ цифрой 2 и​

​ Excel Online не​ 2008 г., —​Включить фоновый поиск ошибок​Мы стараемся как​ представлены только основные​ и т.д. Поэтому​ скопируйте 5 июля​щелкните элемент​В диалоговом окне​ 28 мая 1998​

1. ​В следующей таблице представлены​ когда был приобретен​

2. ​Месяцев в виде первой​​ будут изменены при​​2 января 1904 г.​​ как последовательность дней​​ отформатируйте все ячейки​​ будет пытаться преобразовать​​ номер 39448, так​

​. Любая ошибка, обнаружении,​ можно оперативнее обеспечивать​ форматы. Если вы​ очень важно правильно​ 2007 даты из​Изменение форматов даты, времени​Язык и региональные стандарты​

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

• ​ (значение 1)​ от 1 января​ так как показано​ их в даты.​ как интервал в​ будут помечены треугольник​ вас актуальными справочными​ хотите более точно​ установить данную характеристику​ книги, которая используется​ и чисел​нажмите кнопку​

  ​В Microsoft Windows можно​​ даты для каждой​

  • ​ будет выполняться задача​ммммм​ даты и времени​31 декабря 9999 г.​ 1900 года. То​ выше на рисунке.​Выберите ячейки, в которые​ днях после 1​ в левом верхнем​ материалами на вашем​

  • ​ указать форматирование, то​ диапазона, в который​ система дат 1904​.​Дополнительные параметры​ изменить способ интерпретации​

  • ​ системы, а также​ в проекте или​Дней в виде чисел​ на панели управления.​ (значение 2957003)​ есть если ячейка​Решение задачи:​ необходимо ввести числа.​

support.office.com

Как изменить формат ячейки в Excel быстро и качественно

​ января 1900.To скопировать​ углу ячейки.​ языке. Эта страница​ следует выбрать пункт​ будут вноситься данные.​ и вставьте дату​В диалоговом окне​.​ двузначным для всех​

​ соответствующие им порядковые​ какая была средняя​ от 1 до​ Форматы без звездочки​Настройки изменения системы дат​ содержит значение –​Выделите диапазон A2:A7 введите​Щелкните​ формулу преобразования в​В разделе​ переведена автоматически, поэтому​«Другие числовые форматы»​ В обратном случае,​ в книгу, использующую​Язык и региональные стандарты​Перейдите на вкладку вкладку​ программ Windows, установленные​ значения.​ выручка в финансовом​ 31​

​ останутся без изменений.​ распространяются не только​ число 2, то​

• ​ число 2 и​
• ​Главная​ диапазон смежных ячеек,​Правила контроля ошибок​ ее текст может​.​

Форматы данных вводимых в ячейки электронной таблицы

​ все вычисления будут​ системы дат 1900​нажмите кнопку​Дата​ ранее.​

​Система дат​

1. ​ квартале. Для получения​д​Если вы хотите применить​ на конкретный лист,​
2. ​ это число в​ нажмите комбинацию клавиш​>​ выделите ячейку, содержащую​установите флажок​ содержать неточности и​После данных действий, откроется​ просто некорректными. Давайте​ Дата отображается в​Дополнительные параметры​
3. ​.​Windows 10​Первая дата​ точных результатов необходимо​Дней в виде чисел​ формат даты согласно​ а на всю​ формате даты должно​
4. ​ CTRL+Enter.​Числовой формат​ формулу, которая была​Ячейки, которые содержат годы,​ грамматические ошибки. Для​ окно форматирования диапазона,​ выясним, как изменить​
5. ​ виде 4 июля​.​В поле​В поле поиска на​Последняя дата​ правильно вводить даты.​ от 01 до​ как другой язык​ программу. Поэтому если​ отображаться как 02.01.1900​В ячейке A2 должен​>​ введена , а​ представленные 2 цифрами​ нас важно, чтобы​ о котором уже​ формат ячеек в​
6. ​ 2003, ранее 1,462​Откройте вкладку​Если год отображен двумя​ панели задач введите​35000​ Но не менее​ 31​ отображает даты, выберите​

​ нет острой необходимости​ и так далее.​ быть формат «Общий​Текст​ затем перетащите маркер​.​ эта статья была​ шёл разговор выше.​ Microsoft Excel.​

​ дней. Фон Подробнее​Дата​ цифрами, отображать год​ запрос​1 января 1900​ важно задать понятный​дд​

• ​ нужный язык в​ их менять, то​Время для Excel –​ он в программе​.​ заполнения​Выполните эту процедуру, чтобы​
• ​ вам полезна. Просим​ Пользователь может выбрать​Скачать последнюю версию​ системы дат в​.​ как​панель управления​
• ​(значение 1)​ формат даты для​Дней в виде «Пн»,​язык (местоположение).​

​ лучше пользоваться системой​ это значение чисел​ Excel задан по​Если вам нужно ввести​

​по диапазону пустых​ преобразовать дату в​ вас уделить пару​ тут любой из​ Excel​ Excel.​В списке​измените верхний предел​и выберите элемент​31 декабря 9999 г​ правильной интерпретации этих​ …, «Вс»​Совет:​ установленной по умолчанию​ после запятой. Так​

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

​Устранение проблемы копирования​

Формат даты со временем в Excel

​Краткий формат​ для года, относящегося​Панель управления​(значение 2958465)​

​ результатов.​ддд​ У вас есть номера​ – 1900 года.​ как у нас​ переходим на ячейку​ можно отменить их​ размерам совпадает диапазон​ обычном даты.​ помогла ли она​ форматов данных.​Форматирование текста в Microsoft​

​   ​выберите формат, который​ к данному столетию.​.​1904​

​Важно:​Дней в виде «понедельник»,​ отображаются в ячейках,​ Это позволит избежать​ в ячейке A7​ A3. Щелкаем по​ преобразование в даты​ ячеек, содержащих даты​Выберите на листе любую​ вам, с помощью​Ещё одним вариантом настройки​ Word​В пустую ячейку введите​ использует четыре цифры​Если изменить верхний предел​В разделе​

​2 января 1904​ Поскольку правила, которые задают​ …, «воскресенье»​ как ;? Весьма​

​ серьезных ошибок при​ целое число, время​ инструменту «Главная»-«Число»-«Увеличить разрядность»​ в Excel Online​ в текстовом формате.​ ячейку или диапазон​ кнопок внизу страницы.​

Две системы отображения дат Excel

​ данной характеристики диапазона​Сразу определим, какие форматы​

1. ​ число​ для отображения года​ для года, нижний​
2. ​Часы, язык и регион​(значение 1)​ способ интерпретации дат​дддд​ вероятно, что ваш​

​ выполнении математических операций​ там отображено соответственно.​ (увеличение количества отображаемых​ одним из таких​В результате получится диапазон​ смежных ячеек с​ Для удобства также​ является использования инструмента​

​ ячеек существуют. Программа​1462​ («yyyy»).​ предел изменится автоматически.​

​щелкните элемент​31 декабря 9999 г​ в разных вычислительных​Лет в виде чисел​

​Изменение форматов даты, времени​(значение 2957003)​ программах, довольно сложны,​ от 00 до​ широк для отображения​ временем.​Отформатируем таблицу с данными​Перейдите на ячейку A4​добавить перед числом апостроф​ номерами, который соответствует​ верхнем левом углу.​ оригинал (на английском​«Ячейки»​ из следующих основных​Выделите эту ячейку и​

exceltable.com

​ОК​

В эксель число в дату

Преобразование дат из текстового формата в формат даты

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

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

Поскольку функция проверки ошибок в Excel распознает даты в текстовом формате с двузначным номером года, можно воспользоваться средством автозамены и преобразовать их в даты в формате даты. С помощью функции ДАТАЗНАЧ можно преобразовывать в даты большинство типов текстовых дат.

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

Вы можете использовать индикатор ошибки для преобразования дат из текстового формата в формат даты.

Примечания: Сначала убедитесь в том, что в Excel включена проверка ошибок. Для этого:

Щелкните Файл > Параметры > Формулы.

В Excel 2007 нажмите кнопку Microsoft Office и выберите Параметры ExcelExcel 2007формулы.

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

В разделе правила проверки ошибоквыделите ячейки, которые содержат годы, представленные 2 цифрами.

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

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

Совет: Чтобы отменить выделение ячеек, щелкните любую ячейку на листе.

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

В меню выберите команду Преобразовать XX в 20XX или Преобразовать XX в 19XX. Если вы хотите отключить индикатор ошибки, не преобразуя число, нажмите кнопку пропустить ошибку.

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

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

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

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

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

Краткий формат даты выглядит следующим образом:

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

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

Выполните указанные ниже действия:

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

В пустой ячейке сделайте следующее.

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

Нажмите клавишу ВВОД, и функция ДАТАЗНАЧ возвращает порядковый номер даты, представленной текстовым форматом даты.

Что такое серийный номер Excel?

В Excel даты хранятся в виде порядковых номеров, что позволяет использовать их в вычислениях. По умолчанию 1 января 1900 г. является порядковым числом 1, а 1 января 2008 — порядковый номер 39448, так как он составляет 39 448 дня после 1 января, 1900.To скопировать формулу преобразования в диапазон смежных ячеек, выделите ячейку, содержащую введенную формулу. , а затем перетащите маркер заполнения по диапазону пустых ячеек, который соответствует размеру диапазона ячеек, содержащих текстовые даты.

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

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

Сочетание клавиш: Кроме того, можно нажать клавиши CTRL + C.

Выделите ячейку или диапазон ячеек, которые содержат даты в текстовом формате, и на вкладке Главная в группе Буфер обмена нажмите стрелку под кнопкой Вставить и выберите команду Специальная вставка.

В диалоговом окне Специальная вставка в разделе Вставить выберите параметр Значения и нажмите кнопку ОК.

На вкладке Главная нажмите кнопку вызова всплывающего окна рядом с полем число.

В поле Категория выберите пункт Дата, после чего укажите необходимый формат даты в списке Тип.

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

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).

Преобразование в Дату в Excel. Бесплатные примеры и статьи.

Функция ЗНАЧЕН() в MS EXCEL

Функция ДАТАЗНАЧ() в MS EXCEL

Единственная задача функции ДАТАЗНАЧ() , английский вариант DATEVALUE(), — преобразовывать даты, которые хранятся в виде текста, в числа, которые соответствуют этим датам. Например, формула ДАТАЗНАЧ(«11.09.2009») возвращает число 40067, соответствующее 11 сентября 2009 года. Но, функция ДАТАЗНАЧ() понимает только определенные форматы записи дат. Например, 2009-сент-11 она не поймет, а 11-сент-2009 — поймет.

Автоматическое преобразование формата ячейки в MS EXCEL при вводе ТЕКСТовых данных (Часть 2)

Продолжаем бороться в MS EXCEL 2007 с автоматическим преобразованием формата ячейки при вводе данных в ячейку. При вводе пользователем данных, EXCEL пытается определить тип вводимых данных. Если данные можно перевести в формат даты, то EXCEL производит соответствующее преобразование и форматирование. Часто текстовые строки действительно имеют формат дат (2-3-8, т.е. 2 марта 2008), но на самом деле ими не являются (например, это м.б. артикул). В этом случае необходимо запретить EXCEL выполнять автоматическое преобразование и форматирование.

Преобразование в MS EXCEL ТЕКСТовых значений в ДАТУ

Бывает, что при экспорте значений в EXCEL, даты записываются в незнакомом для EXCEL формате, например 20081223 (т.е. 2008г, 23 декабря). Для дальнейшей работы с такими датами выполним преобразование в привычный для EXCEL формат даты.

Является ли в MS EXCEL значение ДАТОЙ

Попробуем преобразовать заданное значение в дату. Если это удастся, то будем считать значение датой.

Преобразование ТЕКСТовых значений в ЧИСЛА и ДАТЫ (Часть 2. Групповое изменение в MS EXCEL)

При копировании ЧИСЛОвых данных в EXCEL из других приложений бывает, что Числа сохраняются в ТЕКСТовом формате. Если на листе числовые значения сохранены как текст, то это может привести к ошибкам при выполнении вычислений. В ряде случаев можно быстро изменить текстовый формат на числовой сразу в нескольких ячейках. Произведем это преобразование с помощью Специальной вставки из Буфера обмена.

Эффективная работа в MS Office

Экономия 5 минут в час за счет более продуктивной работы дает за год экономию в 4 рабочие недели

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

Когда вы связываете несколько систем друг с другом возможны всякие…нюансы. Вроде таблицы с числами вида

Само собой при выгрузке не имелся ввиду июнь 2034 года или 4 августа 2016 года. А конкретные числа 6.34 или 4.08, которые Excel по ошибке попытался преобразовать в даты. Тот же самый механизм, что и если вы введете в любой ячейке любого листа, например, текстом «12-15» и нажмете на Enter. Вы думаете, что вводите числовой диапазон, а Excel думает, что это незавершенная дата вида 12-15-2016 и «помогает» вам дописать его.

Исправить при вводе это автоматическое преобразование можно добавив в начало формулы одинарный апостроф — ‘ . В этом случае неважно что вы введете в значение ячейки (в том числе формулу) — Excel будет считать, что дальше текст.

Само преобразование может пойти по трем путям (в т.ч. в зависимости от региональных разделителей):
1. Если второе число меньше или равно 12. Excel будет пытаться определить в первом числе день, во втором числе месяц
2. Если второе число больше 12, но меньше или равно 29. Excel будет считать первое число месяцем, второе — годом. Год будет считаться от 1900
3. Если второе число больше или равно 30. Excel будет считать первое число месяцем, второе — годом. Год будет считаться от 2000

Итак, причины и механизмы возникновения ошибки ясны. Как поправить? Простая махинация вроде —A2 не поможет ведь после «дописки» значения до даты прошло преобразование и то же число 6.34 это теперь 12 571.

Исправление немного сложнее. Во-первых посмотрим по какому пути пошло преобразование. Если год от этой даты совпадает с текущим годом — значит мы имеем дело с первой ситуацией, в ином случае — ситуация 2 или 3.

Запишем формулу с вложенным ЕСЛИ():

Для Ситуации 1 мы должны склеить дни (целое число) и номер месяца (дробная часть числа):

Для Ситуации 2 и 3 мы должны взять номер месяца (целое число) и две последние цифры года (дробная часть числа).

Осталось только подставить эти значения в наш ЕСЛИ() в соответствующие группы формулы:

И вуаля. Можем преобразовать хоть 10 000 строк за раз.

PS К сожалению, в ситуации 1 мы не можем точно знать какое число было преобразовано в дату — с нулем, или без него как первый знак после запятой. То есть 4,08 и 4,8 в данном случае будут преобразованы одинаково.

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

Функция ДАТАЗНАЧ для преобразования текста в дату в Excel

Функция ДАТАЗНАЧ в Excel предназначена для работы с текстовыми данными в формате ДАТА. Она используется для преобразования текстовых данных в формат Дата и возвращает числовое значение, характеризующее указанную дату.

Как преобразовать дату в текст в Excel

В Excel каждой дате соответствует определенное число дней, прошедших с принятой точки отсчета – 1 января 1900 года. Функция ДАТАЗНАЧ возвращает число, соответствующее числовому представлению даты, которая указана в виде текста, с учетом указанной выше особенности хранения дат в Excel. Формат возвращаемого значения зависит от настроек формата ячейки, в которой будет выведен результат вычислений.

Зачастую даты в Excel записывают без использования функции ДАТА. Табличный редактор определяет такие значения как обычные текстовые строки. Поэтому процедуры форматирования, сортировки по дате, а также различные вычисления (например, разница дат) приводят к некорректным результатам или появлению ошибок. Поэтому функция ДАТАЗНАЧ полезна для преобразования текстовых значений к данным формата Дата.

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

Вид таблицы данных:

Для получения даты в формате, поддерживаемом Excel, используем следующую функцию:

Единственный аргумент состоит из подстрок, склеенных амперсандами (&):

1. Функция ЛЕВСИМВ возвращает номер дня (первые два символа строки, содержащейся в ячейке A2). Очень важно, чтобы однозначные номера дней (например, 8 апреля) записывались как 08 апреля (имели нуль в начале), иначе будет возникать ошибка.
2. Комбинация функций ПСТР и ЛЕВСИМВ выделяет из строки три первых символа названия месяца и возвращает их.
3. Комбинация функций ПСТР и ПРАВСИМВ выделяет 4 символа, соответствующие числовому представлению года.

Растянем формулу вниз по столбцу, чтобы рассчитать остальные значения:

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

Обработка значений даты в текстовом формате в Excel

Пример 2. В таблице Excel указаны даты неверного формата (вместо записи вид «13.06.2019» используется 13_06_2019). Такие данные указаны в двух столбцах. В соседнем необходимо вычислить разницу дней между указанными датами.

Вид таблицы данных:

Для расчетов используем следующую формулу:

Для получения текстовой строки, которая может быть преобразована в данные формата Дата с помощью функции ДАТАЗНАЧ, используем функцию ПОДСТАВИТЬ, которая выполняет замену символов «_» на «.». Результат вычитания двух полученных дат – искомое значение.

Растянем формулу вниз по столбцу чтобы рассчитать все значения:

Особенности синтаксиса функции ДАТАЗНАЧ в Excel

Функция ДАТАЗНАЧ имеет следующую синтаксическую запись:

Единственным аргументом (обязателен для заполнения) является дата_как_текст – текстовое представление даты, которое может быть преобразовано к данным формата Дата. В Excel есть несколько допустимых вариантов записи дат: 13-июн-2019, 13.06.2019. Любой из этих вариантов записи может быть использован в качестве аргумента функции ДАТАЗНАЧ.

1. Если текстовые строки, характеризующие даты, хранятся в ячейках Excel, большинство функций выполняют преобразования данных к требуемому типу автоматически. Однако, во избежание возможных ошибок, рекомендуется использовать функцию ДАТАЗНАЧ.
2. Рассматриваемая функция ориентируется на показания часов, встроенных в ПК, на котором используется редактор Excel. Если в качестве текстового представления даты указана неполная дата, например «13.06», данные о годе будут взяты из текущего времени. Например, функция =ДАТАЗНАЧ(“13.06”) вернет значение 43629, которое после установления формата Дата для ячейки будет преобразовано в 13.06.2019.
3. Если в качестве аргумента функции ДАТАЗНАЧ было передано значение, не преобразуемое к формату Дата (например, =ДАТАЗНАЧ(23), =ДАТАЗНАЧ(ИСТИНА), =ДАТАЗНАЧ(“333”)), будет возвращен код ошибки #ЗНАЧ!
4. Для склеивания значений, содержащихся в отдельных ячейках, чтобы «собрать» их в одну строку, характеризующую значение даты, следует использовать символ “&”. Например, в ячейках A1, B1, C1 хранятся значения 10, 3 и 2019 соответственно. Чтобы получить данные формата Дата и записать их в отдельную ячейку, можно использовать следующую функцию — =ДАТАЗНАЧ(A1&».»&B1&».»&C1).

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

Преобразование строки даты в дату

= ЛЕВСИМВ (дата; 10) + ПСТР (дата; 12;8)

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

Чтобы преобразовать строку даты в дату-время (дату со временем), вы можете разобрать текст на отдельные компоненты, а затем построить правильное время и дату.

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

Для извлечения даты формула в C5:

Чтобы извлечь дату, формула в D5:

= ВРЕМЗНАЧ (ПСТР (B5;12;8))

Чтобы собрать дату-время, формула в E5:

Чтобы получить дату, мы извлекаем первые 10 символов значения с помощью ЛЕВСИМВ:

ЛЕВСИМВ(B5;10) // возвращает «2015-03-01»

Результатом является текст, поэтому, чтобы заставить Excel интерпретироваться как дата, мы помещаем ЛЕВСИМВ в ДАТАЗНАЧ, который преобразует текст в правильное значение даты Excel.

Чтобы получить время, мы извлекаем 8 символов из середины значения с ПСТР:

ПСТР (B5;12;8) // возвращает «12:28:45»

Опять же, результатом является текст. Чтобы заставить Excel интерпретироваться как время, мы помещаем ПСТР в ВРЕМЗНАЧ, который преобразует текст в правильное значение времени Excel.

Чтобы построить окончательную дата-время, мы просто добавляем значение даты к значению времени.

Хотя этот пример извлекает дату и время отдельно для ясности, вы можете комбинировать формулы, если хотите. Следующая формула извлекает дату и время и объединяет их в один шаг:

= ЛЕВСИМВ(дата; 10) + ПСТР(дата; 12;8)

Обратите внимание, что в этом случае значения ДАТАЗНАЧ и ВРЕМЯЗНАЧ не нужны, поскольку математическая операция (+) заставляет Excel автоматически принудительно передавать текстовые значения в числа.

Преобразовать дату в Юлианский формат

= ГОД (дата) и ТЕКСТ (дата-ДАТА (ГОД (дата); 1;0); «000»)

Если вам нужно преобразовать дату в формат даты в Юлиане в Excel, вы можете сделать это, построив формулу, в которой используются функции ТЕКСТ, ГОД и ДАТА.

«Формат даты в Юлиане» относится к формату, в котором значение года для даты комбинируется с «порядковым днем для этого года» (т. Е. 14-й день, 100-й день и т. д.) для формирования штампа даты.

Есть несколько вариантов. Дата в этом формате может включать в себя 4-значный год (гггг) или год с двумя цифрами (гг), а номер дня может быть заполнен нулями или может быть не дополнен тремя цифрами. Например, на дату 21 января 2017 года вы можете увидеть:

Для двухзначного года + число дня без дополнения используйте:

Для двузначного года + число дня, дополненное нулями до 3-х мест:

Для четырехзначного года + число дня, дополненное нулями до 3-х мест:

Эта формула строит окончательный результат в 2 частях, объединенных конъюнкцией с оператором амперсанда (&).

Слева от амперсанда мы генерируем значение года. Чтобы извлечь 2-значный год, мы можем использовать функцию ТЕКСТ, которая может применять числовой формат внутри формулы:

Чтобы извлечь полный год, используйте функцию ГОД:

С правой стороны амперсанда нам нужно определить день года. Мы делаем это, вычитая последний день предыдущего года с того дня, с которым мы работаем. Поскольку даты — это просто серийные номера, это даст нам «n» день года.

Чтобы получить последний день года предыдущего года, мы используем функцию ДАТА. Когда вы даете ДАТА значение года и месяца и ноль на день, вы получаете последний день предыдущего месяца. Так:

B5-ДАТА(ГОД (B5); 1;0)

Дает нам последний день предыдущего года, который на примере 31 декабря 2015 года.

Теперь нам нужно заполнить значение дня нулями. Опять же, мы можем использовать функцию ТЕКСТ:

ТЕКСТ (B5-ДАТА (ГОД (B5); 1;0); «000»)

Если вам нужно преобразовать юлианскую дату назад к обычной дате, вы можете использовать формулу, которая анализирует юлианскую дату и пробегает ее через функцию даты с месяцем 1 и днем, равным «n-му» дню. Например, это создаст дату с Юлианской датой ггггддд, например, 1999143.

= ДАТА(ЛЕВСИМВ(A1;4); 1; ПРАВСИМВ(A1;3)) // для ггггддд

Если у вас есть только номер дня (например, 100, 153 и т. д.), вы можете жестко закодировать год и вставить следующий день:

Где A1 содержит номер дня. Это работает, потому что функция ДАТА умеет настраивать значения вне диапазона.

Преобразование даты в месяц и год

Чтобы преобразовать нормальную дату Excel в формат ггггмм (например, 9/1/2017> 201709), вы можете использовать функцию ТЕКСТ.

В показанном примере формула в C5:

= ТЕКСТ (B5; «ггггмм»)

Функция TEКСT применяет заданный числовой формат к числовому значению и возвращает результат в виде текста.

В этом случае предоставляется формат числа «ггггмм», который присоединяется к 4-значному году с 2-значным значением месяца.

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

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

= ТЕКСТ (дата; формат)

Если вам нужно преобразовать даты в текст (т. е. дату в преобразование строк), вы можете использовать функцию ТЕКСТ. Функция ТЕКСТ может использовать такие шаблоны, как «дд / мм / гггг», «гггг-мм-дд» и т. д., чтобы преобразовать действительную дату в текстовое значение.

Даты и время в Excel хранятся в виде серийных номеров и преобразуются в удобочитаемые значения «на лету» с использованием числовых форматов. Когда вы вводите дату в Excel, вы можете применить числовой формат, чтобы отобразить эту дату по своему усмотрению. Аналогичным образом, функция ТЕКСТ позволяет преобразовать дату или время в текст в предпочтительном формате. Например, если дата 9 января 2000 года введена в ячейку A1, вы можете использовать TEКСТ, чтобы преобразовать эту дату в следующие текстовые строки следующим образом:

= ТЕКСТ(A1; «ммм») // «Янв»

= TEКСТ(A1; «дд/мм/гггг») // «09/01/2012»

= ТЕКСТ(A1; «дд-ммм-гг») // «09-Янв-12»

Вы можете использовать TEКСТ для преобразования дат или любого числового значения в фиксированном формате. Вы можете просмотреть доступные форматы, перейдя в меню «Формат ячеек» (Win: Ctrl + 1, Mac: Cmd + 1) и выбрав различные категории в списке слева.

Преобразование даты текста дд/мм/гг в мм/дд/гг

= ДАТА(ПРАВСИМВ(A1;2) + 2000; ПСТР(A1;4;2); ЛЕВСИМВ(A1;2))

Чтобы преобразовать даты в текстовом формате дд/мм /гг в истинную дату в формате мм/дд/гг, вы можете использовать формулу, основанную на функции ДАТА. В показанном примере формула в C5:

= ДАТА(ПРАВСИМВ(B5;2) + 2000; ПСТР(B5;4;2); ЛЕВСИМВ (B5;2))

Который преобразует текстовое значение в B5 «29/02/16» в правильную дату Excel.

Ядром этой формулы является функция ДАТА, которая используется для сборки правильного значения даты Excel. Функция ДАТА требует действительных значений года, месяца и дня, поэтому они анализируются из исходной текстовой строки следующим образом:

Значение года извлекается с помощью функции ПРАВСИМВ:

ПРАВСИМВ получает по крайней мере 2 символа от исходного значения. Число 2000 добавлено к результату, чтобы создать действительный год. Это число переходит в ДАТА в качестве аргумента год.

Значение месяца извлекается с помощью:

ПСТР извлекает символы 4-5. Результат переходит в ДАТА в качестве аргумента месяц.

Значение дня извлекается с помощью:

ЛЕВСИМВ захватывает последние 2 символа исходного текстового значения, которое переходит в ДАТА в качестве аргумента дня.

Три значения, извлеченные выше, входят в ДАТУ следующим образом:

= ДАТА (2016; «02»; «29»)

Хотя месяц и день предоставляются в виде текста, функция ДАТА автоматически преобразуется в числа и возвращает действительную дату.

Примечание: значение 2016 года автоматически было преобразовано в число при добавлении 2000.

Если исходное текстовое значение содержит дополнительные начальные или конечные символы пробела, вы можете добавить функцию СЖПРОБЕЛЫ для удаления:

= ДАТА(ПРАВСИМВ (СЖПРОБЕЛЫ (A1); 2) + 2000; ПСТР(СЖПРОБЕЛЫ (A1); 4;2); ЛЕВСИМВ(СЖПРОБЕЛЫ (A1); 2))

Преобразование текста в дату

=ДАТА (ЛЕВСИМВ(текст; 4); ПСРТ(текст; 5;2); ПРАВСИМВ(текст; 2))

Чтобы преобразовать текст в непринятом формате даты в правильную дату Excel, вы можете проанализировать текст и собрать правильную дату с формулой, основанной на нескольких функциях: ДАТА, ЛЕВСИМВ, ПСРТ и ПРАВСИМВ.

В показанном примере формула в C6:

= ДАТА(ЛЕВСИМВ(B6;4); ПСРТ(B6;5;2); ПРАВСИМВ(B6;2))

Эта формула отдельно извлекает значения года, месяца и дня и использует функцию ДАТА, чтобы собрать их в дату 24 октября 2000 года.

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

текст (19610412) Дата представления (Апрель 12, 1961)

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

Функция ДАТА принимает три аргумента: год, месяц и день. ЛЕВСИМВ извлекает самые левые 4 символа и поставляет это в ДАТА в качестве года. Функция ПСРТ извлекает символы 5-6 и поставляет это в ДАТА в качестве месяца, а функция ПРАВСИМВ извлекает самые правые 2 символа и поставляет их в ДАТА в качестве дня. Конечным результатом является правильная дата Excel, которая может быть отформатирована любым способом.

В строке 8 (непризнанный) формат даты дд.мм.гггг и формула в C8:

= ДАТА(ПРАВСИМВ(B8;4); ПСРТ(B8;4;2); ЛЕВСИМВ(B8;2))

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

Чтобы преобразовать даты, добавив нуль, попробуйте Специальную вставку:

• Добавить ноль в неиспользуемую ячейку и скопировать в буфер обмена
• Выберите проблемные даты
• Специальная вставка> Значения> Добавить

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

Где A1 содержит непризнанную дату.

Другой способ заставить Excel распознавать даты — использовать текст в столбцах:

Выберите столбец дат, затем попробуйте Дата> Текст в столбах>Исправлено> Конец