=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

 

[Jonmo1]

Try these with the original string in A2

For the Date
=DATE(MID(A2,FIND(" ",A2)-4,4),LEFT(A2,FIND("/",A2)-1),SUBSTITUTE(LEFT(REPLACE(A2,1,FIND("/",A2),""),2),"/",""))

For the Time
=TRIM(MID(A2,FIND(",",A2)-5,5))+0


Should work whether PC is in UK or US date format

 

[Special-K99]

Use this instead, I've swapped the first to TRIM's around

For dates
=(TRIM(MID(SUBSTITUTE(B1," ",REPT(" ",50)),(1-1)*50+1,50))&"/"&TRIM(MID(SUBSTITUTE(B1," ",REPT(" ",50)),(2-1)*50+1,50))&"/"&TRIM(MID(SUBSTITUTE(B1," ",REPT(" ",50)),(3-1)*50+1,50)))+0

 

[AlanY]

@Special-K99
didn't work for the UK date format either
is there a typo there, replace " " with " "

in column B
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"/"," ")," "," "),","," ")

 

[homie]

Edit:
Replly to Special K-99:
Thanks, I am still looking to replicate it, I have to change some things, as we use ; as a parameter change, not ",". I.e. we use semicolons, not commas, to seperate arguments in Excel functions. I got "You have entered too few arguments" error message, and Excel didn't specify where I made an error. Probably I just made a typo. I will try again and get back to you. Thanks for your help

 

[Jonmo1]

You can't just replace all the commas (the actual comma in the original string is used as well)

Here it is with semicolons
For the Date
=DATE(MID(A2;FIND(" ";A2)-4;4);LEFT(A2;FIND("/";A2)-1);SUBSTITUTE(LEFT(REPLACE(A2;1;FIND("/";A2);"");2);"/";""))

And time
=TRIM(MID(A2;FIND(",";A2)-5;5))+0

 

[homie]

Try these with the original string in A2

For the Date
=DATE(MID(A2,FIND(" ",A2)-4,4),LEFT(A2,FIND("/",A2)-1),SUBSTITUTE(LEFT(REPLACE(A2,1,FIND("/",A2),""),2),"/",""))

For the Time
=TRIM(MID(A2,FIND(",",A2)-5,5))+0


Should work whether PC is in UK or US date format

Thanks alot! This did indeed work :D

I figured the problem was some American/European date settings stuff, but did not know how to solve it. This does indeed solve it. I get my data from an American but I work in Europe. Shouldn't these problems be a thing of the past? haha :P

Could you be so kind and explain what these formula actually do? I like to understand my Excel code, so I can solve similar problems in the future

 

[homie]

You can't just replace all the commas (the actual comma in the original string is used as well)

Here it is with semicolons
For the Date
=DATE(MID(A2;FIND(" ";A2)-4;4);LEFT(A2;FIND("/";A2)-1);SUBSTITUTE(LEFT(REPLACE(A2;1;FIND("/";A2);"");2);"/";""))

And time
=TRIM(MID(A2;FIND(",";A2)-5;5))+0

Yes, I didn't replace the commas that should be there, only the argument seperator ones. And it did work!

I was responding to Special-K99 with that post, and as AlanY confirmed, there was a typo in his original post, which he fixed later. So thanks to you too Special-K99 for your solution, there are many roads to Rome, hehe.

 

[Jonmo1]

You're welcome.

Sure, break it out in pieces
=DATE(MID(A2;FIND(" ";A2)-4;4);LEFT(A2;FIND("/";A2)-1);SUBSTITUTE(LEFT(REPLACE(A2;1;FIND("/";A2);"");2);"/";""))
=DATE(Year, Month, Day)

Year = MID(A2;FIND(" ";A2)-4;4)
Find the space, subract 4, return 4 character string.

Month = LEFT(A2;FIND("/";A2)-1)
Return string from beginning of A2 up to the / -1

Day = SUBSTITUTE(LEFT(REPLACE(A2;1;FIND("/";A2);"");2);"/";"")
This was the tricky one.
Find the /
Replace (remove) all characters from the beginning of A2 up to that /
Return the Left 2 characters of that result
Since the Day might only be 1 digit, this would then have a / at the end of that result.
So use substitute to remove it.

Combine them all into the date function.


Similar for the TIME
=TRIM(MID(A2;FIND(",";A2)-5;5))+0

Find the comma, subtract 5, return string of 5 characters.
Since the hour may be only 1 digit, this will end up with a space at the beginning.
Use TRIM to remove that space.
+0 to convert the TEXT string into a real time value.


Hope that helps.

 

[MARK858]

Apologies, posting issues and created a duplicate post. See next post.

 

[MARK858]

Thanks alot! This did indeed work :D

I figured the problem was some American/European date settings stuff, but did not know how to solve it. This does indeed solve it. I get my data from an American but I work in Europe. Shouldn't these problems be a thing of the past? haha :P

Easiest solution if you are dealing with a real person and not a download is to ask them if they could format the cells as numbers rather than dates when they send it to you.
Then format the cells as dates at your end.