Excel: не удается настроить пользовательский формат чисел на определенную дату и время

Question

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

У меня есть эти даты (несколько примеров):

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

Answer 1

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

Поэтому вам нужно преобразовать данные в даты, самое быстрое решение - использовать функцию: =DATEVALUE(A2)+TIMEVALUE(A") . Теперь у вас будет значение в ячейке, просто нужно отформатировать его в нужный формат.

ПРИМЕЧАНИЕ: это будет работать, только если формат даты в ваших региональных настройках подобен month/day/year .
В противном случае вам необходимо:

• изменить формат даты, выполнить преобразование, преобразовать расчеты в значения и изменить формат даты на исходный
• используйте формулы, чтобы изменить порядок частей даты (например, советы здесь)

Answer 2

Нет ничего плохого в шаблоне или формуле, которую вы используете. Формула работает с несколькими форматами даты, которые я пробовал.

Убедитесь, что в начале значений даты в B и D. нет лишних пробелов или других невидимых символов.

Answer 3

Предполагая, что ваши значения фактически сохранены (не отформатированы) как даты, вы можете просто сделать простое вычитание, используя этот числовой формат:

d"d" hh"h" mm"m"

Answer 4

Поэтому я "решил" это, изменив локаль моего компьютера на en_US , чтобы формат m/d/yyyy h:mm am/pm был распознан.

После этого мне удалось сменить локаль на настоящую, и она все еще распознает нужный мне формат даты.