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)

 

[Fluff]

How about

=DATE(MID(B2,7,5),MONTH(LEFT(B2,6)),MID(B2,5,2))

 

[lezawang]

Thank you so much. It is working. But want to ask a question please, why that formula. Do I need to use that formula all the time or on specific cases? How do I know. Because I have been told here most cases will be solved by PasteSpecial or datevalue(), timevalue() etc. Thank you again.

 

[Fluff]

Just depends on the data & your date settings

 

[Alex Blakenburg]

I am pretty sure the issue here is that you need a space before the AM & PM.
Try doing a replace "AM" with " AM"

 

[Fluff]

That makes no difference for me, but may work if you have US style date format.

 

[Alex Blakenburg]

I have logged out for the day but it definitely made a difference in the mobile app and I am pretty sure I have had it before on the desktop too. My regional format is dd/mm/yyyy.

 

[Rick Rothstein]

Thank you so much. It is working. But want to ask a question please, why that formula. Do I need to use that formula all the time or on specific cases?

What formula you would need to use might depend on where in the world you are located (your profile does not say). For the data you displayed, Fluff's formula should work for anyone's locale. I am located in the US so, for me, this simpler formula also worked (but it probably would not work for someone in England say)...

=0+REPLACE(LEFT(B2,11),7,0,",")

although what it returns is the date as a serial number so you have to format the cell with the date format pattern you want it to display as.

SIDE NOTE: Did you see my response to your "first cell in a range/table" question yet (I think my response answers your question)?

 

[Alex Blakenburg]

I have logged out for the day but it definitely made a difference in the mobile app and I am pretty sure I have had it before on the desktop too. My regional format is dd/mm/yyyy.

I have just had a chance to test this out on my desktop. And Fluff was of course correct.
Also there are some differences between the Desktop version and the Online & Mobile versions.

 

[Alex Blakenburg]

The test results looks like this.
(for even a chance at conversion it needs both the comma and the space)