Как сохранить дату и время измененных данных или записей ячеек в excel

Описание сути проблемы

Посмотрите на следующий скриншот. Я специально несколько чисел записал с одинаковым разделителем дробной части от целой, чтобы показать, как Эксель автоматически форматирует их в даты. Такое конвертирование происходит только в том случае, если само число может являться датой. Например, это будет 6.1995 – такое число отобразится как июн.95 и вернуть ему прежний формат уже проблематично.

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

Комьюнити теперь в Телеграм

Подпишитесь и будьте в курсе последних IT-новостей

Использование формулы TODAY() для автоматического обновления даты

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

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

После ввода формулы и нажатия клавиши «Enter», в выбранной ячейке будет отображена текущая дата. Затем, каждый раз при открытии файла или пересчете формул, эта дата будет автоматически обновляться.

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

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

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

Удалить время из даты в Excel с помощью формул

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

В этом разделе я покажу вам, как это сделать, используя три формулы Excel — INT, DATEVALUE и TEXT.

Использование формулы INT

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

Ниже приведена формула INT, которая может это сделать:

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

Но вы по-прежнему видите в результате временную часть (12:00 AM)… почему это так?

Это из-за формата ячейки, который заставляет ее показывать дату, а также время. А поскольку значение времени равно 0, для всех ячеек отображается 12:00.

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

Вот шаги, чтобы отображать только дату, а не время в Excel:

Перейдите на вкладку «Главная»
В группе «Номер» щелкните раскрывающийся список.
Выберите Краткую дату (или Длинную дату) — в зависимости от того, как вы хотите отображать дату.

Вот и все!

Вы успешно удалили время из даты.

Теперь давайте посмотрим на другую формулу Excel, чтобы сделать это.

Использование формулы ДАТА ЗНАЧЕНИЕ

В то время как формула INT извлекает целую часть и отбрасывает десятичную часть (которая является частью времени), функция DATEVALUE выполняет одну задачу — дать вам значение даты из метки времени.

Итак, если я дам входное значение как 29-02-2020 02:36:43, он даст мне только значение даты, а не время.

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

Мне нужно убедиться, что дата указана в текстовом формате. Поэтому мне придется объединить функцию DATEVALUE с функцией TEXT, чтобы получить результат.

Ниже приведена формула, которая удаляет время из даты: В приведенной выше формуле используется функция ТЕКСТ, чтобы взять дату и передать ее в указанном текстовом формате. Затем это используется функцией DATEVALUE для возврата единственной части даты в метке времени.

Вы увидите результат в виде числа — числового значения даты. Вы можете вручную изменить формат результата на короткую / длинную дату или использовать формулу ниже:

В приведенной выше формуле я заключил результат DATEVALUE в формулу TEXT и указал формат как «дд-мм-гггг». Это даст результат в виде текстового значения, но вам не нужно беспокоиться о формате.

Чтение файлов csv или txt при возможности записи значений в файл

Если возможно влиять на запись значений в файле csv или txt, то можно использовать следующие способы.

Использование формул при записи csv/txt файла

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

column1,»=»»1/4″»»,1/4

1	column1,»=»»1/4″»»,14

При открытии такого файла во втором столбце Excel правильно отобразит значение. А в третьем столбце по умолчанию превратит в дату.

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

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

Использование скрытых символов при записи csv/txt файла

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

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

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

К минусам данного способа относится тот факт, что пользователь не сможет проверять текст на равенства или использовать нормально формулы, так как символ будет в тексте, и с ним надо что-то делать. Например, пробелы спереди можно убирать функцией СЖПРОБЕЛЫ, а табуляцию с помощью ПЕЧСИМВ.

Я рассмотрел несколько способов, как можно открывать файлы и отключить конвертацию значений в даты. В большинстве случаев они базируются на превращении данных в текст. На мой взгляд, без лишней скромности, самый простой способ это юзать мой бесплатный скрипт Excel-CSVD. Все остальные способы требуют от пользователя каких-то лишних действий. Скрипт Excel-CSVD можно привязать к исполнению файлов csv, и открывать из меню Открыть с помощью. Но тут у каждого свой подход и свои заморочки.

