Вставка времени в excel

Формат даты в Excel, календарь в Excel, надстройка samradDatePicker

Вставка времени в excel

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

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

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

Как задать формат даты в Excel 2007/2010?

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

В раскрывшемся списке доступны два числовых формата: «Краткая дата» и «Длинный формат даты». В первом случае дата отображается числами, а во втором — числами и словами. Если предложенные форматы не подходят, выбираем пункт «Другие числовые форматы…», в результате чего появляется окно «Формат ячеек».

Это окно также можно вызвать щелчком левой кнопки мыши на правый нижний уголок группы,

либо просто кликнуть правой кнопкой мыши в области выделенных ячеек и выбрать из контекстного меню пункт «Формат ячеек».

В появившемся окне «Формат ячеек» переходим на вкладку «Число» и в группе «Числовые форматы» выбираем пункт «Дата»

В правой части окна отображаются возможности данного формата, в группе «Тип» выбераем нужный способ отображения даты. Форматы даты, перед которыми стоит значок *(звездочка), зависят от даты операционной системы, форматы, перед которыми этого значка нет – не зависят.

Как задать свой собственный формат даты? Как преобразовать дату в год, месяц либо день недели?

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

— М – для отображения месяцев в виде числа от 1 до 12;

— ММ — для отображения месяцев в виде числа от 01 до 12;

— МММ – для отображения месяцев в виде «янв» — «дек»;

— ММММ — для отображения месяцев в виде «январь» — «декабрь»;

— МММММ – для отображения месяцев виде первой буквы месяца;

Форматы с кодом «М» можно использовать для преобразования даты в месяц.

— д – для отображения дней в виде числа от 1 до 31;

— дд – для отображения дней в виде числа от 01 до 31;

— ддд – для отображения дней в виде «Пн» — «Вс»;

— дддд – для отображения дней в виде «понедельник» — «воскресенье»;

Форматы с кодом «д» используются для преобразования даты в день.

— гг – для отображения лет в виде чисел от 00 до 99;

-гггг – для отображения лет в виде чисел от 1900 до 9999.

Форматы с кодом «г» используются для преобразования даты в год.

Как установить систему дат 1904?

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

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

Как вводить дату в ячейку?

Для того, чтобы Excel воспринимал информацию, вводимую в ячейку, как дату, необходимо использовать в качестве разделителя точку, тире либо наклонную черту «/». При этом все введенные числа, меньшие 31.12, 31-12 либо 31/12 будут автоматически восприниматься как даты, а большие — как текст. Как уже говорилось выше, ячейки с датами могут использоваться в расчетах.

Как в Excel вводить даты при помощи надстройки?

Для тех, кто активно работает с датами в своих таблицах, были созданы надстройки, представляющие собой календарь в Excel. Пример такой надстройки – календарь samradDatePicker.

Скажем спасибо создателям этой надстройки и тем, кто доработал ее и перевел на русский язык! Надстройка упрощает ввод дат в ячейки листа Excel.

После установки этой надстройки в контекстное меню (щелчок правой кнопки мыши) добавляется новый пункт «Выбрать из календаря», а при выделении ячеек, содержащих дату, справа от ячейки отображается значок календаря.

бесплатно скачать надстройку samradDatePicker для быстрого ввода дат

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

Как вставить в ячейку статическую, фиксированную, не обновляемую дату?

Выделите в окне открытого листа ячейку, в которую требуется вставить текущую дату, и нажмите сочетание клавиш Ctrl+; (точка с запятой). При нажатии этого сочетания клавиш делается своего рода «снимок» с текущей даты и вставляется в ячейку. Введенная таким способом дата не изменяется с течением времени, а остается фиксированной.

Как вставить в ячейку текущую автоматически обновляемую дату?

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

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

Источник: http://macros-vba.ru/nadstrojki/excel/93-format-datyi-v-excel-i-nadstrojka-kalendar

Трюк №38. Трюки с возможностями даты и времени в Excel

Вставка времени в excel

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

По умолчанию в Excel используется система дат 1900.

Это означает, что числовое значение, лежащее в основе даты 1 января 1900 года, равно 1, у 2 января 1900 года — 2 и так далее. В Excel эти значения называются последовательными значениями, позволяющими использовать даты в вычислениях. Формат времени очень похож, но Excel обрабатывает время как десятичные доли, где 1 — это 24:00, или 00:00.

Числовое значение 18:00 (в русской версии) равно 0,75, так как это три четверти от 24 часов.

Чтобы узнать числовое значение даты или времени, отформатируйте ячейку, содержащую значение, форматом Общий (General). Например, у даты и времени (в английской версии) 3/July/2002 3:00:00 РМ числовое значение 37440.625, где число после десятичной точки представляет время, а 37440 — последовательное значение для 3 июля 2002 года.

Сложение за пределами 24 часов

При помощи функции СУММ (SUM) или просто знака плюс время можно складывать. Таким образом, =SUM(A1:A5) даст нам общее количество часов в диапазоне А1 :А5, если эти ячейки содержат допустимые значения времени. Однако если Excel не дано никаких специальных указаний, он не складывает время за пределами 24 часов.

Это происходит потому, что, когда значение времени превышает 24 часа (настоящее значение равно 1), оно переходит в новый день и отсчет начинается заново. Чтобы заставить Excel не переходить в новый день после каждых 24 часов, можно использовать формат ячеек 37:30:55 или пользовательский формат [ч]:MM:cc([h]:mm:ss).

Схожий формат можно применять для получения общего количества минут или секунд, Чтобы узнать полное количество минут, когда время равно 24:00, отформатируйте ячейку как [м] ([m]), и вы получите 1440. Чтобы получить общее количество секунд, используйте пользовательский формат [с] ([s]) и вы получите 86400.

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

