Parus16.ru

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

Часы в ячейке Excel

Часы в ячейке Excel

Сегодня темой поста будет вопрос одного из подписчиков: «Как вставить в ячейку таблицы время?» Вопрос требует некоторого пояснения. Нам требуются полноценные часы, которые будут отображать текущее время на компьютере.

Пришедшие поначалу в голову формулы СЕГОДНЯ() , ДАТА() и ВРЕМЯ() здесь не помогут, так как они будут оставлять «отпечатки» сегодняшних дней, дат, часов и секунд на момент ввода формулы. Также формулы не поддерживают автоматическое обновление.

Выход один — будем использовать макросы.

Пройдёмся по этапам создания нашей процедуры:

  1. Создадим новую книгу в Excel;
  2. На вкладке «Разработчик» жмём кнопку VisualBasic;
  3. Выполним команды Insert -> Module.

Часы в ячейке Excel

Появится пустое окно куда нужно поместить следующий макрос:

Dim varNextCall As Variant

‘записываем в ячейку текущее время
Cells(1, 1).Value = Now

‘записываем в varNextCall время, когда вызвать макрос в следующий раз (через 1 секунду)
varNextCall = TimeSerial(Hour(Now), Minute(Now), Second(Now) +1)

‘уведомляем программу в вызове макроса
Application.Ontime varNextCall, «UpdateTime»

Часы в ячейке Excel

Мы создали макрос записи обновления времени, который поместит в ячейку А1 текущее время, его можно будет вызвать сочетанием Alt+F8 или щёлкнуть по кнопке «Макросы».

Часы в ячейке Excel

Следующим шагом будет сохранение книги. Обязательно сохранить книгу с поддержкой макросов (.xlsm)!

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

Часы в ячейке Excel

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

В этом примере есть также один минус — как только мы закроем книгу, работа макроса прекратится. Для того, чтобы избежать этого мы прикрутим к макросу автоматический старт (как и в случае с КУРСАМИ ВАЛЮТ).

  1. Щёлкаем кнопку VisualBasic;
  2. В области Project два раза кликаем строчку «Эта книга»;
  3. В появившемся окне в поле «Object» выбираем «Workbook» и в поле «Procedure» название процедуры «Open»;
  4. Между появившихся строчек вводим вызов нашего макроса — Call UpdateTime.

Часы в ячейке Excel

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

Буду рад всем оставленным отзывам и комментариям! Ссылка на файл примера ниже:

Если остались вопросы посмотрите обучающее видео:

Дата VBA

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

Функция VBA DATE возвращает текущую дату на основе системной даты в результате и имеет очень простой синтаксис.

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

Читайте так же:
Можно ли отправлять почтой продукты питания

Синтаксис функции DATE в VBA.

Как использовать функцию даты VBA Excel?

Мы узнаем, как использовать функцию даты VBA с несколькими примерами в Excel.

Вы можете скачать этот шаблон Excel даты VBA здесь — Шаблон Excel даты VBA

Функция даты VBA — Пример # 1

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

Шаг 1. Вставьте новый модуль в редактор Visual Basic.

Шаг 2: Определите подпроцедуру для написания создания и сохранения макроса.

Код:

Шаг 3: Определите переменную с именем CurrDate, которая может содержать значение текущей даты. Поскольку мы собираемся присвоить значение даты переменной, убедитесь, что вы определяете ее как дату.

Код:

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

Код:

Шаг 5: Используйте MsgBox, чтобы увидеть текущую системную дату в окне сообщения. Используйте строку кода, приведенную ниже:

Код:

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

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

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

Функция даты VBA — пример № 2

Срок оплаты платежа по кредиту Home Loan

Предположим, у меня есть рабочий лист, и мне нужна система, чтобы показать мне сообщение « Эй! Вы должны оплатить EMI сегодня. «Каждый раз, когда я открываю свой лист, а значение в ячейке A1 является текущей системной датой. Давайте посмотрим шаг за шагом, как мы можем это сделать.

Шаг 1: Вставьте новый модуль и определите новую подпроцедуру с именем auto_open () для создания макроса. auto_open () позволяет вашему макросу запускаться автоматически каждый раз, когда вы открываете лист.

