Как отделить дату от времени в excel
В ячейках Excel могут быть числа, текст, формулы, ссылки на ячейки и даты.
Даты имеют свою специфику, в первую очередь, из-за большого количества форматов.
Мы разберем основы работы с датами и решение типовых ошибок, с которыми мы сталкиваемся.
Что такое даты для Excel?
Дата и Время — это числа для Excel. Целая часть — номер дня, а все что идет после запятой — время. Если перевести в разные форматы, получим следующее:
То есть 12.07.2016 12:50:30 для Excel значение — 42563,5350694,
Где 42563 — это порядковый номер дня с 1 января 1900 года, а часть после запятой — это время.
С датами можно производить вычисления. Например, вычитать или складывать даты.
Даты нужны для группировки ежедневных данных в недельные, месячные и годовые отчеты. Пример группировок различных рекламных каналов по дате:
Основные ошибки с датами и их решение
Перевод разных написаний дат
Разные системы в выгрузках выдают даты по-разному, например: 12.07.2016 12-07-16 16-07-12 и так далее. Иногда месяца пишут текстом. Для того, чтобы привести даты к одному формату мы используем функцию ДАТА:
Данный вариант подходит, когда количество символов в дате одинаковое. Если вы работаете с однотипными выгрузками еженедельно, создание дополнительного столбца и протягивание формулы будет простым решением.
Дата определяется как текст
Проблемы с датами начинаются, когда мы импортируем данные из других источников. При этом, даты могут выглядеть нормально, но при этом являться текстом. С ними нельзя проводить вычисления, группировки в сводных таблицах, сортировать. Есть 2 решения, которые позволяют сделать это автоматически: 1. Получение значения даты; 2. Умножение текстового значения на единицу.
Получение значения даты
С помощью формулы ЗНАЧ мы выводим текстовое значение даты, потом его форматируем как Дату:
Умножение текстового значения на единицу
Принцип аналогичный предыдущему, только мы текстовое значение умножаем на единицу и после этого форматируем как дату:
Примечание: если дата определилась как текст, то вы не сможете делать группировки. При этом дата будет выровнена по левому краю. Excel выравнивает числа и даты по правому краю.
Очистка дат от некорректных символов
Чтобы привести к нужному стандарту, часть дат можно очистить с помощью функции Найти и Заменить. Например, поменять слэши («/») на точки:
То же самое можно сделать формулой ПОДСТАВИТЬ
Итак, мы разобрали основные варианты решения проблем с датами. Конечно, способов гораздо больше. И подобные преобразования удобнее делать через Power Query, который очень хорошо понимает и преобразовывает даты, но об этом в следующей статье.
Как отделить дату от времени в excel
От нашей студентки проходящей курс «Excel бизнес-анализ и прогнозирование» поступил вопрос в чат с тренером поддержки, по организации работы с формулами даты и времени. Вопрос возник в процессе внедрения в работу знаий полученных на курсе.
Суть «кейса»: имеется табличка с двумя полями, в которой содержится информация о дате создания и дате закрытия операции, на основании которых нужно вычислить количество и процент операций, которые были закрыты:
— в первый рабочий день (со времени начала выполнения операции до 18:00 следующего рабочего дня);
— в пределах двух следующих рабочих дней;
— в сроки более двух рабочих дней.
Для того чтобы всё правильно прописать, для начала нужно правильно проранжировать периоды.
Добавим «технический столбец» в котором пропишем с помощью логических функций ЕСЛИ следующие условия:
1. Если даты создания и закрытия совпадают, то это считается первым рабочим днем (в столбец подставится единичка)
В полях у нас формат дата-время, поэтому чтобы сравнить дни, нужно из этого формата извлечь дату. Сделать это можно несколькими способами:
- С помощью функции ДАТА, собрав даты из наших полей
=ЕСЛИ(ДАТА(ГОД(A2);МЕСЯЦ(A2);ДЕНЬ (A2))=ДАТА(ГОД(B2);МЕСЯЦ(B2);ДЕНЬ(B2));1
- Но если помнить, что дата и время для Excel являются числами (время – дробная часть даты), то можно пойти более коротким путём, используя функцию ЦЕЛОЕ.
2. На месте аргумента [значение_если_ложь] функции ЕСЛИ напишем ещё одну функцию ЕСЛИ, в которой проверим, является ли дата закрытия следующим рабочим днем (на всякий случай проверим чтобы дата была меньше двух рабочих дней от даты создания)
и чтобы одновременно проверялось условие, что время до 18:00
Данная проверка условий также входит в «операции, закрытые в первый рабочий день», поэтому в аргументе [значение_если_истина] должна подставляться единичка.
Формула на данном этапе выглядит примерно так:
3. Теперь пришла очередь проверить принадлежит ли дата закрытия к группе «закрытые в пределах двух следующих рабочих дней». На месте аргумента [значение_если_ложь] пишем еще одну ЕСЛИ, которая будет проверять, является ли дата закрытия меньше третьего рабочего дня и тогда подставляться двоечка в наш «технический столбец».
- Почему не пишем проверку даты, является ли она больше первого рабочего дня? Потому что функция ЕСЛИ останавливается если её результатом является ИСТИНА и при правильном написании условий можно сократить длину формулы, а при неправильном – запутаться, и получить некорректный результат.
- Почему не проверяем час на Потому что это не оговорено условием и по умолчанию считаем что в группу входят даты до конца дня
4. Прописывать условия для третьей группы не нужно, так как в неё попадают все даты, которые не соответствуют предыдущим условиям. На месте аргумента [значение_если_ложь] пишем троечку и закрываем скобки для всех функций ЕСЛИ.
Полностью формула выглядит так:
Имея столбец, в котором каждая операция принадлежит к конкретной группе, можем посчитать необходимые показатели.
Для подсчёта количества используем функцию СЧЁТЕСЛИ.
Первая группа: =СЧЁТЕСЛИ($C$2:$C$17;1)
Вторая группа: =СЧЁТЕСЛИ($C$2:$C$17;2)
Третья группа: =СЧЁТЕСЛИ($C$2:$C$17;3)
С расчётом процента также никаких сложностей нет. Нужно просто разделить количество операций, принадлежащих к конкретной группе на общее количество операций (можно использовать как формулы которые написали выше, так и ссылки на ячейки с этими формулами:
Первая группа: =J2/СЧЁТ($C$2:$C$17)
Вторая группа: =J5/СЧЁТ($C$2:$C$17)
Третья группа: =J7/СЧЁТ($C$2:$C$17)
Таким образом, с помощью простых и понятных операций, помогли нашей студентке справиться с поставленной задачей.
Такие и подобные вопросы поступают к нам в чат поддержки ежедневно в рамках курса «Excel бизнес-анализ и прогнозирование». Многие вопросы цикличны, так как все мы сталкиваемся с аналогичными проблемами в своей работе!
Мы с командой DATAbi будем делиться этими практическими кейсами и выполнять свои рабочие задачи лучше и проще!
Автор статьи тренер DATAbi Михаил Беленчук
Как отделить дату от времени в excel
Добрый день, Есть дата 31.01.2014 18:00:00, нужно 31.01.2014 занести в ячейку А1 а 18:00:00 в ячейку А2.
Приложил свои потуги.
Прикрепленные файлы
- Пример.xlsm (19.69 КБ)
Пользователь
Сообщений: 972 Регистрация: 14.01.2014
15.02.2014 13:15:29
Округл(ДатаВремя;0) — будет дата
Остат(ДатаВремя;1) — будет время.
Если автоматизировать бардак, то получится автоматизированный бардак.
Пользователь
Сообщений: 44 Регистрация: 11.12.2013
15.02.2014 13:17:23
Спасибо конечно, но хотелось бы средствами VBA
Пользователь
Сообщений: 972 Регистрация: 14.01.2014
15.02.2014 13:25:19
Так замените названия на английские и пропишите в макросе.
Если автоматизировать бардак, то получится автоматизированный бардак.
Пользователь
Сообщений: 44 Регистрация: 11.12.2013
15.02.2014 13:29:50
Формулы приму к сведению, на крайняк их вставлю. Разве нету никакой функции в VBA?
Пользователь
Сообщений: 436 Регистрация: 01.01.1970
15.02.2014 13:33:08
xltime = Right(Now, 8) xlDat = Left(Now, 10)
для вашего примера
Sub Date_Time() Dim d As Date Dim q As Date d = Cells(2, 1).Value Cells(4, 1) = Right(d, 8) Cells(5, 1) = Left(d, 10) End Sub
Изменено: lexey_fan — 15.02.2014 13:36:32
Если очень захотеть — можно в космос полететь 😉
Пользователь
Сообщений: 44 Регистрация: 11.12.2013
15.02.2014 13:38:02
Спасибо ,так понимаю ,то что получилось будет в текстовом формате ?
Пользователь
Сообщений: 44 Регистрация: 11.12.2013
15.02.2014 13:41:46
Именно для даты нету никакой функции ?
Пользователь
Сообщений: 436 Регистрация: 01.01.1970
15.02.2014 13:43:15
вот так будет формат даты
Sub Date_Time() Dim d As Date Dim q As Date d = Cells(2, 1).Value q = Right(d, 8) Cells(4, 1) = q q = Left(d, 10) Cells(5, 1) = q End Sub
Изменено: lexey_fan — 15.02.2014 13:43:30
Если очень захотеть — можно в космос полететь 😉
Пользователь
Сообщений: 4091 Регистрация: 01.01.1970
15.02.2014 13:51:21
А формат ячейки не устраивает? Потом будете решать как суммировать время при переходе суток.
Прикрепленные файлы
- Пример (2).xlsm (20.8 КБ)
Изменено: gling — 15.02.2014 13:56:57
Пользователь
Сообщений: 15596 Регистрация: 10.01.2013
15.02.2014 13:51:40
tm = TimeValue(Now) 'системное время dt = DateValue(Now) 'системная дата
Согласие есть продукт при полном непротивлении сторон.
Контакты, благодарности
Пользователь
Сообщений: 44 Регистрация: 11.12.2013
15.02.2014 13:53:42
Есть какая либо функция по типу
A = 31.01.2014 18:00:00
b = Функция(A, hh mm ss)
c = Функция(A. yyyy mm dd)
b= 18:00:00
c= 18.01.2014
Изменено: luppi — 15.02.2014 13:54:21
Пользователь
Сообщений: 15596 Регистрация: 10.01.2013
15.02.2014 13:56:16
См пост #11. Замените Now на Ваше значение.
Согласие есть продукт при полном непротивлении сторон.
Контакты, благодарности
Пользователь
Сообщений: 44 Регистрация: 11.12.2013
15.02.2014 13:59:11
Ок, это то что нужно, хотя все способы в разных ситуациях подойдут. Sanja, ты в точку попал, спасибо.
Остальным тоже спасибо.
Пользователь
Сообщений: 10514 Регистрация: 21.12.2012
15.02.2014 14:11:09
| Цитата |
|---|
| Sanja пишет: tm = TimeValue(Now) ‘системное время dt = DateValue(Now) ‘системная дата |
tm = Time 'системное время dt = Date 'системная дата
Пользователь
Сообщений: 47199 Регистрация: 15.09.2012
09.08.2021 16:13:10
dovos, создайте отдельную тему с названием, отражающим суть задачи
Страницы: 1
Читают тему
© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru
Использование любых материалов сайта допускается строго с указанием прямой ссылки на источник, упоминанием названия сайта, имени автора и неизменности исходного текста и иллюстраций.
| ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949 |
ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071 |
��КАК РАЗДЕЛИТЬ ДАТУ И ВОЕМЯ В EXCEL?
Начните обучение в ExcelClub вместе со мной уже в июле! ��https://excel-club.ru/anketa?utm_source=youtube 3 способа как разделить дату и время по разным столбцам в excel. простой способ разбить дату и время в Excel как разбить дату в Экселе по разным столбцам? чтобы разделить дату в excel нужно уметь работать с датами в Excel. разделить дату Экселе можно несколькими способами, чтобы узнать самый простой и быстрый вариант разделения Даты в Excel смотрите видео до конца Применяйте и автоматизируйте свою работу в Экселе и Гугл-таблицах! #excel #ExcelClub
Показать больше
Войдите , чтобы оставлять комментарии.