=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

 

[AlanY]

it looks like that your dates in Column B are in dd/mm/yyyy format and your regional setting is mm/dd/yyyy.

 

[Scott T]

Datevalue expects to have a text date so it can convert it to an Excel date. Since you have an excel date you do not need to convert it.

for date use and format with a date format

=INT(B2)

for time and format with a time format

=MOD(B2,1)

 

[Special-K99]

This is a bit long but it works

With your date and times in column A

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

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

For time
=(TRIM(MID(SUBSTITUTE(B1," ",REPT(" ",50)),(4-1)*50+1,50)))

 

[homie]

Hey AlanY, it turns out that you are correct in that my format in colomn B Timestamp were in the format mm.dd.yyyy. I then deleted the first cell in Timestamp and changed the formatting to dd.mm.yyyy but it still showed the date as m/d/yyyy. See picture:

I tried inserting picture, don't know if it worked.

 

[homie]

Datevalue expects to have a text date so it can convert it to an Excel date. Since you have an excel date you do not need to convert it.

for date use and format with a date format

=INT(B2)

for time and format with a time format

=MOD(B2,1)

Hey Scott T,

I tried your solutions but I get the #VALUE ! error when I try it on the timestamps where I get the same error in col C (Date) and D (Time). I.e. I don't think I have a date in col B Timestamp. All that does is fetching part of the text string in col A Fiat Deposits through a =Left(Find()) function. If col B Timestamp already was a date recognized by Excel I would be able to change the formatting to a number, and the number would be something like 40908,35. That is not possible though. If I try to "Format Cells" in col B Timestamp nothing changes. If it was already a datevalue, I would be able to change formatting to show it as a date or as a number.

 

[Sandie1712]

Hi Homie,

If i understood it well, after segregating the date and time data in B column, below cited are two ways ,i know, to resolve the issue

1) Simply apply date and time formatting in desired format in date and time column respectively.

2) In case, you want it to be automated, apply below cited formulas in date and time column respectively.

=TEXT(B2,"mm/dd/yyyy")

=TEXT(B2,"hh:mm")

Hope it will resolve your challenge.

 

[Jonmo1]

It doesn't matter the format of column B, because it's a TEXT string resulting from the formula =LEFT(A2;FIND(",";A2)-1)

The problem is that your PC is using UK Date format, but the strings are in US format.
So when the DATEVALUE function sees 9/25/2017, that's not a valid date...in UK format, so you get #Value ! error.
An even worse problem is the ones it DOES recognize as dates are the ambiguous ones, where both month and day are <13.
It sees it as a date, but the wrong date. With the month and day reversed.
It sees 12/6/2015 as December 6th, but the conext of the original string says it's June 12th.

You're going to need some brute force formulas using Left/Right and Mid funtions.
Standby.

 

[Special-K99]

See post #4 .
It works.

 

[Jonmo1]

@Special-K99
I couldn't get those to work for me.
Probably because I'm in US date format