Как изменить формат чч: мм: сс в электронных таблицах

Question

В настоящее время у меня есть набор данных, который "мм: сс" и должен оставаться / рассчитываться таким образом.

Проблема заключается в том, что Microsoft Excel, Google Spreadsheets и OpenCalc все интерпретируют "mm:ss" как "чч: мм", а для добавления оскорбления к травме добавляют ":ss" (в форме ": 00") ко всему, что я копирую в Excel/GSheets/Calc.

1. Я не нашел способа удалить это, кроме как сначала использовать "Поиск и замена":"затем" 00, а затем, наконец, применить пользовательский формат 0:00:00 или 0 »:" 00 ":« 00. (Я также пытался заставить работать " = Concatenate ", но у меня это не сработало ...)

2. Кроме того, использование «0:00:00» фактически работает, только если у меня уже есть электронная таблица. Когда я копирую исходные данные (из MS OneNote) в ячейку, отформатированную с 0:00:00, у меня не возникает описанных проблем, но скопированные данные по-прежнему содержат «:», поскольку MS OneNote не имеет " Поиск и замена ". Моя единственная идея - сделать дополнительный шаг и скопировать данные в Блокнот и обратно.

Но оба подхода не являются решением, потому что моим следующим шагом должно быть суммирование этих цифр.

Что не работает, потому что любые базовые числа выглядят как "1234", а не «12:34» (двенадцать минут тридцать четыре секунды, то есть я говорю не о 12:34 AM/PM (время), а о продолжительности!) как они должны.

Насколько я понял, для правильного суммирования минут мне нужно использовать пользовательский формат " [h]:mm:ss ", но проблема в том, что мне сначала нужно получить данные (в основном) "0:mm: сс "с текущего" чч: мм: 00 ". Но важно помнить, что на самом деле это не часы! (т.е. /60 не работает, потому что он производит неправильные числа!) "Hh", о котором идет речь, на самом деле "mm", но поскольку Excel /Calc /GSheets заставляет меня интерпретировать данные без какого-либо способа изменить этот параметр, у меня нет выбора.

Я никогда не использовал VBA раньше, но я хочу попробовать это.

Answer 1

Я написал функцию VBA, которая добавит ваши минуты и секунды:

Function addTime(rng As Range) As String
    Dim timeArray As Variant
    Dim sumSec As Integer
    Dim sumMin As Integer
    Dim i As Range

    Dim secStr As String
    Dim minStr As String
    Dim hrStr As String

    sumMin = 0
    sumSec = 0

For Each i In rng
        On Error GoTo Continue
        If i.Value <> "" Then
            timeArray = Split(i.Value, ":")
            sumMin = sumMin + timeArray(0)
            sumSec = sumSec + timeArray(1)
        End If
Continue:
        On Error GoTo 0
    Next i

    secStr = Modulo(sumSec, 60)
    If (secStr < 10) Then
        secStr = "0" & secStr
    End If

    minStr = Modulo((sumMin + sumSec \ 60), 60)
    If (minStr < 10) Then
        minStr = "0" & minStr
    End If

    hrStr = sumMin \ 60

    addTime = hrStr & ":" & minStr & ":" & secStr    
End Function

Function Modulo(a, b)
    Modulo = a - (b * (a \ b))
End Function

Поместите две функции в модуль.

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

1. Сначала щелкните столбец (столбцы), в который вы хотите вставить свои данные.
2. Преобразовать формат в "текст"
3. Вставьте ваши данные
4. Type =addTime(A1:A10) (используя «A1:A10» в качестве примера 10 точек данных)

Answer 2

Если вы импортируете данные mm:ss но Excel обрабатывает их как hh:mm то вы можете разделить на 60, чтобы преобразовать в правильные данные мм: сс.

Чтобы разделить весь диапазон значений времени на 60, вы можете использовать этот метод:

1. Поместите 60 в пустую ячейку и скопируйте эту ячейку
2. Выберите диапазон значений чч: мм
3. Щелкните правой кнопкой мыши и выберите "Специальная вставка" из меню
4. В следующем диалоговом окне под "Операция" выберите "Разделить"> ОК> ESC
5. Переформатируйте в нужный формат мм: сс

Теперь ваши значения будут выглядеть так же, как и раньше, но Excel теперь обрабатывает их как mm:ss как требуется

Эта ссылка показывает рабочий лист со случайными значениями времени в столбце A (отформатированный как [ч]: мм), а затем столбцы B и C имеют значение столбца A, разделенное на 60, но отформатированное двумя различными способами - обратите внимание, как столбец A выглядит идентично столбцу C - нажмите F9 для генерации разных значений времени

Answer 3

Если я правильно понимаю, то ваша проблема

1) как ввести данные чч: сс без Excel, не испортив их

2) как их сложить и получить правильный ответ

Я знаю, что это взлом, но если вы используете формат [ч]: мм, а затем введите свои данные, это будет работать.

Несмотря на то, что вы вводите минуты и секунды, Excel будет выполнять математику, как если бы это были часы и минуты, но численно он даст вам нужный ответ, например, 1:45 + 1:30 = 3:15 и 51:45 + 61:30 = 113:15