=DATEVALUE() does not recognize date

[homie]

Hi,

I have a string that contains both date and time and I am trying to turn it into a date and time that Excel recognizes as such. To do so I have used =Datevalue and =Timevalue to extract the date and time, but it doesn't work. I was thinking it was because I am on a european computer with european settings and so datevalue does not recognize the American style dates in this format: m.d.yyyy. The time is in European format (not AM/PM), so 4:25 PM is written like 16:25. However, 4:25 AM is written like this 4:25 (i.e. without the 0 in front), so the number of characters for the time is not consistent, same is true for the date, i.e. 5/12/2017 is 12th of May 2017, and 6/5/2017 is 5th of June. Character length differs.
Here is an excerpt of my data:

Fiat Deposits	Timestamp	Date	Time
9/25/2015 19:54,5200,NOK	9/25/2015 19:54	#VALUE !	#VALUE !
12/6/2015 17:27,330,NOK	12/6/2015 17:27	12.06.2015	17:27
12/14/2015 17:38,2000,NOK	12/14/2015 17:38	#VALUE !	#VALUE !
12/23/2015 18:05,400,NOK	12/23/2015 18:05	#VALUE !	#VALUE !
5/19/2016 5:28,5000,NOK	5/19/2016 5:28	#VALUE !	#VALUE !
6/13/2016 16:08,500,NOK	6/13/2016 16:08	#VALUE !	#VALUE !
6/22/2016 13:33,9200,NOK	6/22/2016 13:33	#VALUE !	#VALUE !
10/2/2016 4:40,5000,NOK	10/2/2016 4:40	10.02.2016	04:40
12/20/2016 16:04,8198,NOK	12/20/2016 16:04	#VALUE !	#VALUE !
12/25/2016 9:01,700,NOK	12/25/2016 9:01	#VALUE !	#VALUE !
12/28/2016 22:40,1000,NOK	12/28/2016 22:40	#VALUE !	#VALUE !
4/3/2017 16:42,5000,NOK	4/3/2017 16:42	04.03.2017	16:42
5/24/2017 7:32,100000,NOK	5/24/2017 7:32	#VALUE !	#VALUE !

<tbody>
</tbody>

"Fiat Deposits" (colomun A) is string/text. Timestamp (B) = =LEFT(A2;FIND(",";A2)-1)
Date (C) =DATEVALUE(B2)
Time (D) =TIMEVALUE(B2)

I have tried formatting as date and time and general before and after copying both data and formulas. I have tried adding +0. Nothing works. As you can see from the excerpt the datevalue function incorrectly reads dates as dd.mm.yyyy. That is how I want it to show, but the source data in string in colomun A is not written as that, thus the =Datevalue recognizes the wrong date. In the other ones it just gets an #VALUE ! error because what it thinks is suppose to be the month is >12- E.g. 7/15/2017 it would not be able to read because it thinks it is the 7th day of the 15th month, which doesn't exist.

Times: It seems that if Excel does not understand the date, it doesn't understand the time either. If it understands the dates though, it shows the time correctly every time.

P.S. I have tried seperating the date and time part of the string in col B using =LEFT(FIND()) functions, but it doesn't seem to help. Excel still has trouble recognizing the dates in string even if they are seperated out in cells showing just the date.

Please help

 

[MARK858]

Possibly you are hitting a floating point precision issue...

https://support.microsoft.com/en-us...ithmetic-may-give-inaccurate-results-in-excel

 

[oldbrewer]

Before I retired I was a Business Improvement Leader for a very large company in London. The only sensible thing to do, imho, is to get the data formatted to meet your requirements before it is sent to you - or even give you access to the raw data. You have had some big guns trying to help you which demonstrates the need for a "rebuild" rather than add on patch repairs.

 

[homie]

Possibly you are hitting a floating point precision issue...

https://support.microsoft.com/en-us...ithmetic-may-give-inaccurate-results-in-excel

Yes, that is probably it. I find it odd that in my years of using Excel I have never encountered this problem before.

 

[homie]

Before I retired I was a Business Improvement Leader for a very large company in London. The only sensible thing to do, imho, is to get the data formatted to meet your requirements before it is sent to you - or even give you access to the raw data. You have had some big guns trying to help you which demonstrates the need for a "rebuild" rather than add on patch repairs.

Yeah, I tried getting the data formatted to meet my requirements without success. I am not in direct contact with the guy who supplies the data.

 

[oldbrewer]

you need to ask if you can have a meeting with this guy imho........

 

[homie]

you need to ask if you can have a meeting with this guy imho........

He's in America, I'm in Europe. I don't have his contact details either. My friend is hiring this guy, and he's pretty expensive, so I'm guessing my friend wants as few hours billed from him as possible. This is a side project that I'm helping a friend with, so this is not a part of the business of a corporation, i.e. this is not related to my day job.

Even in my day job for a medium sized company I find it sometimes easier to fix things myself than to ask my coworker who supplied the data to fix it. This even though it would take him 5 minutes to get it in a different format and me several hours to work around to fit my model. Because sometimes people are lazy and downprioritize you, and I sometimes have to ask 2-3 times and wait a week for a simple thing to get done. I guess that's just life when you are low on the totem pole in a big company.

 

[oldbrewer]

Timestamp	Date	Time
9/25/2015 19:54
12/6/2015 17:27				col A is text
12/14/2015 17:38
12/23/2015 18:05				because the first is 9/25/2015
5/19/2016 5:28				we must assume the format is
6/13/2016 16:08
6/22/2016 13:33				m or mm /d or dd/yyyy h or hh :mm
10/2/2016 4:40
12/20/2016 16:04								this column
12/25/2016 9:01								has to be
12/28/2016 22:40								UK format
4/3/2017 16:42								as I am in UK
5/24/2017 7:32
								you can make it USA
								format
THIS COLUMN IS TEXT	month	dayhelper	day	year	timehelper	hour	minutes	UK DATE	time
9/25/2015 19:54	9	25/	25	2015	19:54	19	54	25/09/2015	19:54
12/6/2015 17:27	12	6/2	6	2015	17:27	17	27	06/12/2015	17:27
12/14/2015 17:38	12	14/	14	2015	17:38	17	38	14/12/2015	17:38
12/23/2015 18:05	12	23/	23	2015	18:05	18	05	23/12/2015	18:05
5/19/2016 5:28	5	19/	19	2016	5:28	5	28	19/05/2016	05:28
6/13/2016 16:08	6	13/	13	2016	16:08	16	08	13/06/2016	16:08
6/22/2016 13:33	6	22/	22	2016	13:33	13	33	22/06/2016	13:33
10/2/2016 4:40	10	2/2	2	2016	4:40	4	40	02/10/2016	04:40
12/20/2016 16:04	12	20/	20	2016	16:04	16	04	20/12/2016	16:04
12/25/2016 9:01	12	25/	25	2016	9:01	9	01	25/12/2016	09:01
12/28/2016 22:40	12	28/	28	2016	22:40	22	40	28/12/2016	22:40
4/3/2017 16:42	4	3/2	3	2017	16:42	16	42	03/04/2017	16:42
5/24/2017 7:32	5	24/	24	2017	7:32	7	32	24/05/2017	07:32
	every column in above table is a calculated field
	it can be hidden out of sight of course
	you need to reference only the last two columns then
	the last 2 columns are working dates and times

<colgroup><col><col span="7"><col><col span="3"></colgroup><tbody>
</tbody>