Мне нужно создать таблицу Excel, которая вычисляет ежедневное время тренировок. каждая строка имеет следующие поля: Дата, Расстояние, Время и Минуты/км.
Моя основная проблема заключается в том, что я хочу отображать время в формате mm:ss
. Например: 24 мин и 3 сек должны быть 24:03
, но «Excel» превращает их в 00:03:00 (3 минуты после полуночи). Я пытался настроить «специальное форматирование», но все же «Excel настаивает» на форматировании часов. Более того, обучение может длиться более 60 минут, и я все еще хочу, чтобы оно выполнялось только как mm:ss
Мой второй вопрос: после того, как я закончу с проблемой форматирования, каков простой способ вычисления времени/расстояния?
Excel 2007: как отображать формат mm:ss не как DateTime (например, 73:07)?
Ответы (7)
введите значения как 0:mm:ss
и отформатируйте как [m]:ss
так как теперь это в минутах и секундах, простая арифметика позволит вам рассчитать вашу статистику
0:74:03
, и он отобразил его, как и ожидалось - 74:03
- person SeanC; 21.06.2012
Excel показывает 24:03 как 3 минуты, когда вы форматируете его как время, потому что 24:03 совпадает с 00:03 (по военному времени).
Используйте общий формат для добавления времени
Вместо форматирования времени используйте Общий формат и следующую формулу:
=number of minutes + (number of seconds / 60)
Пример: в течение 24 минут и 3 секунд:
=24+3/60
Это даст вам значение 24,05.
Сделайте это для каждого периода времени. Допустим, вы вводите эту формулу в ячейки A1
и A2
. Затем, чтобы получить общую сумму прошедшего времени, используйте эту формулу в ячейке A3
:
=INT(A1+A2)+MOD(A1+A2,1)
Преобразование обратно в минуты и секунды
Если вы поместите =24+3/60
в каждую ячейку, вы получите значение 48,1 в ячейке A3
.
Теперь вам нужно преобразовать это обратно в минуты и секунды. Используйте следующую формулу в ячейке A4
:
=MOD(A3,1)*60
Это берет десятичную часть и умножает ее на 60. Помните, что вначале мы делили на 60, поэтому, чтобы преобразовать ее обратно в секунды, нам нужно умножить.
Вы также могли бы сделать это отдельно, т.е. в ячейке A3 использовать эту формулу:
=INT(A1+A2)
и эта формула в ячейке A4
:
=MOD(A1+A2,1)*60
Вот скриншот, показывающий окончательные формулы:
mm+s/60
, где mm
— количество минут, а s
— количество секунд. Если вы введете 24:03
и используете Left
, вы получите 1
, а не 24
.
- person JimmyPena; 20.06.2012
Чтобы облегчить жизнь при вводе нескольких дат/времени, можно использовать пользовательский формат, чтобы исключить необходимость вводить двоеточие и начальный «час» 0. Однако для этого требуется сохранить второе поле для числовой даты, поскольку отображаемая дата из пользовательского формата находится в базе 10.
Отображение числа в виде времени (без двоеточий, но без преобразования времени)
Для отображения времени на листе и для их ввода без ввода двоеточия установите пользовательский формат ячейки и используйте:
0/:00
Затем введите свое время. Например, если вы хотите ввести 62:30, вы просто наберете 6230, и ваш пользовательский формат визуально вставит двоеточие через 2 десятичных знака справа.
Если вам нужно только отображать время, остановитесь здесь.
Преобразование числа во время
Если вам нужно иметь возможность вычислять время, вам нужно будет преобразовать его из десятичной системы в формат времени.
Это можно сделать с помощью следующей формулы (измените A2
на соответствующую ссылку на ячейку):
=TIME(0,TRUNC(A2/100),MOD(A2,100))
=TIME
запускает преобразование числа во время- Нам не нужны часы, поэтому введите
0,
в начале формулы, так как формат всегдаhh,mm,ss
(чтобы отображались часы и минуты вместо минут и секунд, поставьте 0 в конце формулы). - Для минут
TRUNC(A2/100),
отбрасывает две крайние правые цифры. - В течение секунд
MOD(A2,100)
сохраняет две крайние правые цифры и отбрасывает все, что находится слева.
Приведенная выше формула была найдена и адаптирована из этой статьи: PC Mag.com - Простой ввод даты и времени в Excel
Кроме того, вы можете пропустить пользовательское форматирование 0/:00
и просто ввести свое время в ячейку, на которую будет ссылаться край видимой рабочей области, или на другой лист, как для пользовательского форматирования (например: 6230 для 62:30).
Затем измените формат отображения ячеек с формулой на [m]:ss
, как предложил @Sean Chessire.
Вот снимок экрана, чтобы показать, что я имею в виду.
Если вы используете данные, вводимые вручную, вы можете ввести свои данные как mm:ss,0
или mm:ss.0
в зависимости от выбранного вами языка/региона вместо 00:mm:ss
.
Вам нужно указать формат ячейки как [m]:ss
, если вы хотите видеть все минуты в формате секунд вместо формата часов, минут, секунд.
как текст:
=CONCATENATE(TEXT(cell;"d");" days ";TEXT(cell;"t");" hours ";MID(TEXT(cell;"hh:mm:ss");4;2);" minutes ";TEXT(cell;"s");" seconds")
5. В поле «Формат ячеек» выберите «Пользовательский» в списке «Категория». 6. В поле Тип в верхней части списка форматов введите [h]:mm;@ и нажмите OK. (Это двоеточие после [h] и точка с запятой после mm.) Затем вы можете добавить часы. Формат появится в списке типов в следующий раз, когда он вам понадобится.
От MS работает хорошо.
http://office.microsoft.com/en-us/excel-help/add-or-subtract-time-HA102809662.aspx
Один из удобных приемов ввода прошедшего времени в Excel состоит в том, чтобы поставить два нуля и двоеточие перед количеством минут, подробности следуют далее. Для операций копирования и вставки в Excel, не беспокоясь о форматировании, можно использовать формат 00: XX: XX, где XX — две цифры в сумме ‹ 60. В этом случае Excel будет отображать 0: XX: XX в содержимом ячейки. отображается и сохраняет данные как 12:XX:XX AM. Если вставить данные в формате 00:XXX:XX в Excel или 00:XX:XX, где XX > 59, это будет преобразовано в долю дня.
Например, 00:121:12 становится 0,0841666666666667, что, если умножить на количество секунд в сутках, 86 400, дает 7272 с. Далее, 00:21:12 по умолчанию будет показывать 0:21:12, сохраненное как 00:21:12. Наконец, 00:21:60 становится 0,01527777777777778, что также составляет долю дня.
Это предложение сделано просто для того, чтобы не беспокоиться о конкретном форматировании в Excel и позволить программе беспокоиться об этом. Обратите внимание, что для данных Excel, отформатированных внутри как 12:XX:XX AM, можно использовать только определенные команды Excel, например, можно взять среднее значение. Однако вычитание будет работать только тогда, когда результатом является положительное число. Таким образом, преобразование времени в секунды, доли дня или другие действительные числа предлагается для доступа к более полному охвату арифметических операций.
Например, если у вас есть столбец со смешанными форматами времени или время, которое является отрицательным и не будет отображаться, если изменить форматирование чисел на Общее, все время будет преобразовано в доли дня.