Код:

Шаг 2: Используйте условие If, чтобы назначить значение текущей даты в ячейке A1 рабочего листа HomeLoan_EMI.

Код:

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

Код:

Шаг 4: Добавьте оператор, который будет выполнен для условия, которое является истинным.

Читайте так же:
Можно ли восстановить замороженную страницу вк

Код:

Этот оператор появится в окне сообщения, как только будет выполнено условие.

Шаг 5: Как мы знаем, каждому условию IF всегда требовалось условие Else. Добавьте условие Else в этот цикл.

Код:

Это условие условия прекратит автоматическое открытие макроса, если дата в ячейке A1 не является текущей системной датой.

Шаг 6: Наконец, завершите цикл IF с помощью оператора End IF.

Код:

Шаг 7. Теперь все при открытии рабочего листа система автоматически запускает приведенный выше код и проверяет, является ли значение даты в ячейке A1 датой истечения вашего EMI или нет. Если срок исполнения EMI равен системной дате, будет показано сообщение, как показано ниже:

Функция даты VBA — пример № 3

VBA Дата, чтобы узнать о кредитной карте Билл Получатель

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

VBA Date может помочь вам автоматизировать вещи, а не проверять даты по очереди. Давайте посмотрим, как сделать это шаг за шагом:

Шаг 1: Определите новый макрос, используя подпроцедуру в модуле.

Код:

Шаг 2: Определите две новые переменные, одна из которых будет полезна при циклическом кодировании, а другая — для хранения значения текущей системной даты.

Код:

Шаг 3. Теперь используйте следующий фрагмент кода, который помогает в поиске человека, у которого в качестве текущей системной даты указана дата платежа по кредитной карте. Этот код позволяет проверить Клиента, который должен оплатить счет на текущую системную дату, а также сумму счета.

Код:

Шаг 4: Запустите этот код, нажав F5 или кнопку Run вручную и посмотрите результат.

На первой итерации мы видим, что Мохан — тот, у кого Билл 12 900, который должен быть выпущен 29 апреля 2019 года (текущая системная дата, в которую запускается этот код). Если мы нажмем ОК, мы увидим имя следующего клиента, чей счет должен быть выставлен 29 апреля 2019 года (следующим является Раджани).

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

То, что нужно запомнить

  • Функция VBA DATE возвращает текущую системную дату и параллельно с функцией TODAY () в Excel.
  • Функция VBA DATE не имеет аргумента для передачи в Excel. Даже не нужно вызывать скобки при использовании этой функции в коде.
  • VBA DATE — это энергонезависимая функция в Excel.
  • VBA хранит значения Date как DATE во время выполнения. Таким образом, не определяет переменную, содержащую значение как String / Integer. Это приведет к ошибке во время выполнения кода.
Читайте так же:
Заявление ифнс в личный кабинет

Рекомендуемые статьи

Это было руководство по Excel VBA Date. Здесь мы обсудили, как использовать функции даты VBA Excel вместе с практическими примерами и загружаемым шаблоном Excel. Вы также можете просмотреть наши другие предлагаемые статьи —

Вставить дату в ячейку excel

Работа с датами и временем в Excel

Как вводить даты и время в Excel

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

С использованием дефисов

С использованием дроби

Внешний вид (отображение) даты в ячейке может быть очень разным (с годом или без, месяц числом или словом и т.д.) и задается в меню Формат — Ячейки ( Format — Cells) :

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

По желанию можно дополнительно уточнить количество секунд — вводя их также через двоеточие:

И, наконец, никто не запрещает указывать дату и время вместе — через пробел:

Быстрый ввод дат и времени

Для ввода сегодняшней даты в текущую ячейку можно воспользоваться сочетанием клавиш Ctrl + Ж .

Если скопировать ячейку с датой (протянуть за правый нижний угол ячейки), удерживая правую кнопку мыши, то можно выбрать — как именно копировать выделенную дату:

Если Вам часто приходится вводить различные даты в ячейки листа — почитайте это .

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

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

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

Вот так, например, выглядит 8.10.2006 15:42.

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

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

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

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

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

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

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

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

