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:
[TABLE="width: 601"]
<tbody>[TR]
[TD]Fiat Deposits[/TD]
[TD]Timestamp[/TD]
[TD]Date[/TD]
[TD]Time[/TD]
[/TR]
[TR]
[TD]9/25/2015 19:54,5200,NOK[/TD]
[TD]9/25/2015 19:54[/TD]
[TD="align: center"]#VALUE ![/TD]
[TD="align: center"]#VALUE ![/TD]
[/TR]
[TR]
[TD]12/6/2015 17:27,330,NOK[/TD]
[TD]12/6/2015 17:27[/TD]
[TD="align: right"]12.06.2015[/TD]
[TD="align: right"]17:27[/TD]
[/TR]
[TR]
[TD]12/14/2015 17:38,2000,NOK[/TD]
[TD]12/14/2015 17:38[/TD]
[TD="align: center"]#VALUE ![/TD]
[TD="align: center"]#VALUE ![/TD]
[/TR]
[TR]
[TD]12/23/2015 18:05,400,NOK[/TD]
[TD]12/23/2015 18:05[/TD]
[TD="align: center"]#VALUE ![/TD]
[TD="align: center"]#VALUE ![/TD]
[/TR]
[TR]
[TD]5/19/2016 5:28,5000,NOK[/TD]
[TD]5/19/2016 5:28[/TD]
[TD="align: center"]#VALUE ![/TD]
[TD="align: center"]#VALUE ![/TD]
[/TR]
[TR]
[TD]6/13/2016 16:08,500,NOK[/TD]
[TD]6/13/2016 16:08[/TD]
[TD="align: center"]#VALUE ![/TD]
[TD="align: center"]#VALUE ![/TD]
[/TR]
[TR]
[TD]6/22/2016 13:33,9200,NOK[/TD]
[TD]6/22/2016 13:33[/TD]
[TD="align: center"]#VALUE ![/TD]
[TD="align: center"]#VALUE ![/TD]
[/TR]
[TR]
[TD]10/2/2016 4:40,5000,NOK[/TD]
[TD]10/2/2016 4:40[/TD]
[TD="align: right"]10.02.2016[/TD]
[TD="align: right"]04:40[/TD]
[/TR]
[TR]
[TD]12/20/2016 16:04,8198,NOK[/TD]
[TD]12/20/2016 16:04[/TD]
[TD="align: center"]#VALUE ![/TD]
[TD="align: center"]#VALUE ![/TD]
[/TR]
[TR]
[TD]12/25/2016 9:01,700,NOK[/TD]
[TD]12/25/2016 9:01[/TD]
[TD="align: center"]#VALUE ![/TD]
[TD="align: center"]#VALUE ![/TD]
[/TR]
[TR]
[TD]12/28/2016 22:40,1000,NOK[/TD]
[TD]12/28/2016 22:40[/TD]
[TD="align: center"]#VALUE ![/TD]
[TD="align: center"]#VALUE ![/TD]
[/TR]
[TR]
[TD]4/3/2017 16:42,5000,NOK[/TD]
[TD]4/3/2017 16:42[/TD]
[TD="align: right"]04.03.2017[/TD]
[TD="align: right"]16:42[/TD]
[/TR]
[TR]
[TD]5/24/2017 7:32,100000,NOK[/TD]
[TD]5/24/2017 7:32[/TD]
[TD="align: center"]#VALUE ![/TD]
[TD="align: center"]#VALUE !
[/TD]
[/TR]
</tbody>[/TABLE]
"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
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:
[TABLE="width: 601"]
<tbody>[TR]
[TD]Fiat Deposits[/TD]
[TD]Timestamp[/TD]
[TD]Date[/TD]
[TD]Time[/TD]
[/TR]
[TR]
[TD]9/25/2015 19:54,5200,NOK[/TD]
[TD]9/25/2015 19:54[/TD]
[TD="align: center"]#VALUE ![/TD]
[TD="align: center"]#VALUE ![/TD]
[/TR]
[TR]
[TD]12/6/2015 17:27,330,NOK[/TD]
[TD]12/6/2015 17:27[/TD]
[TD="align: right"]12.06.2015[/TD]
[TD="align: right"]17:27[/TD]
[/TR]
[TR]
[TD]12/14/2015 17:38,2000,NOK[/TD]
[TD]12/14/2015 17:38[/TD]
[TD="align: center"]#VALUE ![/TD]
[TD="align: center"]#VALUE ![/TD]
[/TR]
[TR]
[TD]12/23/2015 18:05,400,NOK[/TD]
[TD]12/23/2015 18:05[/TD]
[TD="align: center"]#VALUE ![/TD]
[TD="align: center"]#VALUE ![/TD]
[/TR]
[TR]
[TD]5/19/2016 5:28,5000,NOK[/TD]
[TD]5/19/2016 5:28[/TD]
[TD="align: center"]#VALUE ![/TD]
[TD="align: center"]#VALUE ![/TD]
[/TR]
[TR]
[TD]6/13/2016 16:08,500,NOK[/TD]
[TD]6/13/2016 16:08[/TD]
[TD="align: center"]#VALUE ![/TD]
[TD="align: center"]#VALUE ![/TD]
[/TR]
[TR]
[TD]6/22/2016 13:33,9200,NOK[/TD]
[TD]6/22/2016 13:33[/TD]
[TD="align: center"]#VALUE ![/TD]
[TD="align: center"]#VALUE ![/TD]
[/TR]
[TR]
[TD]10/2/2016 4:40,5000,NOK[/TD]
[TD]10/2/2016 4:40[/TD]
[TD="align: right"]10.02.2016[/TD]
[TD="align: right"]04:40[/TD]
[/TR]
[TR]
[TD]12/20/2016 16:04,8198,NOK[/TD]
[TD]12/20/2016 16:04[/TD]
[TD="align: center"]#VALUE ![/TD]
[TD="align: center"]#VALUE ![/TD]
[/TR]
[TR]
[TD]12/25/2016 9:01,700,NOK[/TD]
[TD]12/25/2016 9:01[/TD]
[TD="align: center"]#VALUE ![/TD]
[TD="align: center"]#VALUE ![/TD]
[/TR]
[TR]
[TD]12/28/2016 22:40,1000,NOK[/TD]
[TD]12/28/2016 22:40[/TD]
[TD="align: center"]#VALUE ![/TD]
[TD="align: center"]#VALUE ![/TD]
[/TR]
[TR]
[TD]4/3/2017 16:42,5000,NOK[/TD]
[TD]4/3/2017 16:42[/TD]
[TD="align: right"]04.03.2017[/TD]
[TD="align: right"]16:42[/TD]
[/TR]
[TR]
[TD]5/24/2017 7:32,100000,NOK[/TD]
[TD]5/24/2017 7:32[/TD]
[TD="align: center"]#VALUE ![/TD]
[TD="align: center"]#VALUE !
[/TD]
[/TR]
</tbody>[/TABLE]
"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

Last edited: