Cant get my head around this date issue

The cells might be formatted as Date but they don't contain a Date, or a Date/Time. They contain a Text string which, nominally, represents a Date and Time.

You'd need to do some extracting, I think, to get the date out of that.

Hi -

Part of the problem is most of the information in the left hand column is text, rather than a true date. Excel uses serial numbers to represent dates, and then you format the cell the way you want to display it. As a test, highlight the cells in the left column and change the format to Number. The ones that change to a number (e.g., 42601 is August 19) are the only ones that will respond to your TEXT formula and your formatting. I suspect either your copying and pasting operation is somehow pasting as text, OR the raw data isn't numeric either (i.e., mixed text and numbers).

As a workaround, you can use the DATEVALUE and TIMEVALUE functions to take text that looks like a date and time (which is what you have) and convert it into a date number and a time number (which will be a decimal value). Add the two together and you get a date and time number which you can then format to your heart's content.

Hope this helps!

Actually, I lie. Most of the "dates" are not dates. The exception being the shorter examples which have 12:00 AM as the time. They are true dates.

Hey DexterX,

This is one of those things that most of us don't deal with because when we build a sheet it is designed in and for the location in which it is built.
However this is a common issue primarily due to the Regional Settings, which would appear you have discovered.
One method I found that worked for a similar issue was to use a Public Function and do my math all in VBA.
Not only did I take care of a date issue, but by the end of the project there were less than 5 formulas that remained live in the workbook out of thousands... Cutting back on processing time significantly. (Whole different topic)

If you want to test it out - you can add the public function. (Again this works in VBA, using it as a formula in cells will revert to local (regional) settings, so even though you say "DD/MM/YYYY" if coming from 9/2/2016 and it returns 02/09/2016...Excel will recognize it as Feb 9th... Inside VBA however it will store the proper date with formatting to later be used in any formula...

Go to your developer window and enter into Visual basics (Or just hit [ALT]+[F11]
Insert a new module in the project window (Right click on any visible sheet and select Insert>Module

Inside the new window that appears input the following.

Please Login or Register  to view this content.

An example of this inside your code might be something like this

Please Login or Register  to view this content.

OR if you were taking it from a cell on sheet 1 in A1 it would be

Please Login or Register  to view this content.

And while the end result would be 02/09/2016 for a reference looking at today, it would retain its VBA.Format$ for you to utilize.

Sounds like you may be satisfied with modifying your entire PC to live outside the UK superficially, but thought I would offer up another possible workaround. Depends on your ability/Comfort level with VBA though.

Cheers

I know this method gets scoffed at on this forum but without using VBA or formulas, what I've done many times is to highlight the entire column, then using find and replace start with find 0, replace with 0, hit replace all, then repeat through 9. It seems a bit repetitive but it clears out pesky text and replaces them with numbers and has worked every time I've tried it, FWIW. Make sure your settings are for any part of the cell.

Select col A, Data > Text to columns, Next, Next, select Date: MDY, press Finish.

Repeat for col C.

	A	B	C	D	E	F
1	Open Date Time	the month	Close Date Time	Age
2	8/19/16 7:35 AM	Aug	8/22/16 7:10 AM	71 H 35 M		D2: =MROUND([@[Close Date Time]]-[@[Open Date Time]], "0:01")
3	8/19/16 12:00 AM	Aug	8/26/16 6:21 AM	174 H 22 M		Format of D2: [h]" H "m" M"
4	8/23/16 9:26 AM	Aug	8/24/16 4:52 AM	19 H 26 M
5	8/22/16 11:25 AM	Aug	8/24/16 6:54 AM	43 H 28 M
6	8/23/16 8:11 AM	Aug	8/25/16 10:04 AM	49 H 53 M
7	8/19/16 6:01 AM	Aug	8/24/16 4:01 AM	118 H 0 M
8	8/22/16 12:00 AM	Aug	8/25/16 7:16 AM	79 H 16 M
9	3/3/16 12:00 AM	Mar	8/25/16 7:17 AM	4207 H 17 M
10	8/23/16 10:56 AM	Aug	8/26/16 5:24 AM	66 H 29 M
11	8/22/16 5:10 AM	Aug	8/25/16 10:24 AM	77 H 14 M
12	8/16/16 4:40 AM	Aug	8/24/16 6:55 AM	194 H 15 M

I think Regional Settings is a bit of a red herring. You do NOT have a date and time in the cells. You have TEXT which looks, to all intents and purposes, like a date and time. Formatting will have no effect on the data in the cell, nor will arithmetic manipulation. A date and time would have a numeric content something like ddddd.ttttt.... It doesn't it has text and hence the formatting options will have no effect.

For example, 11/09/2016 21:32:00, if formatted as General, would display 42624.8972222222. The 42624 is the number of days starting from 01/01/1900. The 0.8972222222 is a fraction representing part of a day, a 24 hour period.

You can use a formula to extract the "date" part. Then you can use Copy and Paste Special | Values to convert that to Text. And finally, you can use Text to Columns to convert the column to a true date.

Use this formula to extract the date: =LEFT([@[Open Date Time]],FIND(" ",[@[Open Date Time]])-1)

Then select the whole column of data and use Copy and Paste Special | Values to paste the values over the original formulae

Finally, use Text to Columns to convert the (still wrong format) "date" to true dates. See the updated example for detailed instructions.

@shg: Text to Columns on Column A didn't work for me. Hence my more convoluted approach. Could it be that Regional Settings does have a bearing on it given that you're in the Great State of Texas and the "dates" are in US format?

@ TMS: Thanks for the followup.

@ Dex: I have no other suggestion.

It's horrible, but I think this will work:

To get the 'Open Date Time' as a combined date and time, use this:

Formula:

Please Login or Register  to view this content.

To get the 'Close Date Time' as combined date and time, use the same with [Close Date Time] instead of [Open Date Time]:

Formula:

Please Login or Register  to view this content.

Format both columns as date and time (select 'Custom' then choose the one you want - I used d mmm yy\ hh:mm )

I think that should work with your combination of UK/US formats / imported/manually typed dates/times, though I'm not 100% sure as it depends on exactly how your system is set-up.

The difference between the date/times can be found using this (the formulae above are in columns titled 'DateTimeOpen' and 'DateTimeClose':

Formula:

Please Login or Register  to view this content.

I've attached a file showing these formulae working - they're in columns Y,Z,AA - the other columns (in small blue text) were the 'working' columns I used to develop them, in case you're interested


Hope that helps!

That is impressive, even if horrible.