Точность даты в Excel и неожиданное отклонение с точностью до 1 с

Question

Эта проблема

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

Воссоздать

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

1. Введите «19/04/2015 13:26:15.456» в ячейку без кавычек (британский формат, поэтому предположительно 19.04.2015 в США); нажмите ввод, чтобы содержимое было принято.
   • Excel решает отформатировать это как "mm:ss.0", представляя это как "26:15.5"
2. Вернитесь к рассматриваемой ячейке, не редактируя ее
   • можно видеть, что он "потерял" точность в строке формул, показав «19/04/2015 13:26:15» без доли секунды. Тем не менее, ячейка по-прежнему показывает «26:15,5», указывая хвостом .5, что точность все еще присутствует.
3. CTRL-1 и измените формат с «мм:сс.0» на «дд / мм / гггг чч:мм:сс.000». Теперь он корректно отображается как «19/04/2015 13:26:15.456» в ячейке (но все еще без 456 в строке формул).
4. Это суть проблемы; теперь нажмите F2, затем нажмите ввод; т.е. "редактировать" ячейку, но не меняя ничего. Точность <1 секунды отбрасывается, и теперь в ячейке отображается «19/04/2015 13:26:15. 000 »

Это означает, что если я отредактирую часть даты в ячейке (то есть изменим апрель на май, или продвинусь на год и т.д.) Или просто случайно дважды щелкните по ней, я каждый раз выбрасываю десятичную точность. Есть ли способ, которым я могу предотвратить это?

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

Answer 1

Если вы настроили ячейку в качестве даты / времени, как показано ниже:

Выберите ячейку и запустите этот короткий макрос:

Sub beforeEDIT()
    Dim s As String

    With ActiveCell
        s = .Text
        .ClearContents
        .NumberFormat = "@"
        .Value = s
    End With
End Sub

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

Когда вы закончите редактирование, запустите этот второй макрос, чтобы преобразовать текст обратно в дату / время.

Sub afterEDIT()
    With ActiveCell
        .NumberFormat = "mm/dd/yyyy hh:mm:ss.000"
        .Value = .Value
    End With
End Sub