Всем удачной работы в Excel…

Как установить текущую дату в Excel на колонтитулах

Вставка текущей даты в Excel реализуется несколькими способами:

Задав параметры колонтитулов. Преимущество данного способа в том, что текущая дата и время проставляются сразу на все страницы одновременно.
Используя функцию СЕГОДНЯ().
Используя комбинацию горячих клавиш CTRL+; – для установки текущей даты и CTRL+SHIFT+; – для установки текущего времени. Недостаток – в данном способе не будет автоматически обновляться значение ячейки на текущие показатели, при открытии документа. Но в некоторых случаях данных недостаток является преимуществом.
С помощью VBA макросов используя в коде программы функции: Date();Time();Now() .

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

Чтобы сделать текущую дату в Excel и нумерацию страниц с помощью колонтитулов сделайте так:

Откройте окно «Параметры страницы» и выберите закладку «Колонтитулы».
Нажмите на кнопку создать нижний колонтитул.
В появившемся окне щелкните по полю «В центре:». На панели выберите вторую кнопку ««Вставить номер страницы»». Потом выберите первую кнопку «Формат текста» и задайте формат для отображения номеров страниц (например, полужирный шрифт, а размер шрифта 14 пунктов).
Для установки текущей даты и времени щелкните по полю «Справа:», а затем щелкните по кнопке «Вставить дату» (при необходимости щелкните на кнопку «Вставить время»). И нажмите ОК на обоих диалоговых окнах. В данных полях можно вводить свой текст.
Нажмите на кнопку ОК и обратите на предварительный результат отображения колонтитула. Ниже выпадающего списка «Нижний колонтитул».
Для предварительного просмотра колонтитулов перейдите в меню «Вид»-«Разметка страницы». Там же можно их редактировать.

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

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

FileDate Changer v1.1 — Изменить время создания / изменения файлов

Примечание: FileDate Changer устарел. Вы можете попробовать новую утилиту File Time Changer — BulkFileChanger.

Утилита FileDate Changer позволяет легко изменять даты создания / изменения / доступа для одного или нескольких файлов. Вы можете использовать эту утилиту во всех 32-битных операционных системах (Windows 95,98, ME, NT, 2000, XP) с Internet Explorer версии 3.0 или выше.

Известные ограничения

В файловой системе FAT точное время «Даты доступа» не сохраняется как другие даты. Сохраняется только дата (дд / мм / гггг).
«Дата доступа» имеет небольшую хитрость: С помощью утилиты FileDate Changer вы можете изменить «Дата доступа». Однако, если вы попытаетесь просмотреть «дату доступа» к файлу после его изменения, вы увидите текущую дату вместо даты, которую вы только что изменили ранее! Причина такого поведения: когда вы просматриваете свойства файла (в среде проводника), «Дата доступа» всегда изменяется операционной системой на текущую дату…

Эта утилита выпущена как бесплатное ПО. Вы можете свободно использовать и распространять его. Если вы распространяете эту утилиту, вы должны включить все файлы в дистрибутив, включая readme.txt, без каких-либо изменений!

История версий

1,1	10.02.2002	Небольшое улучшение пользовательского интерфейса (в элементах управления датой / временем) Добавлена возможность изменять даты файлов сразу из разных папок. Перетаскивание: вы можете добавлять файлы в список, перетаскивая их из окна проводника в окно этой утилиты.
1.0	03.03.2002	Первая версия

Программное обеспечение предоставляется «КАК ЕСТЬ» без каких-либо явных или подразумеваемых гарантий, включая, но не ограничиваясь, подразумеваемые гарантии товарной пригодности и пригодности для определенной цели. Автор не несет ответственности за какие-либо особые, случайные, косвенный или косвенный ущерб из-за потери данных или по любой другой причине.

Использование FileDate Changer