Если вы хотите использовать фактические значения времени в других вычислениях, помните о следующих «магических» числах: 60 — 60 минут или 60 секунд; 3600 — 60 секунд * 60 минут; 24 — 24 часа; 1440 — 60 минут * 24 часа; 86400 — 24 часа * 60 минут * 60 секунд.

Вооружившись этими магическими числами и предыдущей информацией, намного проще манипулировать временем и датами. Взглянем на следующие примеры и посмотрим, что они обозначают (предполагаем, что время записано в ячейке А1). Если у вас есть число 5.50 и вы хотите получить 5:30 или 5:30 a.m., используйте следующую формулу: =А1/24. Необходимо указать подходящий формат!

Если время должно выглядеть как 17:30 или 5:30 p.m., используйте следующую формулу: =(А1/24)+0.5.

Чтобы получить противоположное значение, то есть десятичное время из настоящего времени, воспользуйтесь формулой =А1*24.

Если в ячейке содержится настоящая дата и настоящее время (например, 22/Jan/03 15:36), а вы хотите получить только дату, используйте следующую формулу: =INT(A1), в русской версии Excel =ЦЕЛОЕ(А1).

Чтобы получить только время: =A1-INT(A1), в русской версии Excel =А1-ЦЕЛОЕ(А1) или =MOO(A1;1), в русской версии Excel =OCTAT(A1;1). И вновь необходим подходящий формат.

Чтобы найти разность между двумя датами, воспользуйтесь формулой =DATEDIF(A1;A2;»d»), где А1 — это более ранняя дата. Получим количество дней между двумя датами. В качестве результата можно также указать «m» или «у», то есть месяцы или годы. (В действительности функция DATEDIF в Excel 97 не документирована и является функцией Lotus 123.)

Если более ранние дата или время неизвестны, помогут функции МИН (MIN) и МАКС (МАХ). Например, чтобы наверняка получить правильный результат, можно воспользоваться такой функцией: =DATEDIF(MIN(Al;A2); MAX(Al,A2),»d»), в русской версии Excel: =DATEDIF(MИН(Al;A2);MAKC(A1;A2);»d»).

При работе со временем может также понадобиться учитывать начальное и конечное время. Например, начальное время — это 8:50 p.m. в ячейке А1, а конечное время — 9:50 a.m. в ячейке А2.

Если вы вычтете начальное время из конечного (=А2-А1), получите в ответе ######, так как Excel по умолчанию не работает с отрицательными значениями времени.

Подробнее о том, как работать с отрицательными значениями времени, — в разделе «Трюк № 74. Отображение отрицательных значений времени».

Иначе это ограничение можно обойти двумя способами, гарантировав положительный результат: =MAX(A1;A2)-MIN(A1;A2), в русской версии Excel =МАКС(А1;А2)-МИН(А1;А2) или =A1-A2+IF(A1>A2,1), в русской версии Excel =А1-А2+ЕСЛИ(А1>А2;1).

Можно также приказать Excel прибавить любое количество дней, месяцев или лет к любой дате: =DATE(YEAR(A1)+value1;MONTH(Al)+value2;DAY(Al)+value3), в русской версии Excel =ДАТА(ГОД(А1)+value1;,МЕСЯЦ(А1)+value2;ДЕНЬ(А1)+value3).

Чтобы добавить один месяц к дате в ячейке А1, воспользуйтесь формулой =DATE(YEAR(A1);MONTH(A1)+1;DAY(AD), в русской версии Excel =ДАТА(ГОД(А1);МЕСЯЦ(А1)+1;ДЕНЬ(А1)).

В Excel реализовано и несколько дополнительных функций, являющихся частью надстройки Analysis ToolPak.

Выберите команду Файл → Надстройки (File → Add-Ins) и установите флажок Пакет анализа (Analysis ToolPak) и, если появится сообщение с вопросом, нужно ли установить эту надстройку, ответьте согласием.

Станут доступны дополнительные функции, такие, как ДАТАМЕС (EDATE), КОНЕЦМЕСЯЦА (EMONTH), ЧИСТРАБДНИ (NETWORKDAYS) и WEEKNUM. Все эти функции можно найти в категории Дата и время (Date & Time) диалогового окна мастера функций. Их легко применять, сложнее узнать, что эти функции существуют, и привлечь их к делу.

Настоящие даты и время

Иногда в таблицах с импортированными данными (или данными, введенными неправильно) даты и время отображаются как текст, а не как настоящие числа.

Это можно легко распознать, немного расширив столбцы, выделив столбец, выбрав команду → Ячейки → Выравнивание (Home → Cells → Alignment) и для параметра По горизонтали (Horizontal) выбрав значение По значению (General) (это формат ячеек по умолчанию).

Щелкните кнопку ОК и внимательно просмотрите даты и время. Если какие-либо значения не выровнены по правому краю, Excel не считает их датами.

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

Не снимая выделение столбца, выберите команду → Специальная вставка → Значение → OK (Home → Paste Special → Value → Add).Tenepb Excel будет преобразовывать все текстовые даты и время в настоящие даты и время.

Возможно, вам придется еще раз изменить форматирование. Еще один простой способ — ссылаться на ячейки так: =А1+0 или =А1*1.

Ошибка даты?

Excel ошибочно предполагает, что 1900 год был високосным годом (Добавим, он был последним годом XIX века, а не первым XX). Это означает, что внутренняя система дат Excel считает, что существовал день 29 февраля 1900 года, тогда как его не было! Самое невероятное — Microsoft сделала это намеренно, по крайней мере, они так утверждают.

Источник: http://Excel2010.ru/hack-38.html

Самая полезная информация
Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!: