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

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

s1m3n

Posted 2015-09-04T11:46:02.333

Reputation: 101

Which ones are D3 and B3? – Raystafarian – 2015-09-04T11:51:32.233

Just any two dates in the given format. – s1m3n – 2015-09-04T11:53:35.883

Well, which ones? How can we troubleshoot the formula to get what you want without the full example? – Raystafarian – 2015-09-04T11:54:27.577

OK, lets say that D3 is 6/21/2013 6:40 PM and B3 is 5/28/2013 5:51 AM – s1m3n – 2015-09-04T11:55:34.363

And what's the expected answer? 24d 12h 49m – Raystafarian – 2015-09-04T12:01:41.090

Yes, expected answer is 24d 12h 49m – s1m3n – 2015-09-04T12:09:42.773

Cannot reproduce - the formula works for me with the custom format. – Raystafarian – 2015-09-04T12:11:51.953

Strange. Added link to sample in Q. – s1m3n – 2015-09-04T12:21:41.843

The number format has nothing to do with the actual value of the cell. What happens if you format those cells as a number? – Kyle – 2015-09-04T16:06:11.410

Answers

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.

s1m3n

Posted 2015-09-04T11:46:02.333

Reputation: 101

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)

Máté Juhász

Posted 2015-09-04T11:46:02.333

Reputation: 16 807

By setting a custom format like "m/d/yyyy h:mm am/pm" am I not changing from text to datetime? I guess it is still considered a text because the farmat is incorrect somehow. – s1m3n – 2015-09-04T13:18:33.217

by setting custom format you change format of numbers (date is also stored as numbers in excel); but it doesn't convert text to number / date! – Máté Juhász – 2015-09-04T13:23:14.173

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.

B540Glenn

Posted 2015-09-04T11:46:02.333

Reputation: 1 045

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"

Kyle

Posted 2015-09-04T11:46:02.333

Reputation: 2 286