date and time cells are not changing to numbers

[lezawang]

Hi
I tried to pasteSpecial and mutiply by 1, I tried to use datevalue() function but date and time in column B are not changing. Any idea would be very much appreciated. The link to the file is below.

dateandtime.xlsx

drive.google.com

dateandtime.xlsx
	A	B	C	D	E	F
1	ID	Ordered At		Day
2	1656098	Jul 13 2020 12:15PM	#VALUE!	Mon		TRUE
3	1710182	Oct 9 2020 11:45AM		Fri		TRUE
4	1710256	Oct 9 2020 12:42PM		Fri		TRUE
5	1656942	Jul 14 2020 11:39AM		Tue		TRUE
6	1657357	Jul 14 2020 4:13PM		Tue		TRUE
7	1657321	Jul 14 2020 3:43PM		Tue		TRUE
8	1656855	Jul 14 2020 11:01AM		Tue		TRUE
9	1657429	Jul 14 2020 4:40PM		Tue		TRUE
10	1653857	Jul 8 2020 3:31PM		Wed		TRUE
11	1653190	Jul 8 2020 9:57AM		Wed		TRUE
12	1653194	Jul 8 2020 10:00AM		Wed		TRUE
13	1654194	Jul 9 2020 9:41AM		Thu		TRUE
14	1654272	Jul 9 2020 10:45AM		Thu		TRUE
15	1654358	Jul 9 2020 11:38AM		Thu		TRUE
16	1657024	Jul 14 2020 12:23PM		Tue		TRUE
17	1653895	Jul 8 2020 3:45PM		Wed		TRUE
18	1654520	Jul 9 2020 12:56PM		Thu		TRUE
19	1654523	Jul 9 2020 12:58PM		Thu		TRUE
date andtime

Cell Formulas
Range	Formula
C2	C2	=DATEVALUE(B2)
F2:F19	F2	=ISTEXT(B2)

 

[MARK858]

It would need to be 13 Mar rather than Mar 13 in the comma version on a UK desktop.

 

[Rick Rothstein]

It would need to be 13 Mar rather than Mar 13 in the comma version on a UK desktop.

For the date only, I think this formula will work everywhere (obviously it works in the US locale)...

=0+MID(REPLACE(B2,8,0,LEFT(B2,4)),5,11)

Again, the cell with this formula needs to be formatted with the date format of the OP's choosing.

 

[MARK858]

For the date only, I think this formula will work everywhere (obviously it works in the US locale)...

=0+MID(REPLACE(B2,8,0,LEFT(B2,4)),5,11)

Again, the cell with this formula needs to be formatted with the date format of the OP's choosing.

Book1
	B	C
2	Mar 13, 2021 11:00	#VALUE!
Sheet1

Cell Formulas
Range	Formula
C2	C2	=0+MID(REPLACE(B2,8,0,LEFT(B2,4)),5,11)

on a UK version

 

[Rick Rothstein]

Really? I surely thought that arrangement worked everywhere. Maybe it was with dashes instead of spaces that I am thinking of...

=0+TEXT(MID(REPLACE(B2,8,0,LEFT(B2,4)),5,11),"dd-mmm-yyyy")

 

[Alex Blakenburg]

Really? I surely thought that arrangement worked everywhere. Maybe it was with dashes instead of spaces that I am thinking of...

=0+TEXT(MID(REPLACE(B2,8,0,LEFT(B2,4)),5,11),"dd-mmm-yyyy")

dd-mmm-yyyy hh:mm AM/PM worked for me with both an Australian & US regional setting.
(if I use the 2 digit mm it appears to work until I add in the time element)
For a 2 digit version yyyy-mm-dd hh:mm AM/PM works

 

[MARK858]

yyyy-mm-dd works everywhere (just about) as it complies with the International ISO standard for dates (ISO 8601), and Excel complies with the standard.

 

[lezawang]

Thank you all for the help. I just tried to use Power Query, I went to Data-->From table and to my surprise the column date (Order at) was converted to right date (number, moved to the right hand side). I could not believe it. So even without doing anything PowerQuery changed to right format then I load it to Excel and got everything fixed. Does that mean all Date/Time format issues can be resolved by PowerQuery, just as simple as loading them to PowerQ and it will do it for me. I am shocked to be honest.

 

[Alex Blakenburg]

Thank you all for the help. I just tried to use Power Query, I went to Data-->From table and to my surprise the column date (Order at) was converted to right date (number, moved to the right hand side). I could not believe it. So even without doing anything PowerQuery changed to right format then I load it to Excel and got everything fixed. Does that mean all Date/Time format issues can be resolved by PowerQuery, just as simple as loading them to PowerQ and it will do it for me. I am shocked to be honest.

Clearly it is never going to be "all" but PQ is pretty good at interpreting dates. If it is in a date format that is different to the one in your Region, there is a "use locale" option where you tell PQ which Region the data if from and that will help it convert it to your format eg if your format is d/mm/yyyy and the input is in the format mm/dd/yyyy, you need to tell it the file is from English (United States).