Excel: не удается настроить пользовательский формат чисел на определенную дату и время
Некоторое время я проходил через это, и для конкретного формата даты и времени, который у меня есть, я не могу заставить Пользовательский Формат Чисел правильно подобрать дату.
У меня есть эти даты (несколько примеров):
6/21/2013 6:40 PM
5/28/2013 5:51 AM
6/11/2014 10:03 AM
12/3/2013 2:40 PM
И этот пользовательский формат чисел:
m/d/yyyy h:mm am/pm
На мой взгляд, следует выяснить, что это дата, а затем мне нужно сделать некоторые различия во времени между двумя датами в этом конкретном формате. У меня есть сотни таких дат, поэтому ручное редактирование их в других форматах не является хорошим вариантом.
Например, я хочу сделать что-то вроде этого с датами позже:
=INT(D3-B3) & "d " & HOUR(D3-B3) & "h " & MINUTE(D3-B3) & "m"
Где D3 и B3 - любые две даты в данном формате и D3 > B3.
Это должно дать мне количество дней, часов и минут между двумя датами, например:
Xd Yh Zm
Я пытался с другим форматом, и он работает нормально, но мне нужно в этом.
Что-то не так с шаблоном, который я использую?
Добавлен пример файла xlsx по адресу https://itmb.co/zlw44
4 ответа
Поэтому я "решил" это, изменив локаль моего компьютера на en_US
, Итак m/d/yyyy h:mm am/pm
формат будет признан.
После этого мне удалось сменить локаль на настоящую, и она все еще распознает нужный мне формат даты.
Нет ничего плохого в шаблоне или формуле, которую вы используете. Формула работает с несколькими форматами даты, которые я пробовал.
Убедитесь, что в начале значений даты в B и D. нет лишних пробелов или других невидимых символов.
Предполагая, что ваши значения на самом деле хранятся (не форматируются) как даты, вы можете просто сделать простое вычитание, используя этот числовой формат:
d"d" hh"h" mm"m"
Даже если ваши данные выглядят как даты, на самом деле вы TEXT
в вашем листе, а не в значениях даты (самый быстрый способ проверить это - увидеть горизонтальное выравнивание, текст выровнен по левому краю, а номера выровнены по правому краю по умолчанию).
Таким образом, вам нужно преобразовать ваши данные в даты, самое быстрое решение заключается в использовании: =DATEVALUE(A2)+TIMEVALUE(A")
функция. Теперь у вас будет значение в ячейке, просто нужно отформатировать его в нужный формат.
ПРИМЕЧАНИЕ: это будет работать, только если формат даты в ваших региональных настройках похож на month/day/year
,
В противном случае вам необходимо:
- изменить формат даты, выполнить преобразование, преобразовать расчеты в значения и изменить формат даты на исходный
- используйте формулы, чтобы изменить порядок частей даты (например, советы здесь)