=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

 

[homie]

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.

Thanks, I get what you did now. I honestly never would have been creative enough to get the DAY part of that equation. I have never had the need for Substitute, Replace or Trim, so they are not in my mental "toolbox".

One thing though: If you got the time without using the =TIME() function, but just extracted the time part of the string and added +0 to make it a number, could you not have done the same thing for the date? And thus made the formula shorter. I.e. extracted the date from the string and added +0 to make it a number, without using the =DATE() function?

 

[Jonmo1]

DateString+0 would only work if that date string is in a valid format.
In this case it's not, because it's in m/d/yyyy format, but your pc is expecting d/m/yyyy

So a date like say 9/25/2017 would fail, because it's trying to use 25 as the month, but of course there's no such month.

 

[homie]

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.

Thanks, I know, and I ask my intermediary to ask the source for this. I do not have direct contact with the guy. I get all the info in one cell as a string. This includes date, time, currency amount, currency type, cryptoprice, etc...With different lengths for time, date, currency amounts, etc...He couldn't have made it less usable for me if he tried :P

 

[homie]

Dear Excel geniusus, I have encountered a new problem with this data set.

VLOOKUP PROBLEM
The vlookup function does not find all values, even though they are in the data set. It seems to find roughly 2/3 of the values, the rest get #N/A. I have browsed the web for solutions to vlookup problems, but none of them have worked.

This is what I am trying to do: Find the historical bitcoin price by looking up the time stamp in a table of bitcoin prices.
This is what I have tried:
1. =Type() to make sure lookup values are in the same format (number. Date+time value to be precise)
2. =Len() to make sure the values are the same length, so I can be sure there are no hidden characters I am missing. They were the same length
3. Format the timestamp to a number to make sure the numbers are exactly the same. Usual format is this: dd.mm.yyyy tt:mm. I changed it to numbers for some of the values where I got #N/A just to see if they were the same. Numbers looked like this 42264,6875. The numbers were the same until the last decimal, which I expanded until it was a bunch of zeros, i.e. no more decimal values.

So what is going on here? Why is vlookup not finding some of these values?

Here is an excerpt of the table where I want the price to be fetched from, according to timestamp "Norway time (GM+1)":

D	E
Norway time (GMT+1)	BTC price

<colgroup><col><col></colgroup><tbody>
</tbody>

28.09.2015 17:50	238.92392885
28.09.2015 17:51	239.07515804
28.09.2015 17:52	239.12587678
28.09.2015 17:53	238.90226604
28.09.2015 17:54	238.90776803
28.09.2015 17:55	238.92049082
28.09.2015 17:56	238.91537185
28.09.2015 17:57	238.95559683
28.09.2015 17:58	239.07257138
28.09.2015 17:59	239.08666537

<colgroup><col><col></colgroup><tbody>
</tbody>

Both NorwayTime and BTCprice are values. Norwaytime is a number (datevalue) and the price is text.

Here is an excerpt of the table with the timestamp value I am looking up in the other table and where I can the price to be shown:
You can ignore the colomuns P-S, they are of no significance.

O	P	Q	R	S	T
TimeStamp	Date	Month	Year	Fulldato	Bitcoinkurs
16.09.2015 17:51	16	9	2015	1692015	228
16.09.2015 00:09	16	9	2015	1692015	230.35318075
16.09.2015 14:34	16	9	2015	1692015	228.99
16.09.2015 00:15	16	9	2015	1692015	230.35192762
16.09.2015 18:19	16	9	2015	1692015	228.4
17.09.2015 17:59	17	9	2015	1792015	234.91419594
17.09.2015 05:30	17	9	2015	1792015	230.1214426
42264,68750	17	9	2015	1792015	#N/A
17.09.2015 17:57	17	9	2015	1792015	234.96747986
17.09.2015 07:40	17	9	2015	1792015	230.32
17.09.2015 06:50	17	9	2015	1792015	230.46400404
18.09.2015 15:05	18	9	2015	1892015	NaN
20.09.2015 21:23	20	9	2015	2092015	232.65
20.09.2015 06:02	20	9	2015	2092015	232.2673577
21.09.2015 16:21	21	9	2015	2192015	228.20106948
21.09.2015 17:05	21	9	2015	2192015	228.82916458
21.09.2015 17:08	21	9	2015	2192015	228.82183818
21.09.2015 13:00	21	9	2015	2192015	228
21.09.2015 17:14	21	9	2015	2192015	228.71928872
21.09.2015 17:14	21	9	2015	2192015	228.71928872
21.09.2015 15:17	21	9	2015	2192015	229.14
21.09.2015 16:31	21	9	2015	2192015	#N/A
21.09.2015 19:31	21	9	2015	2192015	#N/A
21.09.2015 06:46	21	9	2015	2192015	#N/A
21.09.2015 01:52	21	9	2015	2192015	232.49989005
22.09.2015 12:14	22	9	2015	2292015	#N/A
22.09.2015 12:18	22	9	2015	2292015	232.41518291
22.09.2015 12:59	22	9	2015	2292015	#N/A
22.09.2015 13:22	22	9	2015	2292015	231.86378556
23.09.2015 12:49	23	9	2015	2392015	229.93