В качестве аргументов этой функции необходимо указать начальную и конечную даты и ячейки с датами выходных (праздников):

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

Про то, как это правильно вычислять, лучше почитать тут .

Сдвиг даты на заданное количество дней

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

Сдвиг даты на заданное количество рабочих дней

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

Вычисление дня недели

Вас не в понедельник родили? Нет? Уверены? Можно легко проверить при помощи функции ДЕНЬНЕД (WEEKDAY) из категории Дата и время .

Другой способ: выделить ячейку с датой, открыть меню Формат — Ячейки (Format — Cells) , вкладка Число , далее — Все форматы и ввести в строку Тип новый формат ДДДД :

Вычисление временных интервалов

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

Нюанс здесь только один. Если при сложении нескольких временных интервалов сумма получилась больше 24 часов, то Excel обнулит ее и начнет суммировать опять с нуля. Чтобы этого не происходило, нужно применить к итоговой ячейке формат 37:30:55 (меню Формат — Ячейки — Число — Время )

Вставить дату в ячейку excel

Выпадающий календарь для ввода дат

Наша задача — добавить в книгу Excel пользовательскую форму (окно) с автоматическим календарем. Вот такое:

Окошко будет появляться при нажатии сочетания клавиш и после выбора в нем нужной даты — она попадает в текущую ячейку листа. Просто и красиво!

Читайте так же:
Емейл вход на почту регистрация бесплатно

Откройте редактор Visual Basic через меню Сервис — Макрос — Редактор Visual Basic (Tools — Macro — Visual Basic Editor).

Создайте в нем новую пустую пользовательску форму, выбрав в меню Insert — User form .

Автоматически должна появиться панель Toolbox :

Если не появилась — жмите в меню View — Toolbox .

На этой панели представлены различные управляющие элементы окон: кнопки, списки, счетчики и т.д. Но нам нужен элемент (календарь), которого пока на панели не видно. Чтобы добавить его, щелкните по серому фону панели правой кнопкой мыши и выберите Additional Controls . Появится вот такое окно:

В нем надо найти и отметить Элемент управления Календарь 11.0 (Calendar Control 11.0). Версии могут отличаться (11.0, 10.0 и т.д.) в зависимости от версии Microsoft Office, это несущественно. После нажатия на ОК на панели появится новая кнопка — Calendar :

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

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

Теперь переключитесь в режим ввода программного кода созданной формы (меню View — Code ) и скопируйте туда этот текст:

Private Sub Calendar1_Click()
ActiveCell = Calendar1.Value
ActiveCell.NumberFormat="dd/mm/yy"
End Sub

Private Sub UserForm_Activate()
Me.Calendar1.Value = Date
End Sub

Теперь надо заставить Excel показывать нам созданную форму, когда мы этого захотим. Для этого вставьте новый модуль ( Insert — Module ) и скопируйте туда этот текст:

Sub ShowCalendar()
UserForm1.Show
End Sub

Осталось закрыть редактор Visual Basic и, вернувшись в Excel, назначить созданному макросу ShowCalendar любое подходящее сочетание клавиш (меню Сервис — Макрос — Макросы — кнопка Параметры ).

Если у Вас установлена не полная версия Office, то элемента управления Календарь может не оказаться в списке Additional Controls . Тогда скачайте этот файл (MSCal.ocx.zip) , поместите его в папку C:Windowssystem32 и зарегистрируйте новый элемент управления через меню Пуск — Выполнить , далее набрать Regsvr32 c:windowssystem32mscal.ocx и нажать ОК .

(За дополнение спасибо Vadim )

Если Вы хотите, чтобы календарь автоматически появлялся на экране, когда пользователь выделяет определенные ячейки (диапазон) на листе, то щелкните правой кнопкой мыши по ярлычку этого листа и выберите Исходный текст (View Source). В открывшееся окно редактора Visual Basic скопируйте следующий код:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Application.Intersect(Range("A1:A20"), Target) Is Nothing Then
UserForm1.Show
End If
End Sub

Теперь при выделении любой ячейки из диапазона А1:А20 календарь будет автоматически отображаться на экране.

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