Excel: Cannot get Custom Number Format to work for specific date and time

Question

I've been going over this for a while and for the specific date + time format I have, I cannot get the Custom Number Format to pick the date up properly.

I have these dates (several examples):

6/21/2013 6:40 PM
5/28/2013 5:51 AM
6/11/2014 10:03 AM
12/3/2013 2:40 PM

And this Custom Number Format:

m/d/yyyy h:mm am/pm

The way I see it, it should figure out it is a date, and then I need to do some time differences between two dates in this specific format. I have hundreds of dates like this, so manually editing them to other formats is not a good option.

For example, I want to do something like this with the dates later on:

=INT(D3-B3) & "d " & HOUR(D3-B3) & "h " & MINUTE(D3-B3) & "m"

Where D3 and B3 are any two dates in the given format and D3 > B3.

That should give me the number of days, hours and minutes between the two dates, like this:

Xd Yh Zm

I tried with other format and it works OK, but I need it in that one.

Is there anything wrong with the pattern I am using?

Added sample xlsx file at https://itmb.co/zlw44

Answer 1

Even if your data looks like dates, actually you've TEXT in your sheet and not date values (quickest way to check it is to see horizontal alignment, text is left aligned and numbers are right aligned by default).

So you need to convert your data to dates, quickest solution is to use: =DATEVALUE(A2)+TIMEVALUE(A") function. Now you will have the value in the cell, just need to format it to the desired format.

NOTE: this will work only if date format in your regional settings is like month/day/year.
Otherwise you either need to:

• change your date format, do the conversion, convert your calculations to values and change date format to the original one
• use formulas to change order of date parts (tips e.g. here)

Answer 2

There is nothing wrong with the pattern or the formula you are using. The formula works with the several date formats that I tried.

Verify that there are no extra spaces or other non-visible characters at the beginning of the date values in B and D.

Answer 3

Assuming your values are actually stored (not formatted) as dates, you can just do a simple subtraction as use this number format:

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

Answer 4

So I "solved" this by changing my computer's locale to en_US, so the m/d/yyyy h:mm am/pm format would get recognized.

After that I was able to change the locale to my real one and it still recognizes the format I need as a date.