<colgroup><col><col span="4"><col></colgroup><tbody>
</tbody>

Formulas in the table above:
Timestamp (O) is a hard number.
Bitcoinkurs (T)=VLOOKUP(O5;'coinbase_BTC price_2014-may2017'!D:E;2;FALSE)
The first table I listed above is in another sheet named 'coinbase_BTC price_2014-may2017'

Please help, I cannot figure why it vlookup cannot find the value sometimes :/

 

[homie]

I was thinking, maybe there is some limitation of the vlookup functions on how many rows it can search. In the table it is searching there are roughly 1 050 000 rows of data. However, both tables are in ascending order according to the timestamp, so that fails to explain why the vlookup functions fetches some values but not others. It seems to be random, I cannot see a pattern. There probably is a pattern, but I do not see it.

 

[homie]

Edit:
Some of the values that return #N/A are different lengths according to the =LEN() function, some are not. Even the ones who show as different length though (10 and 16 characters) still show the exact same number when I format the timestamp cell to show as a number with decimals: 42268,8131944444
Funnily enough, this number (date&time) returned as 16 characters when I used =LEN() in the table where I am looking up the data, the table in sheet 'coinbase_BTC price_2014-may2017'. The very same number returned as only 10 characters when I used =LEN() in the other table, even though the number is clearly 16 characters. Weird. Don't know if that helps...

Edit2: Oops! No, they both are 16, as they should be. I had just forgotten that I had turned off "Automatic Calculation". It was off because of the big dataset the workbook is really slow to work in.

 

[Special-K99]

Times in Excel are held as a decimal number.
If you VLOOKUP a decimal number occasoinally it will not work, e.g. VLOOKUP 0.6666666 when the value is actually 0.6666667
This is probably the problem you're having.

You need to have a helper column on both tables that formats the value to date and time not the internal Excel decimal value.

 

[homie]

Times in Excel are held as a decimal number.
If you VLOOKUP a decimal number occasoinally it will not work, e.g. VLOOKUP 0.6666666 when the value is actually 0.6666667
This is probably the problem you're having.

You need to have a helper column on both tables that formats the value to date and time not the internal Excel decimal value.

I don't know if I understand you correctly. The data is in date and time format (as shown in my excerpt). This date and time has a value in Excel represented by a number, the decimals (as you say) are the time. How am I suppose to format it to date and time? It is already date and time, which in Excel has a number value. So I am not really understanding what you would want to do in this case.

Furthermore, I have checked that the number is EXACTLY the same for the lookup value and the value in the lookup array, up to the very last decimal. So if Excel has rounded this number, it has done so equally on both values, i.e. the numbers are the same, so the rounding shouldn't matter, yes?

 

[Special-K99]

Just because you can see the decimal doesnt mean there arent any more decimal values lurking internally that are not being displayed.
in A1 put 22/09/2015 08:00
in B1 put =A1 and format it as General, it displays 42269.33333
in C1 put =(B1=42269.33333)

It displays FALSE yet the values displayed are the same.

"So if Excel has rounded this number, it has done so equally on both values"
Not necessarily, I have seen it before where a decimal calculation is made two different ways in Excel and what should be the same answer comes out differently.

 

[homie]

Just because you can see the decimal doesnt mean there arent any more decimal values lurking internally that are not being displayed.
in A1 put 22/09/2015 08:00
in B1 put =A1 and format it as General, it displays 42269.33333
in C1 put =(B1=42269.33333)

It displays FALSE yet the values displayed are the same.

"So if Excel has rounded this number, it has done so equally on both values"
Not necessarily, I have seen it before where a decimal calculation is made two different ways in Excel and what should be the same answer comes out differently.

Thanks for your reply. Yeah, but in my data I tested this. I formatted the cells with the date and time as number (both for the lookup value and the value it was suppose to return in the array). I kept adding decimal numbers to be shown in the decimal box until you just saw zeros trailing, so there were no more numers being shown.
Example: Date and time cell from my lookup table of Bitcoin prices, reformatted as number: 42264,68750000000000
Date and time cell value that I am trying to look up, reformatted as number: 42264,68750000000000

These are the same numbers, right? Also, if the date and time is the same, it should give the same datevalue and timevalue number, right?

I also tried this thing which you suggested for my numbers:
in A1 put 17.09.2015 16:30
in B1 put =A1 and format is as General, it displays 42264,6875
in C1 put =(B1=42264,6785)
(We use commas for periods in Norway). This I did with the respective cells. The end cell displays TRUE.

Something else has to be the problem, no?

Edit: Maybe this deserves its own thread, as it is a seperate problem.