Как быстро перейти к ячейке с текущей датой в Excel?

Excel works!

Еще раз привет. Как обычно начну статью с того, что скажу — часто встречаюсь с каким-нибудь незнанием со стороны пользователей и сейчас расскажу об этом. Банально, но действительно так и приходят идеи о статье. Сегодня хочется написать отдельный текст про работу со временем в Excel. Как можно быстро обработать временные данные? Как перевести время в число в Excel? Как не запутаться в этом — читаем в тексте!

Время и число в Excel. =СЕГОДНЯ()

Сразу скажу что дата и время в Excel — это в первую очередь числовые данные. И только в определенном формате эти числа выглядят как время или дата.

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

Любое значение дата_время в Excel — это число преобразованное в нужный формат числа при помощи опции Формат ячеек!

Приведем пример. Вам нужно задать сегодняшнюю дату? Пожалуйста! Записываем функцию =СЕГОДНЯ() — без аргумента в скобках. Она возвращает сегодняшнее число. Как правило, сразу в этой ячейке будет дата в правильном формате.

В таком случае, как это выглядит, если данные перевести в числовой формат? Число выглядит как 42544.00.

В формате Время (см. первое изображение) значение будет равно, например, 21:00. Значения чисел идентичны.

Как же складывать и вычитать такие данные? Как и обычные числа! Если вы хотите, чтобы dato_time отображалось в ячейке, просто добавьте цифры и установите соответствующий формат. Как в примере:

То же самое относится и к вычитанию данных друг из друга.

Следующим шагом будет знакомство с хитростями системы дата_время.

Отделить дату от времени и наоборот в Excel

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

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

Используйте формулу для определения времени из date_time

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

Как перевести время в число в Excel? Функции ДЕНЬ, ЧАС, МИНУТЫ

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

Чтобы преобразовать 3:30 в три с половиной часа, мы должны использовать формулу.

Метод прост: находим количество полных часов с помощью функции HOUR, затем находим количество полных минут и делим на 60, чтобы получить количество минут в часе. Готово!

Так же, как верно подсказали мне в комментариях, можно умножить 3:30 (в ячейке A1) на 24 — получится числовое значение времени.

Если вы хотите подсчитать количество дней во времени (например, 33:30:00), используйте функцию ДЕНЬ.

Важно! Не забудьте перевести ячейку в числовой формат, иначе после выполнения формулы, или в ячейке будет выдаваться 0:00:00
Это будет удобно при работе с большим объемом данных, вычислениями со временем или группировкой данных в сводной таблице

Функция СЦЕПИТЬ (CONCATENATE)

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

Здесь между ячейками вставлены пробелы (» «) и в конце дорисовывается восклицательный знак.

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

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

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

Вставить амперсанд сложно — необходимо переключиться на английскую раскладку, а затем нажать Shift + 7. Поэтому рекомендуется использовать специальную комбинацию — удерживая клавишу Alt, нажмите 3 и 8 на цифровой клавиатуре. Вы сохраните рассудок, если сделаете это.

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

Функция СЦЕПИТЬ для большого количества ячеек

Один из вариантов заключатся в том, чтобы в качестве ссылки на ячейки указать массив данных. Следует сразу отметить, что данные могут располагаться по горизонтали или вертикали. Если данные расположены в одной строке, то делаем следующее. Для примера, отдельные по ячейкам слова находится в 5-й строке. Теперь в пустой ячейке указываем весь диапазон для соединения и через амперсанд (&) добавляем пробел (» «).

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

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

Данные могут быть расположены не по горизонтали, (как в примере выше), а по вертикали. В этом случае в получаемом массиве значения будут разделены двоеточием. Придется сделать один лишний шаг, чтобы заменить их на точку с запятой (через Найти и заменить, горячая клавиша Ctrl + H, либо еще быстрее — использовать функцию ТРАНСП, чтобы из горизонтального массива сделать вертикальный).

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

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

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

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

Соединение текста и даты

И еще один трюк с формулой СЦЕПИТЬ. Довольно часто приходится соединять текст с датой. Дата, как известно, это простое порядковое число (отсчет начинается с 1 января 1900 года), которому придается нужный формат даты. Если же соединить напрямую текст и дату, то на выходе получится указанный текст и простое число. Возьмем, например, слово «Сегодня » (не забываем добавить пробел в конце) и функцию СЕГОДНЯ (всегда возвращающую текущую дату).

Как вы можете видеть, это не сработало.

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

Формат ДД.ММ.ГГГГ определяет, как должна отображаться дата. Если, например, указан формат DD.MM.YYYYY, то дата будет отображаться следующим образом: 03.09.2015 (год состоит из 4 цифр).

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

Я всегда знал текущую дату и день недели. Кроме того, содержимое ячейки состоит только из одной функции TODAY.

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

P.S. Все вышеперечисленные проблемы были исправлены в новых функциях Excel — SCEP и CONNECT.

Как отображать текущую дату и время в Excel и Google Таблицах

Если вам нужно отображать текущую дату и время в Excel или Google Таблицах таблицу, вы можете сделать это быстро. Существует несколько способов отображения текущей даты и времени в электронной таблице. Помимо использования сочетаний клавиш, самый простой способ — использовать СЕЙЧАС ЖЕ и СЕГОДНЯ функции. Они совместимы с Google Таблицами, а также с Microsoft Excel.

Отображение текущей даты и времени в Excel и Google Таблицах

Чтобы отобразить текущую дату и время в Excel и Google Spreadsheets

1. Использование ярлыка
2. Использование функций СЕГОДНЯ и ДЕНЬ

Вот все, что вам нужно знать об этих процессах.

1]Используйте сочетание клавиш

Эти два сочетания клавиш позволят вам вставить текущую дату и время в любую ячейку электронной таблицы. Неважно, используете ли вы Google Spreadsheets или Microsoft Excel, вы можете использовать эти сочетания клавиш.

Чтобы добавить дату, выберите ячейку и нажмите эти кнопки — Ctrl +;

Чтобы добавить текущее время, выберите ячейку и нажмите эти кнопки — Ctrl + Shift +;

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

• Нажмите Ctrl +;
• Нажмите пробел
• Нажмите Ctrl + Shift +;

2]Используйте функции СЕЙЧАС и СЕГОДНЯ

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

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

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

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

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

После завершения внесения изменений нажмите OK, чтобы сохранить их.

Форматирование даты и времени в Google Таблицах

Как и в Microsoft Excel, вы также можете изменить форматирование даты и времени в Google Таблицах. Для этого выберите ячейку, в которой видны дата / время, перейдите в Формат> Числои выберите форматирование в соответствии с вашими потребностями.

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

Для этого перейдите в Файл> Настройки таблицы, и переключитесь на Расчет таб. После этого выберите что-нибудь между этими двумя вариантами —

• При изменении и каждую минуту
• При изменении и каждый час.

После подтверждения изменений нажмите кнопку Сохранить настройки.