Утилита FileDate Changer не требует никакого процесса установки, если у вас установлена более новая версия comctl32.dll: версия 4.70 или более поздняя. Вам не нужно об этом беспокоиться, потому что этот файл обычно устанавливается Internet Explorer версии 3.0 или более поздней, а также другими приложениями. Вероятно, в вашей системе уже есть нужная версия comctl32.dll. Чтобы начать использовать эту утилиту, скопируйте исполняемый файл (filedate.exe) в любую папку, которая вам нужна, и запустите его. После запуска утилиты следуйте инструкциям ниже, чтобы изменить даты одного или нескольких файлов:

Нажмите кнопку «Добавить файлы» и выберите один или несколько файлов. Вы можете выбрать несколько файлов в одной папке, удерживая клавиши Ctrl или Shift. Вы можете повторять описанную выше операцию и каждый раз выбирать файлы из разных папок. Вы также можете добавлять файлы, перетаскивая их из окна проводника в окно FileDate Changer.
Выберите тип даты (Дата создания, Дата изменения и Дата доступа), который следует изменить, сняв или установив 3 флажка.По умолчанию «Дата доступа» отключена.
Выберите дату и время для изменения выбранных файлов и нажмите «Изменить дату файлов».

Обратная связь

Если у вас есть проблемы, предложения, комментарии или вы обнаружили ошибку в моей утилите, вы можете отправить сообщение на

При работе в открытом Excel

Если в любой ячейке написать, например, 1/4, то это превратится в 04-янв. Для изменения поведения Excel есть такие способы

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

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

Вставка символа апострофа перед любым текстом

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

Продвинутый поиск

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

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

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

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

Поиск формата – это хорошо, но чаще искать приходится конкретные значения. И тут Excel предоставляет дополнительные возможности для расширения и уточнения параметров поиска.

Первый выпадающий список Искать предлагает ограничить поиск одним листом или расширить его до целой книги.

По умолчанию (если не лезть в параметры) поиск происходит только на активном листе. Для повторения поиска на другом листе все действия нужно проделать еще раз. А если таких листов много, то поиск данных может отнять немало времени. Однако если выбрать пункт Книга, то поиск произойдет сразу по всем листам активной книги. Выгода очевидна.

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

В следующем выпадающем списке находится замечательная возможность поиска по формулам, значениям, а также примечаниям. По умолчанию Excel производит поиск в формулах либо, если их нет, в содержимом ячейки. Например, если искать фамилию Иванов, а фамилия эта есть результат формулы (копируется из соседнего листа), то поиск нечего не даст, т.к. в ячейке нет искомого перечня символов. По той же причине не удастся отыскать число, являющееся результатом работы какой-либо функции. Поэтому бывает смотришь в упор на ячейку, видишь искомое значение, а Excel его почему-то не видит. Это не глюк, это настройка поиска. Измените данный параметр на Значения и поиск будет осуществляться по тому, что отражено в ячейке, независимо от содержимого. Например, если в ячейке содержится результат вычисления 1/6 (как значение, а не формула) и при этом формат отражает только 3 знака после запятой (т.е 0,167), то поиск символов «167» при выборе параметра Формулы эту ячейку не обнаружит (реальное содержимое ячейки — это 0,166666…), а при выборе Значения поиск увенчается успехом (искомые символы совпадают с тем, что отражается в ячейке). И последний пункт в данном списке – Примечания. Поиск осуществляется только в примечаниях. Очень может помочь, т.к. примечания часто скрыты.

В диалоговом окне поиска есть еще две галочки Учитывать регистр и Ячейка целиком. По умолчанию Excel игнорирует регистр, но можно сделать так, чтобы «иванов» и «Иванов» отличались. Галочка Ячейка целиком также может оказаться весьма полезной, если ищется ячейка не с указанным фрагментом, а полностью состоящая из искомых символов. К примеру, как найти ячейки, содержащие только 0? Обычный поиск не подойдет, т.к. будут выдаваться и 10, и 100. Зато, если установить галочку Ячейка целиком, то все пойдет, как по маслу.